Stored Procedure Timing Out

Stored Procedure Timing Out

Post by Kanwaljeet Grewa » Thu, 30 Mar 2000 04:00:00



I have a stored procedure on 6.5(service pack 3, I think) on nt4 service
pack 5.  The procedure is just creating a temporary table, inserting a few
rows, selecting from that table and then dropping that table.

The problem is that the procedure is executed some 15,000 times a day on
average and after about a week or so it stops returning data and just times
out.

This is rectified by droping and recreating the procedure.

Any help on why is is doing this and how to correct the problem would be
greatly appreciated.

Cheers,

Kan

 
 
 

1. Time-outs on only 2 of many stored procedures

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,
Theresa

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. HELP ! replication doesn't work

3. Stored Procedure Initiated Call Outs

4. SQL language standards

5. Continual time-outs (time is not on my side)

6. Check on duplicate values

7. Time-outs that didn't happen under NT4sp6

8. how to connect to MSAccess database??

9. query time outs! please help

10. Time-outs

11. Time Outs When running a query

12. SQL Server time outs

13. Setting Time Outs