0

I'm using ceODBC to connect to sql-server 2014 from a centos 6 box from python 2.7.9.

In a critical part of our code, after inserting rows into a table, I want to do double check that all rows have arrived safely. I want to do this because sometimes an error happens, but ceODBC does not throw an error, and the table is empty.

To make sure that in between inserting data and doing a 'count statement' no other parts of the code do any inserts I want to lock the table. This is where I have my problem. It seems that there is a sp_getapplock build into sql-server, but when I do the following:

import ceodbc
conn = # Make connection here
cursor = conn.cursor()
cursor.execute("declare @result int; exec @result = sp_getapplock @Resource='Dim_Date', @LockMode='Exclusive'; select @result").fetchall()

The result sometimes is a 0, sometimes a -999, but never is the table locked for other connections.

Does anyone know what I''m doing wrong?

(I added the pyodbc tag because I think the two drivers are similar.)

Peter Smit
  • 1,594
  • 1
  • 13
  • 27
  • How many rows are you inserting? SQLServer won't elevate to a table lock until after some largish number of rows are changed (about 5000 is the figure I saw hre a couple of days ago, but can't confirm personally). – simon at rcl Apr 30 '15 at 12:21
  • Depends. Some jobs insers 10s of tables, some jobs insert millions. I just want to do a count after inserting to check if the amount of rows in the table matches the expected number. – Peter Smit Apr 30 '15 at 12:51
  • This might be a way: http://stackoverflow.com/questions/2272733/how-to-get-number-of-rows-inserted-by-a-transaction – simon at rcl Apr 30 '15 at 12:53
  • Nope, the @@rowcount is always 0 after an executemany command from ceODBC. – Peter Smit May 04 '15 at 07:21

0 Answers0