Linked Server using Variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server using Variable

Author

Topic

khanewal
Starting Member

33 Posts

Posted - 2010-09-20 : 19:45:10

Declare @DBname AS varchar(25),@LinkedServer AS Varchar(25)
SET @DBname = 'ABC'
SET @LinkedServer = 'ServerVM01'

Select * from [@LinkedServer].[@DBname].[EDDSDBO].[File]

Why I can't use Variable in a link server query getting an error,

Msg 7314, Level 16, State 1, Line 11
The OLE DB provider "SQLNCLI" for linked server "ELAWRELSQLVM01" does not contain the table ""@DBname"."EDDSDBO"."File"". The table either does not exist or the current user does not have permissions on that table.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 21:27:41

EXEC('Select * from ['+@LinkedServer'+'].['+@DBname+'].['+EDDSDBO+'].['+File'+']'

I think

why is it dynamic?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

khanewal
Starting Member

33 Posts

Posted - 2010-09-20 : 21:57:28

No, just store the DB into the variable so that whenever Store proc runs change the DB name and insert values into the particular project, it will easy to store DB name in a variable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources