Wednesday, February 15, 2012

EXECUTE AT <Linked Server> Question

In a stored procedure, I have the following TSQL statement:

EXECUTE ('Insert Into Federation.dbo.FederationData (FederationData) Values
(?)', @.Buffer) AT [HostBox.abcd.mydomain.com\SQLServer2005,1563];

This works fine. However, I do not want to hard-code the name of the linked server like I have above. I would like to specify it at run-time (because it could be different based on certain situations). I cannot seem to get it to work using a variable to represent the linked server name, so it can be dynamic. Can it be done?

Thanks - Amos

What about something like the following:

DECLARE @.SQL1 varchar(500)

DECLARE @.STR_LINKEDSERVER varchar(80)

SET @.STR_LINKEDSERVER=N'HostBox.abcd.mydomain.com\SQLServer2005,1563'

SET @.SQL1 = N'EXECUTE (''Insert Into Federation.dbo.FederationData (FederationData) Values (?)'', @.Buffer) AT [' + @.STR_LINKEDSERVER + '];'

EXECUTE sp_executesql @.SQL1

No comments:

Post a Comment