MS SQL Table Locked by another user

[skiko]

New to databases, just using a simple dbconnect and dbexec.
having a problem with an Update script if locked by another user. Looking for a way to see if table is locked befor doing update or a way to pass along the SQL error to the user or log.

I am using macro Sched v12 with MS SQL 2005 I qet a quick error box that pops up but not on the screen long, I also get r=0 but also get the same if item is not in the databse.



DBExec>dbH,UPDATE ItemRecord SET basePrice= '%USELL%' WHERE itemCode='%UPC%',r

[adroege]

I don't have MS SQL to test on, but something like this *might* help

Code: Select all

BEGIN TRAN
  SELECT *
  FROM ItemRecord WITH (XLOCK,READPAST) 
  WHERE itemCode='%UPC%


  UPDATE ItemRecord
  SET basePrice= '%USELL%'
  WHERE itemCode='%UPC%
COMMIT

[skiko]

Found this litle peace of code of interest

USE master
EXEC sp_lock2

not sure how to use it yet, but kinda cool
Thanks for your help