3

I am trying to call a stored procedure that creates a Database from pyodbc

The Following is a minimal example of the code

import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server=SERVERNAME;Trusted_Connection=yes;")
cursor=conn.cursor()
cursor.execute("""\
    DECLARE @RC int
    DECLARE @ClientName varchar(255)
    SET @ClientName = 'Test'
    EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName
""")
conn.commit()

The code should ideally create a Database named 'Test' in the SQL Server Instance. This does not produce any error. But the database is not created.

Running 

DECLARE @RC int
DECLARE @ClientName varchar(255)
SET @ClientName = 'Test'
EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName

From SSMS seems to create the database. Already referred a couple of questions including this one.

dCoder
  • 509
  • 3
  • 16
  • Might be worth including the DDL of your SP `sp_NAME`. – Thom A Apr 24 '19 at 11:40
  • On a different note [Is the sp_ prefix still a no-no?](https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix) – Thom A Apr 24 '19 at 11:40
  • 1
    Try `conn.autocommit = True` immediately after opening the connection. – Gord Thompson Apr 24 '19 at 11:55
  • "From SSMS **seems** to create" - What? Either the procedure works in SSMS or it does not. The word "seems" here suggests you just don't know. So - does it work in SSMS? – SMor Apr 24 '19 at 12:01
  • @GordThompson Thanks! This works. Can you leave it as an answer with an explanation as to why `conn.autocommit = True` works when `conn.commit()` does not. – dCoder Apr 24 '19 at 12:03
  • @SMor Sorry English is not my Native language. It does create the database from the SSMS. – dCoder Apr 24 '19 at 12:04

1 Answers1

2

The Python DB API 2.0 specifies that, by default, connections should be opened with autocommit disabled. However, many databases require that DDL statements not be executed within a transaction. Attempts to execute DDL statements with autocommit disabled can lead to errors or unexpected results.

Therefore, it is safer to ensure that DDL statements are executed on a connection where autocommit is enabled. That can be accomplished by setting autocommit=True when opening the connection ...

cnxn = pyodbc.connect(connection_string, autocommit=True)

... or by enabling autocommit on an existing connection ...

cnxn = pyodbc.connect(connection_string)
# ...
cnxn.autocommit = True
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418