question

siera_gld avatar image
siera_gld asked

How to pass paramters to a remote procedure

How do you pass parmaters to a procedure being executed on a linked server... (@BEG_DT, @END_DT are the paramaters!!) INSERT INTO #TEMP EXEC ('REPORTING.dbo.PROC_NAME, @BEG_DT, @END_DT) AT [LINKED_SERVER_NAME]
stored-procedureslinked-serverparametersremote
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
I would look at `OPENROWSET` - http://msdn.microsoft.com/en-GB/library/ms190312(v=sql.90).aspx EDIT. OK, so OPENROWSET has been disallowed by your DBA. Fair enough. Here's a useful-looking discussion: [How to pass a variable to a linked server query - MS Support site][1] What about EXEC LinkedServer.DatabaseName.SchemaName.StoredProcName @param1, @param2... If that doesn't work, then `OPENQUERY` may also be a way to go: DECLARE @OpenQuery varchar(max) --assuming your parameters are integers. If strings, or dates, then you'll need to add some ''''s SELECT @OpenQuery = 'SELECT * FROM OPENQUERY (LinkedServer, ''SELECT * FROM foo WHERE bar BETWEEN ' + @Param1 + ' AND ' + @Param2 + ''')' INSERT INTO #TempTable EXEC (@OpenQuery) (Untested code) [1]: http://support.microsoft.com/kb/314520
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

it may be technically correct, however, mr dba has closed the possibility of conducting an openrowset
0 Likes 0 ·
@siera-gld OK - I've made a couple of other suggestions...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.