Hi,
We created a JDBC Adapter connection with XA_TRANSACTION (which our Sybase Database supports) using the datasource class : com.sybase.jdbc2.jdbc.SybXADataSource.
Then we created an Adapter Service in Developer to be able to execute an Insert SQL int the same database.
However, in Developer, as soon as we click/select a table (on the drop-down of the table name) it cannot retrieve the table fields and gives the following pop-up screen:
com.wm.pkg.art.error.DetailedServiceException: [ART.114.243] Adapter Runtime (Metadata): Failed to run resourceDomainLookupValues service. Details provided in error log.
[ADA.1.319] Cannot get the list of table columns. "
(ZZZZZ/2762) The ‘CREATE TABLE’ command is not allowed within a multi-statement transaction in the ‘tempdb’ database.
"
The ‘CREATE TABLE’ command is not allowed within a multi-statement transaction in the ‘tempdb’ database.
com.wm.pkg.art.error.DetailedServiceException: [ART.114.243] Adapter Runtime (Metadata): Failed to run resourceDomainLookupValues service. Details provided in error log.
[ADA.1.319] Cannot get the list of table columns. "
(ZZZZZ/2762) The ‘CREATE TABLE’ command is not allowed within a multi-statement transaction in the ‘tempdb’ database.
"
The ‘CREATE TABLE’ command is not allowed within a multi-statement transaction in the ‘tempdb’ database.
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:125)
…
We have looked this up on Google and it seems that this is a problem for WebLogic too ([url]http://publib.boulder.ibm.com/infocenter/wasinfo/v5r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/rtrb_sybaseprobs.html[/url]) and that other applications that have similar functionality (to retrieve table fields with a XA connection) also get the issue.
We also discovered in many forums the suggestion to enable the Sybase DB option “ddl trans”…but this does not seem to solve the issue and also is not recommended by Sybase since it can generate more contention/table locks on the DB.
It seems that the stored procedure that is called by Developer in Sybase is trying to create a temporary table which is not allowed…
Anyone come accross this issue before and solved it?
Thanks,
Tiago A.