Time-outs on only 2 of many stored procedures

Time-outs on only 2 of many stored procedures

Post by Theresa Rie » Tue, 18 Sep 2001 02:35:26



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!

 
 
 

Time-outs on only 2 of many stored procedures

Post by Theresa Rie » Tue, 18 Sep 2001 03:03:22


UPDATE!

When I went to compare the indexes from the previous server to the new server, I found I had never had an index set up on either server.  I decided that now was a great time to add one.  It seems to have resolved my time-out issues completely.  I cannot explain, however, why the stored procedures worked on the previous server without indexes, but not on this new server.  

Theresa

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

 
 
 

1. Stored Procedure Initiated Call Outs

In designing a three tier solution, I am assessing methods of communication from
SQL Server 7 to Application Server, initiated by triggers.

Automation
Call outs to a COM Server appear to be the only option that I have read, and it
is suggested that there are limitations to this.   (sp_OACreate/sp_OAMethod are
slow and serialized).

Can anyone suggest other methods?

Thank you.

Damian Jolly

2. Alternative to MDB for desktop solutions

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

4. Selecting from Master/Detail w/ Detail as columns of Master

5. MS SQL Server 2000 time-outs periodicly over the internet

6. New sections in Forums opened

7. Time outs

8. rows numbering

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

10. Time outs!

11. query time outs! please help

12. Distribution Agent Time Outs

13. Performance trouble, time outs