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

Question

I am working on java project in which i am using Sybase and Apache Metamodel to get table data and its information.Now "ddl in tran" option is true but still when i get columns details using Apache Metamodel its giving me error:

com.sybase.jdbc4.jdbc.SybSQLException: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

I have Sybase ASE 15.5 installed on Windows 10.

Answer 1

The "ddl in tran" options needs to be "on" in the temporary databases as well. The system administrator or someone with sa_role can do this:

1> use master
2> go
1> sp_dboption tempdb, 'ddl in tran', 'true'
2> go
Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases.
Database option 'ddl in tran' turned ON for database 'tempdb'.
Running CHECKPOINT on database 'tempdb' for option 'ddl in tran' to take effect.
(return status = 0)

As the warning suggests this needs to be consistent across all temporary databases if you have multiple. Once the command has completed the options can be checked with sp_helpdb:

1> sp_helpdb
2> go

[stuff deleted]

 tempdb                            104.0 MB              sa             2      Aug 05, 2019             no_recovery                     0               NULL        
 select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, mixed log and data, allow wide dol rows 

Here's an example of creating a table inside a transaction:

1> use tempdb
2> go
1> begin tran
2> go
1> create table foo ( k int not null, value varchar(25) null) 
2> go
1> commit
2> go
1> 

Answer 2

This can be used (sp_dboption tempdb, 'ddl in tran', 'true') but it's not a good practice to set this to true, it may cause concurrency problems in tempdb.

Must be careful using that option.