Executing stored procedure with Parameters on Linked Server

Question

Post reply

Executing stored procedure with Parameters on Linked Server

Grinja

Hall of Fame

Points: 3583
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: 3583 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://qa.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?