April 3, 2006 at 11:52 am
I came across this google search:
specifically these lines of code:
DECLARE @linkedServerName VARCHAR(255)
SET @linkedServerName = 'ProductionLinkedServerName'
GO
EXEC('CREATE PROCEDURE whatever
AS
BEGIN
SELECT * FROM ['+@linkedServerName+']. ...
END
GO')
When I run in Query analyzer, I get this result:
======
Server: Msg 7202, Level 11, State 2, Line 3
Could not find server ''+@linkedServerName+'' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
======
And yet the link server exists. I really don't want to use openquery as this appears to be fairly wordy and would be a lot of changes as we have numerous sps that use a link server.
April 3, 2006 at 12:12 pm
Something like this should work:
DECLARE @string VARCHAR(255)
SET @string = 'ProductionLinkedServerName'
EXEC ('SELECT * FROM ' + @string)
April 3, 2006 at 10:59 pm
DECLARE @linkedServerName VARCHAR(255)
SET @linkedServerName = 'ProductionLinkedServerName'
GO -- What this thing is doing here???
EXEC('CREATE PROCEDURE whatever
AS
BEGIN
SELECT * FROM ['+@linkedServerName+']. ...
END
GO')
And try this:
DECLARE @SQL nvarchar(4000)
DECLARE @linkedServerName VARCHAR(255)
SET @linkedServerName = 'ProductionLinkedServerName'
SELECT @SQL = 'CREATE PROCEDURE whatever
AS
BEGIN
SELECT * FROM ['+@linkedServerName+']. ...
END
GO'
PRINT @SQL
EXEC (@SQL)
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply