September 24, 2003 at 2:48 pm
We have a small group of users (4) that use MS Access 2000 to run queries on our MS SQL 2000 SP3 server. When particular queries are ran, a deadlock is created disabling all access to the server. We have narrowed it down to these particular queries. We have tries setting the queries to "Record Locks: no locks" and "Recordset Type: SnapShot" in the properties of the query and it is still causing the deadlocks. If anyone can give me any information that would help I would be greatful. Thank you.
September 24, 2003 at 4:00 pm
Hi,
I have no idea if this is going to help with your problem, but I had a different problem with calling stored procs in Access and adding the line 'SET NOCOUNT ON' at the top of my stored procs sorted it.
Also, not completely sure how your calling your stored proc, but you could try setting the loctype to adlocOptimistic in ADO code, if optimistic locking suits your needs.
Just a few ideas.
HTH
Cheers
Chris
September 25, 2003 at 6:26 am
We are actually not calling a stored proc., this is a query that is built in Access and runs against the server. Also, another thing that it is doing, it is dependent on a date range. If you run it for only 1 month, it works perfectly fine, if you try to run it for any longer, that is when the deadlock happens. We have checked the databases for corruption and the check is comming back with no errors.
September 25, 2003 at 12:47 pm
One more thing that I just realized about this issue. When you run it say from 5/1/2003 to present date, it creates a deadlock, but if you run it from 5/1/2003 to yesterdays date, it is working fine. You can however get todays information if you run for a month period. Thanks for any help.
September 25, 2003 at 2:16 pm
Hi,
Ok, sorry, I had assumed you were using an access project to run the queries(stored procs) on the server. It looks like you have linked the SQL server tables by ODBC and are using the Jet query engine against these tables.
OK, i'm no expert, but I think what might be happening is that your query is grabbing hold of thes tables hard and not letting go until it has finished running, causing you to be left with a lock on the server. This should be temporary, but Jet queries are fat client so the amount of resource on your users PC's will make a difference to performance. I think that the query could be running out of resource to run and leaving the lock on your SQL box. This would tie with the fact that it works for 1 month but not more. The more data you try to use the more resource it requires on the client.
OK, so what could you do:
1) You could upgrade the PC's of the users, but it would always be a losing battle. Might fix this query but not another. Worth doing if you can afford it but don't rely on it as a solution.
2)Distribute more of your processing to the server as views or stored procs.
3) Look at the possibility of breaking your query into several smaller queries that will complete and release the lock and another query to process these to the final solution.
HTH
Chris
September 30, 2003 at 1:43 am
quote:
Ok, sorry, I had assumed you were using an access project to run the queries(stored procs) on the server. It looks like you have linked the SQL server tables by ODBC and are using the Jet query engine against these tables.
Use "SQL specific pass-through" queries with either readpast or nolock and never use Access "Action Queries" against linked tables
In fact ideally never use the Jet engine ever
Keith Henry
DBA/Developer/BI Manager
Keith Henry
September 30, 2003 at 5:10 am
That is what I suggested to my boss. Thank you for the information.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply