Database Issue - Calling procedure.

[vrs1008]

Hi,

DSTX Map is calling a stored procedure which is creating couple of temp table for maintaining complex logic.

In the end stored procedure will return the recordset which Map will capture in the BLOB variable.

When Map calles the procedure it is giving me the following error twice (Stored proc is creating 2 temp tables)in the .dbl file.

-------------------------------------------------------
<3480-3864>: Server(sybase_de) error or informational msg--->
number(2762) severity(16) state(3) line(33) status(6)
error or info: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
procedure: ODS_SP_PF_CLAIMSROUTE
-------------------------------------------------------

I have MERC_TEMPDB system variable set up and point to the correct database.

Regards,
Vibhav1008

 

[janhes]

This seems to be a Sybase problem. Check the net for solutions - it depends on what your stored procedure is doing..

 

[BocaBurger]

The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
Change the statement. Use one per card and scope card.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[vrs1008]

Thanks for the reply...

from investigation,
what I understood was :

Whenever we call any procedure or SQL from DSTX using adapters, it will

begin transaction in Tempdb
executes all the statments
End transaction in Tempdb

Since DDL commands are not allowed in transactions, it failed with the error as

----------------
The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
procedure: ODS_SP_PF_CLAIMSROUTE
-----------------

I tried :

Commiting transaction at the begining of the procedure to make sure that we are not in the transaction and begin transaction at the end of the procedure so that when DSTX ends the transaction, it won't throw any error.

It worked fine...

But I want to control it from DSTX rather then doing it from stored proc.

I can not change the stored proc as it is very complex & we can't avoid creating temp table for handling complex logic.



Regards,
Vibhav1008

 

[BocaBurger]

Based on your answer, it looks like the only soultion is to modify the procedure.

Use the right tool for the job.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!