Transactions in SQL coming from pymssql rollback on their own

Question

We have a developer connecting to SQL Server using pymssql which uses freetds. His script dynamically generates sql insert queries based on values in a MySQL DB.

The statements are parsed properly and have proper begin transaction/commits in them when you view them in SQL Profiler. The only 'user error message' that comes up a 'changed database context to...' which comes up whenever you issue a USE in SQL. After the batch completes, there is a transaction log event 'rollback' and all the records that were inserted are removed.

We are not using XACT_ABORT_OFF because I haven't seen 'change db context to' be affected by it.

Does anyone have any ideas or experience with this? Thanks!

[edit]: The code copied out of profiler works fine in SSMS using the same user and there are no triggers.

[2nd edit]: inside SQL profiler I see a 'TransactionLog' entry with 'rollback' under eventsubtype, however there isn't a TM:Rollback Tran

Have checked if there is a trigger on the table that is causing the rollback? Have you directly run the query from profiler in SSMS and seen what happens? — HLGEM, Sep 12 '12 at 19:57
Correct, there are no triggers on the tables and when I paste the code in SSMS it works without a hitch using the same username as well. Thanks for the input! — Ali Razeghi - AWS, Sep 12 '12 at 19:59

score 2 · Answer 1 · answered Sep 12 '12 at 20:12

2

Perhaps the connection is not being committed or closed correctly. Check the freetds documentation to ensure that you are using the correct usage patterns. Also you might want to check whether its possible to enable autocommit mode on the connection.

answered Sep 12 '12 at 20:12

ifx

561
2
13

Autocommit, that's what I was looking for. Thanks, I'll look into that now and let everyone know. If someone has other ideas let me know. Thanks! – Ali Razeghi - AWS Sep 12 '12 at 20:24

score 0 · Accepted Answer · answered Sep 12 '12 at 23:01

So after much searching and triple checking the auto commit setting, we caught that 2 variables were very closely named and it was committing the wrong one. There is a mysql and a pymysql module, but in this case we were using pymssql but it was typed in at pymysql instead. Thanks everyone who commented.

Transactions in SQL coming from pymssql rollback on their own

2 Answers2