Declare a linked server

Question

Declare a linked server

saivko

SSC Eights!

Points: 898
More actions
June 18, 2009 at 1:59 pm

#135369

How do you declare a linked server? Something like this failing example:
DECLARE @linkedserv CHAR;
SET @linkedserv = [srvname];
GO
SELECT * FROM @linkedserv..database.dbo.table;

Viewing 8 posts - 1 through 7 (of 7 total)

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

ps. One Orange Chip Points: 29423 More actions · Answer 1

saivko (6/18/2009)
How do you declare a linked server? Something like this failing example:
DECLARE @linkedserv CHAR;
SET @linkedserv = [srvname];
GO
SELECT * FROM @linkedserv..database.dbo.table;

You have to add a linked server, not declare it.

sp_addlinkedserver 'myservername'

select * from myservername.database.dbo.table

Pradeep Singh

saivko SSC Eights! Points: 898 More actions · Answer 2

It's already added.... and I can select from it with no problem. I just wanted the wording for the linked server into a parameter for a stored proc.

ps. One Orange Chip Points: 29423 More actions · Answer 3

declare @sql varchar(255)

declare @myserver varchar(255)

set @myserver='myDistantServer'

set @sql = 'select * from ' + @myserver + '.mydatabase.dbo.mytable'

sp_executesql @sql

check this out if this is what u need.

Pradeep Singh

saivko SSC Eights! Points: 898 More actions · Answer 4

Thanks, getting this though: Incorrect syntax near 'sp_executesql'

ps. One Orange Chip Points: 29423 More actions · Answer 5

i guess there must be some syntax error in the query. for sp_executesql, syntax is plain vanilla.

http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

you may want to have a look at EXECUTE statement too.

http://msdn.microsoft.com/en-us/library/ms188332(SQL.90).aspx

Edit - added url for execute.

Pradeep Singh

saivko SSC Eights! Points: 898 More actions · Answer 6

Thanks, got it now... it wanted an nvarchar rather than a varchar

ps. One Orange Chip Points: 29423 More actions · Answer 7

ps.

One Orange Chip

Points: 29423

June 18, 2009 at 3:07 pm

#1012082

Glad u got it going 🙂

Pradeep Singh