8

Im trying to create a database using pymssql and im getting this error.

cur.execute("CREATE DATABASE %s;" % self.getsql('dbname'), conn)

gives

*** OperationalError: (226, 'CREATE DATABASE statement not allowed within multi-
statement transaction.DB-Lib error message 226, severity 16:\\nGeneral SQL Serve
r error: Check messages from the SQL Server\\n')

What does this mean ??

Amber
  • 507,862
  • 82
  • 626
  • 550
jagguli
  • 631
  • 1
  • 7
  • 21
  • Try `cur.execute("create database %s" % (self.getsql('dbname'),), conn)`, also try it without the `getsql` call. – Naftuli Kay Mar 29 '12 at 01:57
  • 1
    If you are running other statements before your `CREATE DATABASE` one, you may need to `commit()` the current transaction before running it. – Amber Mar 29 '12 at 01:59

1 Answers1

10

The issue was that cur.execute starts a transaction every time, but 'CREATE DATABASE' operation cannot be excuted within a transaction

http://social.msdn.microsoft.com/Forums/pl/adodotnetdataproviders/thread/594ff024-8af6-40b3-89e0-53edb3ad7245

>>> connection.autocommit(True)
>>> cursor = connection.cursor()
>>> cursor.execute("CREATE DATABASE Foo")
>>> connection.autocommit(False)

This to works. Strangely its not documented in pymssql ... hmmm

jagguli
  • 631
  • 1
  • 7
  • 21
  • FWIW, [pyodbc also automatically starts transactions](https://github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management), so setting autocommit True solves this for pyodbc too. – pianoJames Aug 01 '18 at 18:17
  • yes this also worked for me. Strangely if you toggle autocommit before and after the create statement it does work. – jpspesh Jan 22 '19 at 22:26
  • How would you provide the `server`, `user`, `password` ? in `connection` – Anu Aug 19 '20 at 05:28