Post reply

Passing Params to Linked Server Stored Procedure

  • 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+"'' ');

  • July 9, 2005 at 6:28 pm

    #572712

    Can't you just do this?

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

  • July 10, 2005 at 8:21 am

    #572726

    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.

  • July 10, 2005 at 9:12 am

    #572731

    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 !!!**

  • July 10, 2005 at 9:36 am

    #572733

    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.

  • July 10, 2005 at 9:55 am

    #572734

    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 !!!**

  • July 10, 2005 at 10:14 am

    #572736

    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!

  • July 10, 2005 at 1:01 pm

    #572742

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

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

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