Executing stored procedure with Parameters on Linked Server

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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?