SQL distributed transaction. Problem with getting data from SQL VIEW

Question

I'm an Microsoft SQL VIEW's end-user on SQL 2000.

Up to now I'm using Excel Microsoft Query to fetching data. It works properly.

I've decided to improve my work and rearange working with this VIEW.

I wrote python script that should fetch data, do some calculation, export to Excel and send it to many recipients. But python script is having a problem with getting data, showing me an error:

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. (7391) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] (7312); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. (7300)`

Excel connection works proper only python script not working.

Python script is quite simple and works with other VIEWs placed on this server.

connectionString = f'DRIVER={{SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};WSID={WSID};APP={APP}'
conn=pyodbc.connect(connectionString)

sql = 'SELECT * FROM VIEW_DATA'
conn_cursor=conn.cursor()
cursor.execute(sql)
sql_data = cursor.fetchall()

Maybe someone have similar problem and solved it?

It make me crazy that Excel works but python not.

Answer 1

As @siggemannen

Try using autocommit=True when creating connection perhaps. Just a guess but perhaps pyodbc starts some transaction which sql server 2000 doesn't like.