Creating stored procedure in MS SQL through pyodbc

Question

I am trying to create a store procedure in master in mssql through python code. The following is my code:

import pyodbc

conn = pyodbc.connect("driver={SQL Server};server=localhost; database=master; trusted_connection=true",
                      autocommit=True) 
cursor = conn.cursor()
sqlcommand = """
                USE master
                GO
                CREATE PROCEDURE sp_myCustomSystemProc
                AS
                BEGIN
                    PRINT 'myCustomCode'
                END
                GO
                EXEC sp_ms_marksystemobject 'sp_myCustomSystemProc'
             """     
cursor.execute(sqlcommand)

cursor.commit()

conn.commit()

After running this python code, I am getting this error:

Traceback (most recent call last):

File "auto_complete.py", line 27, in <module>
  cursor.execute(sqlcommand)  
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'GO'. (102) (SQLExecDirectW); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. (111); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'GO'. (102); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'sp_myCustomSystemProc'. (102)")

Can anyone please help me to resolve this?

score 4 · Accepted Answer · answered Dec 16 '18 at 08:26

4

Since your connection string already specifies the master database (i.e. database=master;), simply remove

USE master
GO

from your query.

answered Dec 16 '18 at 08:26

Mitch Wheat

295,962
43
465
541

Creating stored procedure in MS SQL through pyodbc

1 Answers1