Re: SQLNCLI is not allowing CREATE DATABASE inside a TRansaction

Tech-Archive recommends: Fix windows errors by optimizing your registry

(zunilp@xxxxxxxxx) writes:
Till now I am using SQL server 2000 with SQLOLEDB. Everything is fine.
But now I moved to SQLServer 2005 with SQLNCLI provider. But it throws
an error "CREATE DATABASE statement not allowed within multi-statement
transaction" when our application tries to create a new DB inside a
transaction. All other DDL statements like CREATE TABLE, ALTER etc
works well inside a transaction with SQLNCLI. We found that only CREATE

DATABASE has problem.
This problem is not there with SQLOLEDB.
I know such a problem is there with SYBASE in which by defaukt sybase
wn't allow DDL statements inside a transaction. But they provide an
option DDL_IN_TRAN or something like that. Enabling this will allow
DDL.

How I can make CREATE DATBASE work inside a transaction. Is there any
options available.

No. Books Online for SQL 2000 says in the topic "Transact-SQL Statements
Allowed in Transactions":

You can use all Transact-SQL statements in a transaction, except
for the following statements

ALTER DATABASE LOAD DATABASE
BACKUP LOG LOAD TRANSACTION
CREATE DATABASE RECONFIGURE
DISK INIT RESTORE DATABASE
DROP DATABASE RESTORE LOG
DUMP TRANSACTION UPDATE STATISTICS

In the SQL 2005 you find the same topic on
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ffadadc1-ed01-46a7-923d-bd583141a6cb.htm

I don't know why your code seemed to work with SQOLEDB, but apparently
you were not creating databases within a transaction then.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • RE: Foreign Key Violations During Insert in Transaction
    ... parent row into the database, SQL Server generates a new identity value. ... that value does really exist even before you commit the transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
  • Re: statement not allowed within multi-statement transaction (Long)
    ... So if I do have an open transaction I can't figure ... > 100 Database does not exist in sysdatabases can not dettach ... > --Verify database Exiists for detach ... >> Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Insert Into Without Log
    ... level of logging of logged in the database ... transaction log, however, you can minimize this via setting the database ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)