Accessing linked servers dynamically

We have a view in one database that consists of the union of all the
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!

Access sub-select question

NewBe Access Question

Hi folks,

I am having trouble building a query that retrieves what I need. The table I query is pre-filled with rows of records both empty and filled that I will continue to fill or empty from a form. Two columns in the table are completely pre-filled, the ID column and another, which has three different text categories. I need to sub-select an entire group of rows with one kind of text entry and then select the lowest ID value in that group whos columns are otherwise empty.

The table might look like:

ID CAT INFO2 INFO3 Etc.
1 aaaa
2 aaaa something
3 aaaa
4 bbbb
5 bbbb something
6 bbbb
7 cccc
8 cccc
9 cccc something

So, if Row ID 4 was the MIN ID of CAT bbbb whos columns were otherwise empty, I would like to query it and fill the other columns.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)

The above returns the lowest ID row in the entire table, of course. Not what I wanted.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)
AND Cat = 'bbbb'
AND Info2 IS NULL

This returns a No records returned message. Of course! I presume it does so because the lowest ID for an empty bbbb category is not the lowest ID in the table.

Any help would be greatly appreciated.

RosieI hope I understood this correctly.

To return the lowest ID for a certain category has a null value do the following.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl WHERE Cat = 'bbbb'
AND Info2 IS NULL)

Note that there is another where statement in the sub-query

Machado|||Yes, you understood it perfectly!

Two WHERE's. That did it!

Thanks soooo much.

'Rosie'

Access path selection

SQL Server 2000 32 bit sp4 / Windows 2003

I have a table that has over 7.5 million rows. I have a unique clustered index (U_ORDR, D_ORDR_YR). There are 5342000 distinct occurances of U_ORDR and 24 distinct occurances of D_ORDR_YR. Database Tuning Advisor showed that there should be a nonclustered index created like (D_ORDR_YR, U_ORDR). I created this index and ran a query with show execution plan. The nonclustered index is chosen even though a bookmark lookup is required. Also the way I understand it the clustered index would be used to get to the C_ORDR column. So why is SQL Server choosing to access the nonclustered index. The plan shows a clustered index scan when I force the clustered index to be used. It also show that it would take longer to use the clustered index (85%) than to use the nonclustered/bookmark access (15%). Here is the query that I am using

declare @.OrderNumber [bigint], @.OrderYear [bigint]

select @.OrderNumber = 1753851, @.OrderYear = 02

SELECT C_ORDR

FROM CIS540T CIS540T --with (index (IX_CIS540T_Ordr_no))

WHERE CIS540T.U_ORDR = @.OrderNumber and CIS540T.D_ORDR_YR = @.OrderYear

If this is the exact query, consider just putting an index to cover:

C_ORDR, U_ORDR, D_ORDR_YR

Then you get the best of both worlds...

|||

Just to expand on Louis' suggestion in that you should specify the following order for the columns in your new index:

U_ORDR, D_ORDR_YR, C_ORDR

It sounds like the U_ORDR column offers the highest selectivity, followed by D_ORDER_YR. C_ORDR is included to form a covering index for your query.

Chris

|||My question are why is SQL Server doing an index scan when I told it that D_ORDR_YR and U_ORDR is unique. Why doesn't it use an index seek?|||

Both U_ORDR and D_ORDR_YR are defined as BIGINT in the table?

|||Both columns are defined as decimal. U_ORDR with a precision of 7 and U_ORDR_YR with a prrcision of 3. I changed the definition of @.U_ORDR and @.D_ORDR_YR to decimal the plan stayed the same. It is still doing an index scan instead of a seek.|||

Hmmm, the conversion can/will cause a scan to substituted for a scan.

try issuing:

DBCC FREEPROCCACHE

GO

first and then rerun.

|||It does seem to have been a column definition problem. I did some more swapping of data types and was able get the plan to show an index seek. Thanks for your help.

Access' Last equivalent in SQL2005

Microsoft Access has the functions - First & Last that you can apply to a select list to limit the returned rows to those. I need to convert several queries, developed by a data researcher in Access, that use these functions into genuine Transact SQL.

I can use T-SQL's Top for the First function, but I'm stumped on what to use for Last.

Also, the inherited queries don't have any 'order' clauses, wouldn't they be necessary when using such a function?

Thanks.

For a 'q&d' (quick and dirty') way to accomplish your task, for LAST, just like using the TOP 1 function for the FIRST, you can reverse the sort [ORDER BY DESC] and retrieve the TOP 1 for the LAST.

And a custom function may be more efficient -it depends upon several factors. If you posted your entire query, someone here may be able to provide more directed assistance.

|||

This is the one I'm working on now(first), it's designed to show which students didn't finish school but were pretty close to a degree. It's nasty.

SELECT Last(fice_table.inst_type) AS LastOfinst_type, student_table.academic_year, Max(student_table.term) AS term, Last(student_table.geo_state) AS LastOfgeo_state, student_table.ssn_id, Last(student_table.degree_intent) AS LastOfdegree_intent, Last(student_table.student_level) AS LastOfstudent_level, graduated_student_table.ssn_id, Last(stdnt_lvl_desc_table.description) AS StudentLevel, Last(degr_intnt_desc_table.description) AS DegreeIntent

FROM (((student_table INNER JOIN fice_table

ON student_table.fice_code = fice_table.fice_code)

LEFT JOIN graduated_student_table

ON student_table.ssn_id = graduated_student_table.ssn_id)

INNER JOIN degr_intnt_desc_table

ON (student_table.academic_year = degr_intnt_desc_table.academic_year) AND (student_table.degree_intent = degr_intnt_desc_table.degree_intent))

INNER JOIN stdnt_lvl_desc_table

ON (student_table.academic_year = stdnt_lvl_desc_table.academic_year) AND (student_table.student_level = stdnt_lvl_desc_table.code)

GROUP BY student_table.academic_year, student_table.ssn_id, graduated_student_table.ssn_id

HAVING (((Last(fice_table.inst_type))="1") AND

((student_table.academic_year)="2006" And Not (student_table.academic_year)="2007") AND

((Last(student_table.geo_state))="04") AND

(Not (Last(student_table.degree_intent))="3") AND

((Last(student_table.student_level))="01" Or (Last(student_table.student_level))="02" Or (Last(student_table.student_level))="03" Or (Last(student_table.student_level))="04") AND

((graduated_student_table.ssn_id) Is Null));

And since she is using 'Last' without any ordering, I'm now wondering if the thing has ever worked correctly.

Thanks for your time.

|||

That is an interesting query. There is a bit of difference between Access and SQL Server. Access is a record manager and tables do have some order, so it might not be a problem. In SQL Server, it is much more particular about things like this. SQL Server optimizes query plans in a very strict manner.

|||

Interesting that's a kind word for it

I tried using top with descending indices, it was a disaster. I wound up with 1 row. Access seems able to stack up the Last functions, while SQL only allows a single TOP.

This is looking like it will have to be done with a program or stay in Access. It's beyond my meager T-SQL abilities.

Thanks.

|||

You can specify the number of rows TOP returns

SELECT TOP (5) * ...

or

DECLARE @.cnt int

SET @.cnt = 5

SELECT TOP (@.cnt) * ...

or

SELECT TOP (10) PERCENT * ...

|||

Paul,

What determines the 'LAST' entry in each of the tables?

(Is there a datetime column in each table?)

It would be helpful to have the table DDL...

|||

I'm not sure what determines 'Last' of each column. That lack of ordering was the basis of my questioning the whole history of the use of this query.

But generally, the columns that are 'Last'ed are char(1 or 2) with strictly numeric data. e.g. the domain of fice_table.inst_type is '1', '2', '3', '4' but there is no index on that column. Likewise with student_table.geo_state, the type is char(2) with domain of '00' -> '50' inclusive & '98' & '99' again no index on that column.

And I know that I can specify TOP x <col name>, but I need the Last one of each group by and I can't figure out how to do that. I've been stumped forever by a simple query to get the top 5 scores from each county from a table with a county code column domain of '00' to '75' and an integer score column. I know it can be done and it has to be the grouping that's the trouble.

|||

If you want to post some sample DDL (create table, etc.) and sample data (preferrablyin the form of INSERT statements)...we can all take a stab at coming up with something that works for you.

|||

You should be able to use the row_number() function to some advantage here. Elsewhere you asked about finding the top 5 rows for each student, which you can also do this way.

WITH Ranked AS (
select
<the columns you need>,
row_number() over (
partition by
student_table.academic_year,
student_table.ssn_id,
graduated_student_table.ssn_id
order by
whatever column list will put the rows
you want for each student/year at the top
) as rk
from your join
), TopFiveEachStudentEachYear AS (
select * from Ranked
where rk <= 5
)
select * from TopFiveEachStudentEachYear
-- or a different query against these results

The ordering in the OVER clause may benefit from expressions like:

ORDER BY

student_table.student_level DESC,

case when student table_academic_year = '2006' then 0 else 1 end,

...

Steve Kass

Drew University

www.stevekass.com

|||

OK, here's the DDL, I'm cutting out a LOT of columns and deleting a bunch of indices (it's a huge database):

This first table is kind of the parent of all tables underneath, one row per college:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[fice_table] Script Date: 07/03/2007 14:00:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fice_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[fice_table](
[fice_code] [char](6) NOT NULL,
[school_name] [varchar](62) NOT NULL,
[school_abbr] [varchar](8) NOT NULL,
[school_type] [char](1) NULL,
[inst_type] [char](1) NULL,
[act_code] [char](4) NULL,
[fap_code] [char](3) NULL,
[ipeds_code] [char](6) NULL,
[type_code] [char](1) NULL,
[sreb_cat] [char](2) NULL,
[ets_code] [char](4) NULL,
[system_code] [char](1) NULL,
[title_iv_code] [char](6) NULL,
CONSTRAINT [PK_fice_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO

This is the student_table, one row per student per term per year per college, so a student can have up to 4 rows per year 0 = Summer II, 1 = Fall, 2 = Spring, 3 = Summer I

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[term] [char](1) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[geo_county] [char](3) NULL,
[geo_state] [char](2) NULL,
...
[degree_intent] [char](1) NULL,
[attend_status] [char](1) NULL,
[init_admit_date] [char](6) NULL,
[student_level] [char](2) NULL,
...
[diploma_ged] [char](1) NULL,
...
[on_cr_hours] [tinyint] NULL,
[off_cr_hours] [tinyint] NULL,
...
) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [PK_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'PK_student_table')
ALTER TABLE [dbo].[student_table] ADD CONSTRAINT [PK_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
END
GO
SET ANSI_PADDING OFF
GO

/****** Object: Index [_dtw_index_student_table] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'_dtw_index_student_table')
CREATE NONCLUSTERED INDEX [_dtw_index_student_table] ON [dbo].[student_table]
(
[academic_year] ASC,
[term] ASC,
[fice_code] ASC
)
INCLUDE ( [student_level],
[on_cr_hours]) ...
GO

/****** Object: Index [fice_ssn_admit_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_admit_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_admit_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[admit_status] ASC,
[hs_grad_year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_ssn_enroll_status_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_ssn_enroll_status_idx')
CREATE NONCLUSTERED INDEX [fice_ssn_enroll_status_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[ssn_id] ASC,
[enroll_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
GO

/****** Object: Index [fice_year_term_ssn_idx] Script Date: 07/03/2007 13:46:19 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[student_table]') AND name = N'fice_year_term_ssn_idx')
CREATE NONCLUSTERED INDEX [fice_year_term_ssn_idx] ON [dbo].[student_table]
(
[fice_code] ASC,
[academic_year] ASC,
[term] ASC,
[ssn_id] ASC
)...
GO

Now here is the graduated_student_table, again lot's cut out:

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[graduated_student_table](
[fice_code] [char](6) NOT NULL,
[academic_year] [char](4) NOT NULL,
[ssn_id] [char](9) NOT NULL,
...
[graduation_date] [char](6) NULL,
[degree_level] [char](2) NULL,
...

[init_enroll_status] [char](1) NULL,
[init_attend_status] [char](1) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
...

/****** Object: Index [PK_graduated_student_table] Script Date: 07/03/2007 13:54:12 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[graduated_student_table]') AND name = N'PK_graduated_student_table')
ALTER TABLE [dbo].[graduated_student_table] ADD CONSTRAINT [PK_graduated_student_table] PRIMARY KEY CLUSTERED
(
[fice_code] ASC,
[academic_year] ASC,
[ssn_id] ASC,
[degree_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

Now here is the stdnt_lvl_desc_table, there are codes for each student level, 01 freshman, 02 sophomore, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[stdnt_lvl_desc_table] Script Date: 07/03/2007 13:58:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stdnt_lvl_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[stdnt_lvl_desc_table](
[academic_year] [char](4) NOT NULL,
[code] [char](2) NOT NULL,
[description] [varchar](40) NOT NULL,
CONSTRAINT [PK_stdnt_lvl_desc_table] PRIMARY KEY NONCLUSTERED
(
[academic_year] ASC,
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

And the degree_intnt_desc_table:

There are codes for the reason the student began college, 1 thru 9 for Bacc Degree Seeking, Assoc Degree Seeking, etc, etc.

Code Snippet

USE [sisdb]
GO
/****** Object: Table [dbo].[degr_intnt_desc_table] Script Date: 07/03/2007 13:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[degr_intnt_desc_table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[degr_intnt_desc_table](
[academic_year] [char](4) NOT NULL,
[degree_intent] [char](1) NOT NULL,
[description] [varchar](50) NULL,
[include_in_fall_cohort] [char](1) NULL,
CONSTRAINT [PK_degr_intnt_desc_table] PRIMARY KEY CLUSTERED
(
[academic_year] ASC,
[degree_intent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

As for sample data, I'm afraid I can't let that out and my VSDBPro project isn't finished yet. But, you can imagine that each row in the student_table has a Social Security Number or College Assigned ID and the graduated_student_table has a corresponding ssn_id for graduates.