How can I execute a cross-server Stored Procedure in pymssql without using Transactions?

Question

I'm trying to insert data (using a stored procedure) from one server to another. This is a restriction of the server architecture, and I'm forced to work around it.

That being said, I have a stored procedure on serverA that can normally connect to serverB and can normally perform the operation since the two servers are linked.

The problem is that pymssql uses transactions, so I keep getting this error:

File "pymssql.pyx", line 465, in pymssql.Cursor.execute (pymssql.c:7190) pymssql.OperationalError: (7391, b'The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerB" was unable to begin a distributed transaction. DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server.)

When I preface the stored procedure with a BEGIN TRAN on the server, I see the same error, so I'm concluding that because pymssql uses transactions, I might not be able to do a cross-server distributed transaction.

Here's the code:

def databasedata(start, end):
    startday = datetime.datetime.strptime(str(start), '%m/%d/%Y')
    endday = datetime.datetime.strptime(str(end), '%m/%d/%Y')
    conn = pymssql.connect(  user=os.getenv('SQLUSER'),
                             password=os.getenv('SQLP'),
                             server='serverA',
                             database=os.getenv('SQLDB'))
    cursor = conn.cursor()
    cursor.execute(' select day from dbo.valid_days '
                   'where day between %s and %s',
                   (startday, endday)
                   )
    days = cursor.fetchall()
    for day in days:
        date = ('%s-%s-%s' % day[0].timetuple()[:3])
        cursor.execute('exec [hooper].[dbo].[cross_server_generation] %s', date)
        conn.commit()


if __name__ == '__main__':
    (firstday, secondday) = (sys.argv[1], sys.argv[2])
    databasedata(firstday, secondday)

Answer 1

Instead of using pymssql, I opted to run this as a subroutine via subprocess.

import subprocess

def databasedata(start, end):
    startday = datetime.datetime.strptime(str(start), '%m/%d/%Y')
    endday = datetime.datetime.strptime(str(end), '%m/%d/%Y')
    conn = pymssql.connect(  user=os.getenv('SQLUSER'),
                         password=os.getenv('SQLP'),
                         server='serverA',
                         database=os.getenv('SQLDB'))
    cursor = conn.cursor()
    cursor.execute(' select day from dbo.valid_days '
                   'where day between %s and %s',
                   (startday, endday)
                   )
    days = cursor.fetchall()
    cursor.close()

    for day in days:
        date = ('%s-%s-%s' % day[0].timetuple()[:3])
        statement = 'exec [hooper].[dbo].[cross_server_generation] \'%s\'' % date
        subprocess.call(["sqlcmd", "-S", sqlserver, "-Q", statement])


if __name__ == '__main__':
    (firstday, secondday) = (sys.argv[1], sys.argv[2])
    databasedata(firstday, secondday)