x

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]

more ▼

asked May 16, 2013 at 11:41 PM in Default

avatar image

siera_gld
1k 82 88 93

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

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)

more ▼

answered May 17, 2013 at 09:47 AM

avatar image

ThomasRushton ♦♦
41.6k 20 50 53

it may be technically correct, however, mr dba has closed the possibility of conducting an openrowset

May 17, 2013 at 03:36 PM siera_gld

@siera-gld OK - I've made a couple of other suggestions...

May 18, 2013 at 03:24 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x140
x93
x8

asked: May 16, 2013 at 11:41 PM

Seen: 900 times

Last Updated: May 19, 2013 at 03:59 AM

Copyright 2017 Redgate Software. Privacy Policy