Stored procedure using SSIS variables from EXECUTE SQL TASK

• PSB

  SSChampion

  Points: 13195

  More actions

  October 11, 2011 at 2:49 pm

  #245186

  Hi,

  I have a sp that looks like this:

  CREATE PROCEDURE [dbo].[TEST]

  (

  @ServerName VARCHAR(100),

  @DatabaseName VARCHAR(100)

  )

  AS

  BEGIN

  SET NOCOUNT ON

  SELECT * FROM [@ServerName].[@DatabaseName].dbo.Abcd

  END

  I want to add the [] in front of the Servernames and database name.

  The above code fails with the following error below :

  Could not find server '@ServerName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

  I have to use it in an execute sql task and get values for these parameters from SSIS variables .

  My server name is like for example ServerName\ABCD . So if I donot use the square bracket it will fail.

  I have these Variables set up in SSIS .(ServerName and DatabaseName) .

  I am not sure how to use them in the stored proc with the [] .

  Please help .

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  October 11, 2011 at 2:52 pm

  #1393172

  Duplicate post, please make all responses to http://www.sqlservercentral.com/Forums/Topic1188781-392-1.aspx

  CEWII