2

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.

user2379077
  • 21
  • 1
  • 2
  • Do you have multiple temporary databases and if so, have you verified 'ddl in tran' is enabled/true for all temporary databases (including 'tempdb')? – markp-fuso Aug 13 '18 at 11:54
  • you can run commit before the table creation then try – Moudiz Aug 14 '18 at 11:30

1 Answers1

0

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.

ouflak
  • 2,458
  • 10
  • 44
  • 49
Learning_DBAdmin
  • 105
  • 4