October 17, 2021 at 4:33 am
Hello everyone!
We have a small group of users (4) using MS Access 2000 to run queries on our MS SQL 2000 SP3 server. When specific queries are run, a deadlock is created, disabling all access to the server. We narrowed it down to these specific queries. We've tried setting the queries to "Record Locks: no lock" and "Recordset Type: SnapShot" in the query's properties and it still causes deadlocks. If someone can provide me with any information that might help me, I would be very happy.
Thank you.
October 17, 2021 at 7:02 pm
Really need to upgrade.
SQL2000 and Access 2000 really using 21+ year old technology in this day and age your asking for issues.
Your going to have to look at the queries and look at what’s causing the deadlocks and tune them so they don’t.
Make sure table access in each query is the same order etc.
October 18, 2021 at 12:36 pm
Wait, a deadlock is an error that will clear immediately. It won't lock the server and prevent access. I think you're saying that you're getting blocking that is preventing access. That suggests an open transaction. Find and kill it. Then fix the code so that it doesn't open a transaction and then leave it.
Oof, trying to remember how to deal with this in SQL Server 2000 where there isn't a single DMV... sp_who? Is that right for back then? I think it is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2021 at 12:47 pm
AFAIK you cannot disable an index on SQL2000 !
syntax "alter INDEX myix ON myschema.mytable disable;" only works as of SQL2005 ...
And even if you can, DO NOT disable index id 1 ( i.e. the clustering index )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply