Linked Server Issue when Executing Dynamic SQL

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 2000 Forums

Transact-SQL (2000)

Linked Server Issue when Executing Dynamic SQL

Author

Topic

Cardinalae
Starting Member

3 Posts

Posted - 2002-11-13 : 14:41:39

Hola Everybody.

I found an issue that appears to be a quirk with SQL Server 7, 2000, etc.

If I have a SQLServer 2000 base install, with SP2, On Win2k that is
named: Player_Server
with a Database named: TestDb
With a Table named: player

then I should am I able to do the following:

1) Use the Client Network Utility and set up an Alias entitled:
"Server1" that points back to Player_Server.

Granted, this is an alias that is currently recursive, but for development purposes, should the TestDb be ever seperated out to another SQL Server, than having that alias becomes important.

Now, in Query analyzer I can issue sp_addlinkedserver 'Server1'
which will then add that server alias to the LinkedServer list in master DB.

I can than issue the following Query:

select * from server1.TestDb.dbo.Player

with no errors (I read about a feedback loop issue that sometimes occur if you refer an alias back to the same server, but I have not run into it here).

HOWEVER,

If I were to construct a string

DECLARE @sqlStr VARCHAR(200)
SELECT @sqlStr = 'select * from server1.TestDb.dbo.Player'

and then do a:
exec sp_executesql @sqlStr

I *THEN* Get a error from that server1 alias. If you remove the server1 alias, it is happy. Something that maybe not too many folk our there have encountered but there it is.

Comments, thoughts,
Cardinalae@lvcm.com

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 04:54:55

try
DECLARE @sqlStr NVARCHAR(200)
SELECT @sqlStr = 'select * from server1.TestDb.dbo.Player'
exec sp_executesql @sqlStr

or

exec sp_executesql N'select * from server1.TestDb.dbo.Player'

or just

exec ('select * from server1.TestDb.dbo.Player')

Should work OK.
sp_executesql expects an nvarchar

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 11/14/2002 04:55:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources