January 6, 2009 at 5:07 am
Hi,
I have around 10 SQL servers in my test lab and i have created linked servers for each of them on our main central server. I can fetch data from these servers by using the linked servers perfectly.
Please see the small example code below:-
select v.name,v.age
from mylinkedserver1.mydb.dbo.mytable v
Now, I have a small issue (may sound dumb as i don't know whether I am doing it right or not).
I need to run this code on all the servers to fetch the data and I am trying to put it into a single procedure where I can pass all the linked servers as parameters and get the desired output. So, when I am trying to declare the linked server as a variable and use it in my code above it says syntax error and I don't know why it is so 😀 . The way I am trying it is as follows:-
--== Declare the link server variable
declare @link_serv1 varchar(20)
set @link_serv='mylinkedserver1'
--== use the variable in the select query
select v.name,v.age
from @link_serv1.mydb.dbo.mytable v
Regards,
RP
January 6, 2009 at 5:27 am
This is just another case of using "Dynamic SQL". You can't directly use a variable in place of object definitions...
You have to do something like....
--== Declare the link server variable
declare @link_serv1 varchar(20)
set @link_serv='mylinkedserver1'
--== use the variable in the select query
EXEC( 'select v.name,v.age from ' + @link_serv1 + '.mydb.dbo.mytable v' )
--Ramesh
January 6, 2009 at 7:14 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply