Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Linked server procedure call

    Hello!

    I have a hard time and begin to lose hope....

    I am trying to call a DB2 procedure in SQl server through a linked server object.
    I use the IBMDADB2 provider for the linked server. on the DB2 I have a simple procedure with 2 in and 1 out paramater. on DB2 it works no problem.

    but in SQL server I just cannot get it to work. I googled and googled and found no solution to this.

    so my code:
    DB2IBM is the name of the linked server.


    declare @I_DIS smallint
    declare @I_UID char(8)
    declare @ID_TICK int

    alternative 1:
    Exec ('Call btp.GET_TICK(?,?,?)',@I_DIS, @I_UID, @ID_TICK OUTPUT) AT DB2IBM

    alternative 2:
    EXECUTE DB2IBM..BTP.GET_TICK 2,'19', @ID_TICK


    in both cases I got the same error:

    OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2IBM" returned message " CLI0100E Wrong number of parameters. SQLSTATE=07001".


    so how can I call this procedure? any ideas??

    thank you in advance!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If the procedure on the DB2 server takes two parameters, do you really need to pass three to it? I'm sure I've seen code that declares a parameter to receive an output, and the format
    Code:
    SET @Output_Param = EXEC (Invocation goes here with input parameters)
    Not sure if this will be useful, but here's hoping!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    3
    i tried to do it (invoke without out parameter) , but I get another error:


    OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2IBM" returned message "[DB2/NT] SQL0440N No authorized routine named "BTP.GET_TICK" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    That would imply that either the connection string is using an account that doesn't have permission to see or use the procedure, or that the parameters are arriving at DB2 in the wrong format. Given that the earlier errors said nothing about authority problems, I'd go with the latter. However, I've never tried this, so I'm fumbling in the dark as well.

    A Google search ("Invoke a DB2 procedure from SQL Server 201") returned the following page (among others). Might be useful?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2013
    Posts
    3
    the link you mention is for the Microsoft provider for DB2. I have tried this and it works. But unfortunately I can not use this provider - I have to use the IBM provider

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Then I would suggest that you talk to IBM's tech support people - I'm out of ideas
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •