Using a Linked server name as a variable

Question

Post reply

Using a Linked server name as a variable

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323741 More actions · Answer 1

i think synonyms might be a good fit for what you are trying to do.

a synonym can point to an object..table,view,procedure, etc.

so you could make a suite of 12 synonyms to point to your lined server tables to make them easier to query:

IF OBJECT_ID('dbo.RPT01', 'SN') IS NOT NULL

DROP SYNONYM dbo.RPT01;

CREATE SYNONYM dbo.RPT01 FOR LinkedServer1.DatabaseName.dbo.TableB;

IF OBJECT_ID('dbo.RPT02', 'SN') IS NOT NULL

DROP SYNONYM dbo.RPT02;

CREATE SYNONYM dbo.RPT02 FOR LinkedServer2.DatabaseName.dbo.TableB;

--etc

--insert into CentralRepository....

SELECT * FROM RPT01' UNION ALL

SELECT * FROM RPT02'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 2

That's an idea I toyed with the only problem is that each synonyms would need it's own insert statment?(unless I've missed something).

I'm inserting into 5 tables. So for each linked server I need 5 insert statments. I'd need 12 sysnomns so those 5 inserts statments turn into 60 statments.

The solution I've created works, but I'm little concerned with so much dynamic sql accross linked servers.

Lowell SSC Guru Points: 323741 More actions · Answer 3

just make it easier on yourself...

create 5 views, one for each group of statements you will need to gather....

yeah the view will have the 12 statements for each synonym, but it makes it easier and more manageable in the long run.

what if SQL statement # 2 needs an extra column of information for example..fiddling with dynamic SQl can be a pain. having it all in a single view, where you edit it in one spot makes sense.

CREATE VIEW V_RPT1

AS

SELECT * FROM RPT01' UNION ALL

SELECT * FROM RPT02' ...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 4

I can see the benefits of doing it that way but, if you had to add a column like you say, you'd only have to do that once with dynamic SQL but 12 times in the view?

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

I don't think that dynammic SQL will be your bottleneck as much as accessing 5 linked servers will be. It's not likely you can do anything about the linked servers. Any optimization you do to the query is most likely not going to give you much performance boost.

Besides, dynammic SQL isn't as naughty as everyone makes it out to be.

If you're using parameters other than @linkedserver, I'd suggest using sp_executeSQL, and calling a parametized stored procedure or function on the linked server. This will give you the best chance of utilizing pre-compiled execute plans.