SQL Error 2762 - problem creating tables in the 'tempdb' database

[Natalie]

Hi all,

I am running a Powerbuilder 5.5.0.3 app with an Adaptive Server backend
(originally an SQL Anywhere database). In the midst of converting from SQL
Anywhere to Adaptive, I have encountered a problem.

I have a stored procedure that creates a temporary table, performs some
insert statements to put rows in this temporary table, then does a select
from the temporary table to return the rows as the result set for the
stored procedure.

When I run this stored procedure from within SQL Advantage, etc. the stored
procedure works correctly, and returns rows.

When I try to use this stored procedure in PB as the datasource for a
datawindow, I can preview the report from within PB and still get rows.
However, when the PB application runs, and I try to do a retrieve on the
datawindow, the Retrieve function just returns a -1 (all the
settransobjects are being done etc.). By tracking down some error
messaging, I have found that the database appears to be returning the
following message:

SQL Code 2762 encountered

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

I have no idea what this means - I can't find anything on this error
message no matter where I look (Sybase Adaptive Server books, 'net, etc)
and can't work out what the problem is as the stored procedure runs fine
from SQL Advantage, etc. This used to work OK for SQL Anywhere.

Any ideas?

TIA,

Natalie den Duyn

[Ken Drendel]

This error means that you have to put autocommit = true before ytou do the create
table and then you can set it back after that. If you are doing the create table
in the SP select the @@TransCount to see if there are multi. If so then do a
commit for as many trans as there are.
Natalie wrote:

--
Kenneth A. Drendel
ICQ# 34027019
Kdre...@hotmail.com

[Joseph Campbell]

You could also set the DBOption 'DDL IN TRAN' on for the Tempdb database. You should
however be cautioned that multi-statment transactions and tempdb are tough to manage
in the sense that if you are in a transaction and attempt to drop or the system
attempts to drop the tempdb table you are working with the entire transaction set
will rollback due to Transactional Stability.

HTH,
Joe Campbell