I recently rebuilt our windows 2000 server from the ground up. Everything seems to run as it did on the previous server, except for 2 stored procedures. I am a self-taught, on-the-fly VB/SQL programmer and am not sure what is causing this problem. Actually, I'm not sure what information, exactly, to provide in this post to help others help me.
I can tell you this though. I auto generated scripts from the previous server to create the database (tables & stored procedures) on the new server. I then re-loaded data to those tables using DataJunction (as we always do once a month). The data is all there and loaded without issue. Nearly every time I run the program in debug mode, I get the time-out problem. Both the stored procedures that are timing out access the same table and are called one after the other. One gets a recordset and the other does a query on each of those records looking for more specific information within the same table. If certain criteria is met, the record is not inserted into a new table.
About 1/2 the time the 1st stored procedure succeeds without a timeout. When this occurs I invariably get the timeout on the second stored procedure. When run from the executable, records that should not be inserted into the new table are therefore being inserted, which causes a report to be generated that never should have been generated. I did not change anything (that I know of) from the previous server to this new server for these two particular stored procedures.
Things I've tried:
a) increasing the timeout setting from 120 sec. to 240 sec.
b) adding a "DoEvents" statement just before each stored procedure call.
c) removing recent code changes that were unrelated to this portion of my code. Note: I did not discover the problem before making other code changes, but assume the issue was present on this particular server before those changes took place, although I have no proof either way.
Things I'm currently in the process of trying:
c) verifying that indexes I might have set up on previous server possibly didn't get scripted and carried over to the new server.
d) deleting the table, rebuilding it, and re-populating it with data.
My Question is: Does anyone have any suggestions as to what could be causing these time-outs?
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!