How to use a Cursor opened by a Stored Procedure on DB2

Question

Post reply

How to use a Cursor opened by a Stored Procedure on DB2

moz987

SSC Veteran

Points: 222
More actions
January 18, 2014 at 4:45 am

#303420

Hello everyone,
i am currently stuck at the problem mentioned the title. I have a Stored Procedure on DB2 which opens a Cursor to deliver me data. I am able to call this Procedure with the following command:
execute('call mySchema.myProcedure()') at DB2_Linked_Server
After committing on my SQL Server 2008 in the SQL Server Management Studio i get the data filled in the Results tab. But i don't have a clue how to catch/get the cursor so that i can actually work with the cursor.
I hope someone can help me. Thanks in advance for your time and efforts

Viewing 5 posts - 1 through 4 (of 4 total)

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

WolfgangE SSCertifiable Points: 6328 More actions · Answer 1

It think the following should do it:

create table #tmpSomeData ( col1 ..., col2... )

insert int o#tmpSomeData ( col1, col2, ... )

execute('call mySchema.myProcedure()') at DB2_Linked_Server

The table needs as much columns as your result set and appropriate data types.

Then the execute-statement inserts the data in the table.

moz987 SSC Veteran Points: 222 More actions · Answer 2

Hi,

i tried your suggestion and failed with this Error-Code:

'[DB2] SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "15". Subcode = "15". SQLSTATE=58005'

The distributed transaction coordinator is started and Network DTC Access and XA Transactions are allowed. I don't understand what the problem is..

The reason code 15 means that the transaction doesn't exist but i dont know why

WolfgangE SSCertifiable Points: 6328 More actions · Answer 3

It seems that it's not a SQL but a DB2 exception. Unfortunately i can't help you with this.

Maybe you find some DB2-specialists around somewhere...

moz987 SSC Veteran Points: 222 More actions · Answer 4

Hey,

i figured out how to make it work in my case. In the linked server options there is a field called Enable Promotion of Distributed Transactions for RPC which was set to True. After i changed it to False it worked fine. Seems to be a problem with the creation of a protectet distributed transaction, maybe cause by our firewall.

If i find the time i will try let the ports be opened and test it with the option set to true like it is described here[/url]