Run-time error

Hello All:
I am in desparate need of assistance. We are using an application called PaperVision Enterprise which takes scanned invoices and puts them into a database for the purpose of easy reference to any scanned invoice in the db. 2 weeks ago while trying to perform a 'match and merge' of the newly scanned docs, we received a Run-time error '-2147217871 (80040e31)": Timeout expired message. After being told by the company who supports this software that they couldn't help me since it is using a MS SQL Server Table Lookup I started googling the error with minimal success. I am not very proficient with SQL so I'm kind of stuck. I have narrowed this down to an ODBC run-time error, but looking for some kind of guidance as to how I can resolve it. Any info would be greatly appreciated. We are using MS SQL Server 2000.Timeout expired errors are bad to deal with. They are not particularly bad for the data, but they are a pain to track down. The short of it is the client is giving up on the server, after the server takes 30 seconds (default) to return a message about the success or failure of the command. The easiest fix is generally to have the vendor increase their commandtimeout value. Outside of that, you would have to check to see if it is time to purge old documents, reindex tables (if the database is on the order of 10GB or more), or check to see what other activity is going on on the SQL Server (is someone running a giant report at the same time?). Good luck.|||We actually faced this problem 4 years ago on SQL Server 2000. The issue was resolved first by increasing the timeout value in the ASP application code itself (as a temporary measure). However, after tuning the query so that it executed much faster, we switched back to the default timeout in the ASP application code. Everything went fine. If this is static data (committed data) that you are matching and merging, it would be better to use the WITH (NOLOCK) hint to start with. Of course, the query would still have to be optimized. Another case was when a query (with around 5 joins between different tables) was causing Exception Access Violation and failing. When optimized, it ran in a split second with no issues. [As per a Forrester Report (I don't have a copy of it with me right now), 50-60% of database performance issues are caused by the quality of the code.]|||I have increased the timeout value, but still have the same issues. As for the other suggestions, could you possibly recommend any books that would explain how to do some of these things, since I don't know much about SQL?|||I wouldn't use WITH (NOLOCK) unless you know with certainty that the tables involved are not being written to.

It has its own sets of problems since it means you will be doing dirty reads, and can even cause new errors if page splits, etc, occur during the read.

For tips on optimizing queries, start reading the beginner articles at http://www.sql-server-performance.com