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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
yanochCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.