August 26, 2009 at 5:24 am
***Oops wrong forum; mods please close I will recreate on T-SQL forum***
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 needs brackets ...
OPENQUERY ( linked_server ,'query' )
Any help is appreciated.
G
August 26, 2009 at 9:45 am
You can build your SQL String into a variable and use the variable instead of doing the concatentation in OPENQUERY.
I don't think you are gaining anything by using OPENQUERY for the call over just using 4 part naming or a a SYNONYM. I'd use 4 part naming Server.Database.Schema.Procedure Param1, Param2
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply