Table Locking in SQL Server 2000

[curmudgeon42]

I have a table of orderIDs that allows me to get a new order ID simply by doing an insert into the table. I then use this orderID in other tables throughout the database. I need this not to duplicate, so I am trying to learn T-SQL to do so.

I have this so far:

BEGIN TRANSACTION

INSERT INTO orderIDs (used) VALUES (1)

IF @@ <> 0
ROLLBACK TRANSACTION

SELECT MAX(orderID) newID FROM orderIDs TABLOCKX

IF @@ <> 0
ROLLBACK TRANSACTION

COMMIT TRANSACTION

As you can see, I have used Exclusive Table Locking in the SELECT statement which returns the new orderID. However, I would like to use the same locking in the INSERT statement. I can't seem to find where in the INSERT statement I should put the TABLOCKX keyword in order for it to work without throwing a compile error.

I am using SQL Server 2000 and I don't have administration rights to the server, if that is any help.

Thanks a lot,
Justin

[curmudgeon42]

Nevermind, Figured it out

You have to do the following

INSERT INTO tablename WITH(TABLOCKX) (columns) VALUES (values)

figured i'd post it if anyone else has the problem

justin