My application ceases to respond when an spid XX (blocked) occurs on SQL Server 2000 sp4 .(XX being the number assigned to the user/query) My dot.NET application does not require table or row locks when any "Select" statement is used. Can I resolve this in the application code.
1. Will the use of "NO LOCK" in queries resolve the 'blocking'/"blocked" problem at SQL level.
2. Is there a way of setting up SQL to ensure there are no table locks for all queries from the application.
I have 250 users on the systems currently, hence this is urgent and valued at 500. We have just updated to sp4. Had no problems with previous SQL 2000 installation.