Server timeout/not responsive when running St. Proc.

[christheprogrammer]

Hi,
I am running a stored procedure which itself calls about 10 other stored procedures. Approximately 75% of the time, I get the error 'Server timed out or is not responding'. The other 25% of the time the data I want is returned perfectly. I figured it was an issue of how busy the server was (if other people were running queries) at the time of the request. Is this the case? If so (or even if not), how can I bring these timeouts down so that my stored procedure returns the requested data in a more reliable fashion (100% of the time)?
TIA Chris says: "It's time for a beer."

 

[dbtech]

Are you calling the SP from Visual Basic code? if yes then you can try to set the CommandTimeout property of connection/command object to 0, CommandTimeout=0

if you are running from Query Analyser
Go to
Query Analyzer-> Menu Tools-> Options,
Tab "Connections"-->specify timeout value for query.

Set the timeout value to 0

did this help?

dbtech

 

[fluteplr]

I would look at your code and what else is running on your server. What you describe sounds like a deadlock.

Process A has table1 locked and needs table2
Process B has table2 locked and needs table1

The next time it hangs but before it times out I would have a look to see what resources are locked. You can do this in enterprise manager.