Re: SQLNCLI is not allowing CREATE DATABASE inside a TRansaction

(zunilp@xxxxxxxxx) writes:
Thanks for the reply.
But in the real programming world, this is not true.

I can prove that using a microsoft tool or some test program using
OLEDB or JDBC.

Example
If you use RowsetViewer supplied by microsoft,
with SQLOLEDB(my mdac is 2.8 sp1), Inside a trnsaction you can
successfully call CREATE DATABASE.

No:

BEGIN TRANSACTION
go
CREATE DATABASE holger
go
ROLLBACK TRANSACTION

This yields:

Server: Msg 226, Level 16, State 5, Line 1
CREATE DATABASE statement not allowed within multi-statement transaction.

What may heppen with SQLOLEDB is that it under some circumstances can
open a second connection to SQL Server behind the back. This will
typically happen if you initiate a new operation on your session object
before you have picked up all result sets. If that second connection is
not part of the transaction, your CREATE DATABASE will pass. But, obviously,
if you rollback, the database will still be there, because it was
created outside the transaction.

The same tool if you use with SQLNCLI, it will throw back error
mentioned by me.

SQLNCLI should also be able to open a second connection, but your case
may be one where they plugged a hole which caused SQLOLEDB to open a
transaction.

I'm not acquainted with the rowset viewer you mention, so I can't test
with it.

The same thing can be reproduced using a java-jdbc program. I you wish
I can post that some test program using JDBC
Behaviour is totally different.

Java and JDBC is completely foreign land to me.


--
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: 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: Checkpointing Not Happening in Simple Recovery Model
    ... The log cannot be truncated beyond the first open transaction. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ... we can manually issue a checkpoint. ...
    (microsoft.public.sqlserver.server)
  • 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)
  • 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)