I've created a stored SQL procedure that will need to be modified to lock the table, run the commands and then unlock the table. I need to do this because I want any other users/programs that will need access to "wait in line" until the procedure is finished. It's short a very short procedure. So in essence, I want to
'Lock the table 'run the commands here SELECT @today = GETDATE(), @ordno = OrderNo FROM ORDRNUMBERREC SELECT MAX(OrderNumberID), @ordno FROM ORDRNUMBERREC SELECT @ordno1 = @ordno + 1 INSERT INTO ORDRNUMBERREC (OrderNo, UserID, CreateDate) VALUES(@ordno1, 'system', @today) 'now Unlock the table
I've been reading on this and find the command, TABLOCKX, and this may be what I'm looking for.
My questions are:
1) where is this command inserted (within the SQL procedure or within the VB code)?
2) is there a "unlock" version of this or does the table unlock when the routine is exited?
In advance, thanks for the help.