Get storedProc result with parameters from a linked server

• Dominic Gagné

  SSCrazy

  Points: 2654

  More actions

  April 8, 2011 at 9:53 am

  #237474

  Hi,

  I need to extract data from a linked server by using a StoredProc with a parameter. I wanted to store the result in a temporary table and then, doing some manipulations I need to do before dropping the temp table. I tested with hardcoded value and it worked perfectly. Now, I need to parametrize the query. Here is what I tried:

  SET @NoDossier = '1315-21738'

  SELECT *

  INTO #MyTable

  FROM OPENROWSET('SQLNCLI',

  'Server=SQL2008;Database=xDManagement;Uid=user;Pwd=Passw;',

  'EXEC xDRxVGetPil_sp ''''' + @NoDossier + '''''');

  OPENROWSET doesn't accept parameters, as I learned this morning. So, is there a way I can call a SP with parameters on a linked server and put the result in a temp table, like I wanted to do?

  thanks for your time and help

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 8, 2011 at 10:27 am

  #1309288

  Dominic Gagné (4/8/2011)

  ---

  Hi,

  I need to extract data from a linked server by using a StoredProc with a parameter. I wanted to store the result in a temporary table and then, doing some manipulations I need to do before dropping the temp table. I tested with hardcoded value and it worked perfectly. Now, I need to parametrize the query. Here is what I tried:

  SET @NoDossier = '1315-21738'

  SELECT *

  INTO #MyTable

  FROM OPENROWSET('SQLNCLI',

  'Server=SQL2008;Database=xDManagement;Uid=user;Pwd=Passw;',

  'EXEC xDRxVGetPil_sp ''''' + @NoDossier + '''''');

  OPENROWSET doesn't accept parameters, as I learned this morning. So, is there a way I can call a SP with parameters on a linked server and put the result in a temp table, like I wanted to do?

  thanks for your time and help

  Dynamic SQL with EXEC() will probably do what you want.

  Something along these lines:

  DECLARE @sql NVARCHAR(MAX),

  @NoDossier NVARCHAR(20) ;

  SET @NoDossier = N'1315-21738' ;

  -- note the use of a global temp table, it's important because the

  -- dynamic sql will execute on a new thread, i.e. different scope,

  -- so a local temp table in the dynamic sql will not be visible in

  -- your scope

  SET @sql = '

  SELECT *

  INTO ##MyTable

  FROM OPENROWSET(''SQLNCLI'',

  ''Server=SQL2008;Database=xDManagement;Uid=user;Pwd=Passw;'',

  ''EXEC xDRxVGetPil_sp ''' + @NoDossier + ''');

  ' ;

  EXEC(@sql) ;

  SELECT *

  FROM ##MyTable ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Sioban Krzywicki

  One Orange Chip

  Points: 27770

  More actions

  April 8, 2011 at 10:35 am

  #1309291

  One way is to create the query as a string, put it in a variable and execute the variable

  DECLARE @LinkedQuery varchar(500)

  DECLARE @NoDossier varchar(10)

  SET @NoDossier = '1315-21738'

  SET @LinkedQuery = 'SELECT RowID, AColumn, AnotherColumn, ThisDate

  INTO #MyTable

  FROM OPENROWSET(''SQLNCLI'',

  ''Server=SQL2008;Database=xDManagement;Uid=user;Pwd=Passw;''

  ''EXEC xDRxVGetPil_sp '''''' + @NoDossier + ''''''')';

  EXEC (@LinkedQuery)

  You have to be really careful with your quotes though.

  And I haven't tried this with executing a query within an openrowset, but I've done it with SELECT statements and the principle is the same so I think it'll work.

  --------------------------------------
  When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
  --------------------------------------
  It’s unpleasantly like being drunk.
  What’s so unpleasant about being drunk?
  You ask a glass of water. -- Douglas Adams