Sybase 2762 - The 'Create table' command not allowed within a multi-statement transation in the 'tempdb' database" - ON OLD STOR PROC

Question

I have got the following error Sybase 2762 - The 'Create table' command not allowed within a multi-statement transation in the 'tempdb' database" - ON OLD STOR PROC

The database settings are what is expected : sp_dboption, ddl in tran set to 'true'

This is an old procedure that has not been changed and was working well before. However, we have recently performed a major release on the server.

My question is, would dropping and recreating the procedure help?

This is a production error.

Answer 1

When you get this error in any database - be it user database or tempdb (if the table name starts with # or explicitly specified in tempdb), it specifies the database name. Same can be verified from official site:

The '%s' command is not allowed within a multi-statement transaction in the '%.*s' database.

As mentioned by markp-fuso, you can have multiple tempdb based on system requirement of your application.

Please use that database and enable "ddl in tran" option as below:

USE master
go
exec sp_dboption 'db_name', 'ddl in tran', true
go
USE db_name
go
checkpoint
go

I hope this would solve underlying issue. Dropping and recreating procedure is not required.