Forcing a table lock in SQL Server 2000

We are testing a program change that locks a table when it updates.  There can be multiple requests for this update and we have error handling that does this.  We want to test this functionality before we move it to Production, but I'm having a hard time finding a way to force a table lock so that we can test the error processing.  I have seen a way through bcp, but I'd prefer not to do it that way.  In the query, we are locking using a Transaction and the TABLOCK and HOLDLOCK hints.  This is in SQL Server 2000 accessed through vb.net.

Thanks
dgoncherAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
yanochConnect With a Mentor Commented:
Just by writing the following in your sql console it will lock from updating :
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
select * from youtable

when you want to remove the transaction
COMMIT TRANSACTION

0
 
yanochCommented:
In your vb app, start a transaction
Then select * from your table
Then sleep // do your tests
Then rollback
0
 
dgoncherAuthor Commented:
"In your vb app, start a transaction
Then select * from your table
Then sleep // do your tests
Then rollback"

This did not work.  I started a transaction in vb.net did the select * and a sleep for a minute.  While it was sleeping, I ran the other program which reads then updates the record and it successfully read and updated.

Any other ideas?
0
 
dgoncherAuthor Commented:
This worked thanks.  If anyone else has this issue, make sure you turn off the option in Query Analyzer that disconnects after query executes before you run the SQL.
0
All Courses

From novice to tech pro — start learning today.