none
CREATE DATABASE statement not allowed within multi-statement transaction. error occured. RRS feed

  • Question

  • I have to create database using the SQLClient namespace in dotnet class library inside windows application. I have a script file with all

    script inside it. I am reading script file sequencially and generate a sqlCommand for each compleate sql statement inside the file.

    After completion of file reading I have to run sqlCommand inside a single Transaction so that if any error is occured, rollback can be

    possible. All the sqlCommands are run without error until "Create Database" sqlCommand is not going to execute. On the

    "Create Database" sqlCommand I stuck on error i.e. "CREATE DATABASE statement not allowed within multi-statement transaction."
    Wednesday, October 7, 2009 9:13 AM

Answers

  • Basically the error message say everything. You cannot use CREATE DATABASE DDL statements inside of the transaction and you cannot rollback creating of the objects actions.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Wednesday, October 7, 2009 9:52 AM
    Moderator
  • If CREATE DATABASE statement fails then it cannot be in inconsistent state, because database will not be created at all. What you do later is another story, but the database exists or does not exist and there is no something is a between
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Thursday, October 8, 2009 10:17 AM
    Moderator
  • Take note of this remark in the CREATE DATABASE documentation:
    The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.

    Therefore, check that you have SET IMPLICIT_TRANSACTIONS OFF
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Wednesday, October 7, 2009 9:57 AM

All replies

  • Basically the error message say everything. You cannot use CREATE DATABASE DDL statements inside of the transaction and you cannot rollback creating of the objects actions.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Wednesday, October 7, 2009 9:52 AM
    Moderator
  • Take note of this remark in the CREATE DATABASE documentation:
    The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.

    Therefore, check that you have SET IMPLICIT_TRANSACTIONS OFF
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Wednesday, October 7, 2009 9:57 AM
  • Hi,

    Thanks for the reply. If any error is occurred during the CREATE DATABASE command execution then what will happened? It may be possible that database

    is in inconsistent state.

    Wednesday, October 7, 2009 11:43 AM
  • If CREATE DATABASE statement fails then it cannot be in inconsistent state, because database will not be created at all. What you do later is another story, but the database exists or does not exist and there is no something is a between
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Nitin J Jain Friday, October 9, 2009 5:17 AM
    Thursday, October 8, 2009 10:17 AM
    Moderator
  • Hi,

    Thanks, Now things are more clear to me. I can execute Create Database without transaction and for all other changes with - in

    transaction.
    Friday, October 9, 2009 5:17 AM
  • Hello Mazur, 

    Thanks for clearing up this mystery of the exception popping during the creation of database under transaction scope.

    It does make sense to me that database creation should not be under the transaction coz it is created as one step job or atomic job. 

     

    Now, this also means that i cant use Entity Framework Code First (EF 4.1) that creates database against the business model under TransactionScope block eg:

     

    using(TransactionScope scope = new TransactionScope())

    {

    using(MyModelcontext context = new MyModelContext())

    {

    context.SomeClass.Add(object);

    contest.SaveChanges();

    }

    }

     

    above code will not work as DbContext class will try to create the database if that doesnt exist so we cant use TransactionScope session with EF 4.1 code first.

    the work around is simple, create the database without TransactionScope then put CRUD operations under TransactionScope.......

    any suggestions one this ?

    Regards

    Yawar.


     




    Sunday, April 3, 2011 10:17 PM
  • If CREATE DATABASE statement fails then it cannot be in inconsistent state, because database will not be created at all. What you do later is another story, but the database exists or does not exist and there is no something is a between
    Val Mazur (MVP) http://www.xporttools.net


    Yes of course, but the whole point of a transaction is that a block of statements can succeed or fail as one unit. Wouldn't it be awesome if it was possible to execute any script within a transaction, so it could be EASILY undone if ANYTHING went wrong?

    I thought MSSQL now had fully supported transactional DDL-statements. I am disappointed to find this doesn't apply to CREATE (nor ALTER) DATABASE, and frankly it is difficult to understand why. It seems to me to make every bit as much sense for these DDL statements as it does for others - though let me also say I think it is fantastically useful that it DOES support this for changes within a catalog.

    I'll now attempt to split my script so the CREATE + ALTER database stuff can run without a transaction, and run the rest of it within the transaction. Hopefully the only additional cleanup my code then needs to handle is DROP DATABASE (if anything goes wrong after the CREATE succeeded).

    Monday, November 7, 2011 1:21 PM