sql server - Some sql commands and stored procedures work in SSMS, but not from a Python call

Question

I'm invoking SQL commands as well as stored procedures from python. Some of the commands work well. In fact, I've done this kind of thing dozens of times with no problem. Here's an example of some working code in the same file where I have the problem:

cursor.execute(r'select top 10 * from xyz')

for result in cursor.fetchall():
    last_predecessor = result[0]
    last_successor = result[1]
    print(last_predecessor, last_successor)

This works great!

I have this stored procedure:

alter PROCEDURE make_new_table
AS
BEGIN

    drop table if exists abc
    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    CREATE TABLE abc(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [ida_last_update_date] [datetime] NULL,
    [ida_last_update_source_file] [nvarchar](500) NULL,
    [ID_RSSD_PREDECESSOR] [nvarchar](500) NULL,
    [ID_RSSD_SUCCESSOR] [nvarchar](500) NULL,
    [D_DT_TRANS] [date] NULL,
    [TRNSFM_CD] [int] NULL,
    [ACCT_METHOD] [nvarchar](500) NULL,
    [DT_TRANS] [date] NULL
) ON [PRIMARY]

END

I can invoke this from the ssms query window with

exec make_new_table

Also works fine.

But when I try to execute this from python with:

cursor.execute("exec make_new_table ")

It fails. (Tried it with and without the trailing space) No error! It just doesn't do anything.

I also tried to invoke:

cursor.execute('drop table if exists xyz ')

This also fails - doesn't drop the table, but produces no error (and it shouldn't).

So is there some limit to what can be done from pyodbc? Can we not drop and create tables?

question from:https://stackoverflow.com/questions/65900690/some-sql-commands-and-stored-procedures-work-in-ssms-but-not-from-a-python-call

Answer 1

The real solution: The real solution was a change in my init_connection() function which I did not include in my OP. The only change necessary was to make autocommit=True . No need to specify the database in the stored procedure or the call. When I had the previous solution that was inconsistent I had stupidly made two changes and only focused on the one that did not really fix it. It was setting the autocommit that fixed it.

def init_connection(server_name, db_name):
    """
    Connect to SQL Server database
    :param server_name:
    :param db_name:
    :return:
    """
    pyodbc.pooling = False
    try:
        conn = pyodbc.connect(
            r'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server_name + '; 
            Database=' + db_name + ';Trusted_Connection=yes;', timeout=5, autocommit=True)
    except Exception as e:
        print("Unable to connect to database [" + db_name + '] and server [' + server_name + ']')
        print(e)
        exit(1)

    cursor = conn.cursor()
    return [conn, cursor]

I removed my previous (non-working) solution.