Passing Params to Linked Server Stored Procedure

Question

Post reply

Passing Params to Linked Server Stored Procedure

rolus

Mr or Mrs. 500

Points: 518
More actions
July 9, 2005 at 11:59 am

#167721

I am trying to Pass parameters to a stored procedure that then queries an Oracle Linked Server.
Currently I am creating Dynamic SQL in the Presentation Tier, then passing a String Directly to the Linked Server. The issue is managing the queries is becoming cumbersome and confusing, I want to separate the DB layer into StoredProcs, so I can pass values into the Linked Server string. The Stored Proc runs fine if I type the Parameter values manually, I can't figure out how to pass the Parameter into the string value that hits the linked server. Any help is appreciated! Thanks.
The basic syntax I am using is:
@OracleParam varchar(6)
AS
SET NOCOUNT ON
SELECT *
FROM OPENQUERY(MyLinkedServer,
'select FirstName,Lastname,
from myLinkedServer.EmployeeNameTable
where emplid =''"+@OracleParam+"'' ');

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can't you just do this?

Select * from MyLinkedServer.DbName.dbo.EmployeeNameTable where emplid = @Param

rolus Mr or Mrs. 500 Points: 518 More actions · Answer 2

If I understand, this would run the query locally....

I have to use the

SELECT *

FROM OPENQUERY(LinkedServer, 'SQL HERE')

If I don't use this the linked server will return thousands of rows and filter (run the query) on the local machine. This turns a millisecond query into a 2 minute query.

Thanks for the reply.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Rolus - have you tried playing around with the single quotes and double quotes in your t-sql ?! I don't have the means to test this but have you tried:

SELECT * FROM OPENQUERY(MyLinkedServer,

'select FirstName,Lastname,

from myLinkedServer.EmployeeNameTable

where emplid = ' + "'@OracleParam + '" ')';

**ASCII stupid question, get a stupid ANSI !!!**

rolus Mr or Mrs. 500 Points: 518 More actions · Answer 4

I have gotten the Query to RUN, using those techniques, but the parameter value is not passed. The quoted string is merely part of the query, not active in the local server, if that makes any sense? I have tried all kinds of quotes.

Thank you for the help.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Alright! Have you tried alternately passing the t-sql through openrowset instead - just to see if it makes any difference in getting the parameter across ?!

**ASCII stupid question, get a stupid ANSI !!!**

rolus Mr or Mrs. 500 Points: 518 More actions · Answer 6

I found an article, http://www.sommarskog.se/dynamic_sql.html that basically explains that it is not really possible to do this. In the article they explain how to create a new function that dynamically concatenates your input variables and SQL. They also explain that you may as well create your SQL on the client, then pass it in as a string, (which we are doing). I am going to probably going to create some class files to separate it from the presentaiton tier. Pseudo stored procs. I suppose the benefits of stored procs are moot since the string is not processed on the local sql server.

Thanks for the help!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I think you can make this work if you call a StoredProc on the linked server... no need for dynamic sql there.