none
Calling DB2 Stored Procedures from SQL Server Management Studio RRS feed

  • Question

  • Hi:

         I have linked a DB2 i Series linked server, using OLE DB Provider for DB2 ver 8.0.4294.0, on a SQL Server 2008 R2, and enabled the RPC option.

         From SQL Server Management Studio I can execute Select, Insert and Delete queries against DB2 tables.

         Using the same account on the USER ID of the @provider sp_addlinkedserver paratemer, I created a simple stored procedure on the DB2.

         I ran the stored procedure in the i series navigator, and run ok.

         But when invoke the stored procedure from SQL Server Managemente Studio, it seem like is run ok, but the stored procedure didn't run:

                 EXEC('CALL MYDBSHM.MYSIMPLESP()') AT MYLIKEDDB2

         On Messages tab show:

                    Command(s) completed successfully.

         I used the same account to create and run the stored procedure on the i series navigator and on the liked server on SQL.

         All suggestions are wellcome.

         Thanks in advance for your comments

    Regards

    Norzagaray

    Thursday, February 3, 2011 2:13 AM

Answers

  • What is your stored procedure doing? Is it actually returning anything?

    If your stored procedure has a OUT parameter, and it is written properly,

    	OUT O_PRODID INTEGER , 
    	OUT O_ORDYEAR CHAR(4) , 
    	OUT O_ORDMONTH CHAR(3) , 
    	OUT O_QTY SMALLINT , 
    	OUT O_AMOUNT DECIMAL(9, 2) ) 

    and you populate it in the stored procedure:

    DECLARE C1 CURSOR FOR 
    	SELECT PRODID , ORDYEAR , ORDMONTH , QTY , AMOUNT 
    	FROM CNWIND . SALES WHERE PRODID = I_PRODID ; 
    
    FETCH C1 INTO O_PRODID , O_ORDYEAR , O_ORDMONTH , O_QTY , O_AMOUNT ;

    in SSMS, you should be able to do this:

    declare @var1 int
    declare @var2 char(4)
    declare @var3 char(3)
    declare @var4 int
    declare @var5 decimal(9,2)
    
    exec('call mystoredproc(?,?,?,?,?)',@var1, @var2, @var3, @var4, @var5) at <linkedserver>
    print @var1
    <etc>
    

    Charles Ezzell - MSFT
    • Marked as answer by Norzagaray Wednesday, February 9, 2011 3:59 PM
    Friday, February 4, 2011 7:25 PM
  • Using SSMS it does not appear to be possible. DQP (the data query processor) apparently does not like return codes that are not a listed parameter on the stored procedure. Also, there appear to be some issues in C# also that I'm investigating on the side. Getting the parameters is easy however.

    The below calls a stored procedure with 10 parameters. 5 input, 5 output. The stored procedure returns a select * plus the five parameters inserted (gathered from another select statement). The table has a unique column also to avoid duplicate rows.

    declare 	@INPRODID INTEGER
    declare 	@INORDYEAR CHAR(4) 
    declare 	@INORDMONTH CHAR(3) 
    declare 	@INQTY SMALLINT
    declare 	@INAMOUNT DECIMAL(9, 2)
    declare 	@OUTPRODID INTEGER
    declare 	@OUTORDYEAR CHAR(4)
    declare 	@OUTORDMONTH CHAR(3)
    declare 	@OUTQTY SMALLINT
    declare 	@OUTAMOUNT DECIMAL(9, 2)
    declare		@OUTRETURN INTEGER
    set 	@INPRODID = 6
    set 	@INORDYEAR = '2011'
    set 	@INORDMONTH = 'Jan' 
    set 	@INQTY = 100
    set 	@INAMOUNT = 99.25
    exec('call CHARLIEE.SP_INOUTSALES(?,?,?,?,?,?,?,?,?,?)',
    	@INPRODID, @INORDYEAR, @INORDMONTH, @INQTY, @INAMOUNT, 
    	@OUTPRODID output, @OUTORDYEAR output, @OUTORDMONTH output, 
    	@OUTQTY output, @OUTAMOUNT output) 
    at POWER6L1
    
    select ProdId = @OUTPRODID, OrdYear = @OUTORDYEAR, 
    	OrdMonth = @OUTORDMONTH, Qty = @OUTQTY, Amount = @OUTAMOUNT
    

    output from the above: ResultSet from stored procedure:

    PRODID	ORDYEAR	ORDMONTH	QTY	AMOUNT
    6	2011	Jan	100	99.25
    

    select statement:

    ProdId	OrdYear	OrdMonth	Qty	Amount
    6	2011	Jan	100	99.25

     using a 4-part named query will fail.


    Charles Ezzell - MSFT
    • Marked as answer by Norzagaray Saturday, February 12, 2011 12:21 AM
    Wednesday, February 9, 2011 4:14 PM

All replies

  • What does your stored procedure do? Did you setup RPC on your linked server?
    Charles Ezzell - MSFT
    Thursday, February 3, 2011 2:27 PM
  • Dear Charles:

         The "simple" stored procedure wasn't simple at all. There was some SET statements that caused the trouble.

         Still I don't know exactly what SET is, I removed all and now the execution from SQL Management of the stored procedure is running ok and fast!!

         In order to get a feedback from the remote stored procedure execution, how can I get the return values and output parameter form SQL Manager?

         Thanks for your time.

    Regards

    Sergio Norzagaray

    Friday, February 4, 2011 6:05 PM
  • What is your stored procedure doing? Is it actually returning anything?

    If your stored procedure has a OUT parameter, and it is written properly,

    	OUT O_PRODID INTEGER , 
    	OUT O_ORDYEAR CHAR(4) , 
    	OUT O_ORDMONTH CHAR(3) , 
    	OUT O_QTY SMALLINT , 
    	OUT O_AMOUNT DECIMAL(9, 2) ) 

    and you populate it in the stored procedure:

    DECLARE C1 CURSOR FOR 
    	SELECT PRODID , ORDYEAR , ORDMONTH , QTY , AMOUNT 
    	FROM CNWIND . SALES WHERE PRODID = I_PRODID ; 
    
    FETCH C1 INTO O_PRODID , O_ORDYEAR , O_ORDMONTH , O_QTY , O_AMOUNT ;

    in SSMS, you should be able to do this:

    declare @var1 int
    declare @var2 char(4)
    declare @var3 char(3)
    declare @var4 int
    declare @var5 decimal(9,2)
    
    exec('call mystoredproc(?,?,?,?,?)',@var1, @var2, @var3, @var4, @var5) at <linkedserver>
    print @var1
    <etc>
    

    Charles Ezzell - MSFT
    • Marked as answer by Norzagaray Wednesday, February 9, 2011 3:59 PM
    Friday, February 4, 2011 7:25 PM
  • Hi Charles:

         Currently the stored procedure only insert constant values, but I need add booth IN and OUT parameters; that is why your example resolve the problem!!.

         There is only a questions. How get the Reuturn Value or Return code of the sotored procedure?

          Thanks a lot.

    Sincerely

    Sergio Norzagaray

    Friday, February 4, 2011 8:23 PM
  • Using SSMS it does not appear to be possible. DQP (the data query processor) apparently does not like return codes that are not a listed parameter on the stored procedure. Also, there appear to be some issues in C# also that I'm investigating on the side. Getting the parameters is easy however.

    The below calls a stored procedure with 10 parameters. 5 input, 5 output. The stored procedure returns a select * plus the five parameters inserted (gathered from another select statement). The table has a unique column also to avoid duplicate rows.

    declare 	@INPRODID INTEGER
    declare 	@INORDYEAR CHAR(4) 
    declare 	@INORDMONTH CHAR(3) 
    declare 	@INQTY SMALLINT
    declare 	@INAMOUNT DECIMAL(9, 2)
    declare 	@OUTPRODID INTEGER
    declare 	@OUTORDYEAR CHAR(4)
    declare 	@OUTORDMONTH CHAR(3)
    declare 	@OUTQTY SMALLINT
    declare 	@OUTAMOUNT DECIMAL(9, 2)
    declare		@OUTRETURN INTEGER
    set 	@INPRODID = 6
    set 	@INORDYEAR = '2011'
    set 	@INORDMONTH = 'Jan' 
    set 	@INQTY = 100
    set 	@INAMOUNT = 99.25
    exec('call CHARLIEE.SP_INOUTSALES(?,?,?,?,?,?,?,?,?,?)',
    	@INPRODID, @INORDYEAR, @INORDMONTH, @INQTY, @INAMOUNT, 
    	@OUTPRODID output, @OUTORDYEAR output, @OUTORDMONTH output, 
    	@OUTQTY output, @OUTAMOUNT output) 
    at POWER6L1
    
    select ProdId = @OUTPRODID, OrdYear = @OUTORDYEAR, 
    	OrdMonth = @OUTORDMONTH, Qty = @OUTQTY, Amount = @OUTAMOUNT
    

    output from the above: ResultSet from stored procedure:

    PRODID	ORDYEAR	ORDMONTH	QTY	AMOUNT
    6	2011	Jan	100	99.25
    

    select statement:

    ProdId	OrdYear	OrdMonth	Qty	Amount
    6	2011	Jan	100	99.25

     using a 4-part named query will fail.


    Charles Ezzell - MSFT
    • Marked as answer by Norzagaray Saturday, February 12, 2011 12:21 AM
    Wednesday, February 9, 2011 4:14 PM
  • Dear Charles:

            Yes C# has an issue with DB2 stored procedures, but with output parameter working around is easy, thanks to your examplem code.

            I really appreciate your patency and plain code.

    Best Regards

    Sergio Norzagaray

    Saturday, February 12, 2011 12:24 AM
  • Sergio,

    For what it's worth, I brought this up last week, and we may be doing some talking to the SQL folks on what is (and isn't) supported from SSMS, etc, and seeing if some changes can be made. I know for a fact SQL hard-codes a few things only for SQL<->SQL talking.

    Charles


    Charles Ezzell - MSFT
    Wednesday, February 23, 2011 12:36 PM
  • I did get this to work through SSMS.
    Friday, October 20, 2017 2:05 PM