June 12, 2006 at 9:20 am
More traffic has begun to flow through our system and as a result I've been getting more and more deadlocks. On one of the tables we created a view to check out a "queue" in access. Would having this view open contribute to my problem?
June 13, 2006 at 6:28 am
Kevin,
I am assuming that you are getting a deadlock message (e.g. you have been chosen as the loser in a deadlock) from SQL Server 2000. This error can manifest itself as a result of several scenarios:
1 - The bandwidth from the client to the server is getting clogged up. (Solve by tracing, possibly moving to dedicated pipe).
2 - You are using an on-timer event to query the SQL server db from a form in Access. It may appear to work for a period then all of a sudden, it stops and gives you the error. Consider revising the code to open a forward-only, static dataset and store the data in variables.
3 - You are writing T-SQL strings and executing on the adp side. Take a look at the statements and consider transferring to stored procedures and/or functions.
4 - Consider writing DTS packages that execute at given period intervals directly on the SQL Server. This will definitely cut down on the traffic tie-ups because their is no client interface from Access executing queries.
We have 300+ nodes running simultaneously. We have found, with the diversity of hardware on the floor, that sometimes the nic card in a particular machine is bad and is "chirping" the server trying to re-establish connection.
Also, try not to bind forms to tables / queries. Instead capture data, display and use update routines to modify the data based on a form's field content.
Look for any list or combo box that are loading large recordsets. This too can cause challenges.
Hope some of these ideas help you solve your challenge.
Best Regards,
Mike Jensen - Senior Architect
Plexent, LLP (www.plexent.com)
June 13, 2006 at 7:35 am
Thank you Mike, I guess my question was more geared to binding forms and how an updatable snapshot can affect deadlocks. And if it does a table lock or row lock. We only use the Access Client on our LAN, which I assure you has enough badwidth.
June 13, 2006 at 9:53 am
Kevin - Thanks for the detailed reply. It's a great checklist to keep handy.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply