This is a follow on question to a previous question - Not able to create database using stored procedure pyodbc SQL SERVER

In the previous question I received an ERROR_MESSAGE() -

CREATE DATABASE statement not allowed within multi-statement transaction.

This was solved by setting autocommit=True in pyodbc

import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server=SQL01;Trusted_Connection=yes;",autocommit=True)
#conn.autocommit = True
cursor=conn.cursor()
cursor.execute("""\
    DECLARE @RC int
    DECLARE @ClientName varchar(255)
    SET @ClientName = 'Test2'
    EXECUTE @RC = [test].[dbo].[ClientDBCreation] @ClientName
""")
conn.commit()

After creation of the 'Test2' database, [test].[dbo].[ClientDBCreation] stored procedure goes on to create a few stored procedures in the newly created Test2 DB . From pyodbc these stored procedures are not created.

In SSMS executing [test].[dbo].[ClientDBCreation] works perfectly and gives the desired results.

0 Answers