[odb-users] Note about the creation Microsoft SQL Server database

[Andrey Paraskevopulo]

I need to create database by executing query (CREATE DATABASE).

I’ve tried:

odb::transaction t(db->begin());

db->execute("CREATE DATABASE MYDB;");

t.commit();

But this function throws an exception:

                CREATE DATABASE statement not allowed within
multi-statement transaction.

As I found from the MS documentation for CREATE DATABASE:

The CREATE DATABASE statement must run in autocommit mode (the default
transaction management mode) and is not allowed in an explicit or implicit
transaction.

Creation database from the connection also not working (without transaction)
:

                db->connection()->execute("CREATE DATABASE MYDB;");

throws the same exception.

Because according to ODB manual: The created connection is configured to
use the manual commit mode.



Solution for me is to manually enable autocommit:

                auto conn = db->connection();

       auto handle = conn->handle();

       SQLSetConnectAttrA (handle, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)
SQL_AUTOCOMMIT_ON, 0);

       conn->execute("CREATE DATABASE MYDB;");



I hope this will be helpful for someone.


Andrey Paraskevopulo