June 23, 2009 at 2:44 am
===========================================================================
Go
-- Declare the variables
DECLARE @server_name as varchar(100),
@DB_Name NVARCHAR(50)
SET @DB_Name=N'.Database1'
-- Create temp table to hold the data from all servers
select top 0 C1,C2,C3,C4,C5
into #data
from Base_Table Bt
left outer join table1 t1 on bt.guid=t1.guid
left outer join table2 t2 on bt.guid=t2.guid
left outer join table3 t3 on bt.guid=t3.guid
left outer join table4 t4 on bt.guid=t4.guid
-- Declare cursor and fetch the linked servers
DECLARE c_Servers CURSOR FAST_FORWARD FOR
SELECT NAME
FROM sys.servers AS S
WHERE S.is_linked = 1
order by 1
OPEN c_Servers
FETCH NEXT FROM c_Servers INTO
@server_name
WHILE @@FETCH_STATUS = 0
BEGIN
--run the dynamic query
Insert into #data
Exec('select C1,C2,C3,C4,C5
into #temp
from Base_Table Bt
left outer join '+@Server_Name+@DB_Name+'.dbo.table1 t1 on bt.guid=t1.guid
left outer join '+@Server_Name+@DB_Name+'.dbo.table2 t2 on bt.guid=t2.guid
left outer join '+@Server_Name+@DB_Name+'.dbo.table3 t3 on bt.guid=t3.guid
left outer join '+@Server_Name+@DB_Name+'.dbo.table4 t4 on bt.guid=t4.guid
-- Add another column to identify the Linkedserver for this set of data
alter table #temp
add [Server_Name] VarChar(100)
-- update the linked server value in servername column
-- I am getting error in this step
Update #temp
Set [Server_Name]='+@Server_Name+'
select * from #temp')
FETCH NEXT FROM c_Servers INTO
@server_name
END
CLOSE c_Servers
DEALLOCATE c_Servers
Select* from #data
======================================================================
I am running the above code from a SQL server on which we have linked many other remote SQL servers.
The code picks up the required data from each server n finally mixes up the total data from all the servers where I cannot determine which result set belongs to which of the linked server. So, I am trying to add an extra column through which I can identify the linked server name but it throws error while updating. The variable "@server_name" resolves to appropriate linked server name but gets understood by SQL server as a column name & throws the below error.
Msg 207, Level 16, State 1, Line 119
Invalid column name 'linkedserver1'.
Kindly let me know, how can I accomplish this.
June 23, 2009 at 5:21 am
This is because you are not putting the quotes in the string, and the resulting query is
Update #temp
Set [Server_Name]=linkedserver1
while what you want is
Update #temp
Set [Server_Name]='linkedserver1'
right?
You just have to put quotes like this (watch out: it's 3 single quote characters!):
...
Update #temp
Set [Server_Name]='''+@server_name + '''
...
ciao
Giacomo
June 23, 2009 at 8:38 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply