Executing stored procedure with Parameters on Linked Server

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  August 26, 2009 at 5:46 am

  #135073

  I need to execute a storedproc which accepts parameters on a linked server.

  I'm struggling with the syntax though.

  I have two scenarios:

  Scenarion 1 - sp with no parameters

  INSERT INTO tableX

  SELECT * FROM OPENQUERY ( @sourceLinkedServer, 'EXEC linkedDB.dbo.spX')

  The above works fine.

  Now scenario 2: storedproc needs two INT parameters:

  DECLARE @startMonth INT

  DECLARE @endMonth INT

  SET @startMonth = 200901

  SET @endMonth = 200902

  INSERT INTO tableY

  SELECT * FROM OPENQUERY ( @sourceLinkedServer, 'EXEC linkedDB.dbo.spY ' + @startMonth + ', ' + @endMonth)

  The bit that confuses me is the query portion needs brackets ...

  OPENQUERY ( linked_server ,'query' )

  Any help is appreciated.

  G

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  August 26, 2009 at 7:27 am

  #1044826

  you need to build the ENTIRE statement (the openquery itself AND the query that uses the openquery) as a string and then execute that as dynamic SQL.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  August 26, 2009 at 8:18 am

  #1044887

  Thanks.

  Out of interest sake...

  The reason I've used OPENQUERY is because I got errors when trying to use INSERT INTO-EXEC approach.

  Is OPENQUERY the better approach (although i doubt it) when working with linked servers or should I concentrate on resolving the issue with the INSERT INTO method...

  (I have a post about that specific error somewhere ... will it when i find it)

  EDIT:

  Link to error:

  http://www.sqlservercentral.com/Forums/Topic772108-146-1.aspx?Update=1

  Should the code below not produce a similar outcome as OPENQUERY?

  INSERT INTO tableX EXEC ServerX.DatabaseX.dbo.spX

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  August 28, 2009 at 9:12 am

  #1046300

  While I have not seen the error, I can't think of a situation where OPENQUERY would help your get out of an error during an Insert, unless it had something to do with running a procedure remotely.

  That said - please do post the error when you find it.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?