Using a Linked server name as a variable

Question

Post reply

Using a Linked server name as a variable

Djcarpen11

Ten Centuries

Points: 1069
More actions
August 6, 2010 at 7:50 am

#227514

I’ve created a solution to a problem I had but I’m not sure it’s the best away about it doing it.
Background – We have 12 SQL servers across our estate all with a Auditing database which stores only two weeks worth of data. Addtionally to these 12 we a new sql server that is going to be used as a centrol respoitory for reports. I needed a way of pulling the data from the Audit databases on the 12 SQL servers into the centrol reporting server. I Needed to create a process that would be easy to implement and would be easy to scale up.
My Solution – The centrol server has a linked server added for each of the other 12 servers each linked server follows our nameing convention of being named the same as the server (eg server1 as a linked server would be called server1 etc..). We have a table on the central server that contains details of all servers including the name. So I’ve created something like the below query to pull all this data in.
DECLARE @sql Nvarchar(max)
DECLARE @SQL2 Nvarchar(max)
DECLARE @LinkedServer Nvarchar(30)
DECLARE @RowCount int, @RowNumber int
CREATE TABLE #ServerDetails
(
RowID int IDENTITY(1,1),
ServerName Nvarchar(30)
)
INSERT INTO #ServerDetails(ServerName)
SELECT ServerName
FROM DimSQLServers
SET @RowNumber = @@RowCount
SET @RowCount = 1
WHILE @RowCount <= @RowNumber
BEGIN
SET @linkedSErver = (SELECT ServerName
FROM #ServerDetails
WHERE ROWID = @RowNumber
)
SET @sql = 'INSERT INTO CENTRALREPORTSERVER.dbname.dbo.tableA
(
Value1
Value2
etc....
)
From '+@linkederver+'.dbname.dbo.tableB A
Left Join CENTRALREPORTSERVER.dbname.dbo.tableA on
---join conditons bring back only records not already
in TableA---
This happened for 5 more tables
'
SET @SQL2 --- same thing as @SQL1
Exec @sql
Exec @SQL2
SET @RowCount = @RowCount + 1
END
DROP TABLE #ServerDetails
My question is, is there perhaps a better way of going about what I’m trying to achieve?
Please feel free to ask any questions in case I’ve not properly explained myself.

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

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

Lowell SSC Guru Points: 323518 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: 323518 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.