3

I am using pyodbc in python 2.7 with MS SQL Server 2008R.

Here is my code for creating a database (the SQL code which works fine in SQL alone, but crash when executed in python)

SQL_command = """
                IF EXISTS(SELECT * FROM sys.databases WHERE [name] = 'NewDatabase')
                    DROP DATABASE NewDatabase
              """

conn.cursor.execute(SQL_command)

SQL_command = """
                CREATE DATABASE NewDatabase
                ON     
                (
                     NAME = 'NewDatabase_data'
                ,    FILENAME='D:\MSSQL\DATA\NewDatabase_data.mdf' 
                ,    SIZE = 4096KB 
                ,    FILEGROWTH = 4096KB 
                ) 
                LOG ON  
                (
                    NAME = 'NewDatabase_log'
                ,   FILENAME='D:\MSSQL\LOG\NewDatabase_log.ldf' 
                ,    SIZE = 4096KB 
                ,    FILEGROWTH = 10%
                ) 
                COLLATE SQL_Latin1_General_CP1_CI_AS
              """

conn.cursor.execute(SQL_command)

SQL_command = """
                ALTER DATABASE 
                    NewDatabase
                SET RECOVERY SIMPLE
              """

conn.cursor.execute(SQL_command)

However, I've got following error message:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')

May I know what is wrong with my code?

Many thanks.

====================================================================================

So, after taking the advice from @Matthias, I've executed commit after the drop database, then the error message became:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE FILE encountered operating system error 123(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'D:\MSSQL\DATA\NewDatabase_data.mdf'. (5123) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (1802)")

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
ChangeMyName
  • 7,018
  • 14
  • 56
  • 93
  • Try to do a `COMMIT` after you execute the `DROP DATABASE` and `CREATE DATABASE` commands. – Matthias Dec 12 '14 at 09:42
  • @Matthias Thanks for your reply. I've tried your advice, still no good. But the error has changed, the new error message is posted, please have a look. Many thanks. – ChangeMyName Dec 12 '14 at 09:46
  • try: `autocommit = True` see `pyodbc.connect: (str, autocommit='False', ansi='False', timeout='0', **kwargs)`. – Vishnu Upadhyay Dec 12 '14 at 09:46
  • Just guessing: Looks like your program doesn't have write permission in this folder. – Matthias Dec 12 '14 at 09:48
  • @Matthias Thanks for the reply. Yes, I have permission to write in the folder. – ChangeMyName Dec 12 '14 at 09:56
  • @ChangeMyName Make sure the path exists and the *SQL Server service account* has permission to the folder. Your personal account permissions aren't relevant. – Bryan Dec 12 '14 at 19:24
  • Hopefully this is still of some use to you, but as I'm working with PyODBC myself right now, I would recommend executing each statement individually instead of as a batch. The reason for this is that block comments in python, if properly indented, cannot ignore their leading whitespace. It's easier to relay the commands as a series. Where blocks are necessary, you can pass the block through a library like `textwrap` to get rid of the leading whitespace for each line. – jwarner112 Aug 06 '15 at 14:32
  • Possible duplicate of ["CREATE DATABASE statement not allowed within multi-statement transaction" when using pyodbc](http://stackoverflow.com/questions/42006192/create-database-statement-not-allowed-within-multi-statement-transaction-when) – Gord Thompson Feb 02 '17 at 18:43

0 Answers0