Timeout Expired for SP

[jluost1]

I created a Stored Procedure that will restore a database backup with a new name.

I can run the SP successfully with Query Analyzer (it is fast, around 10 seconds).

When I trigger it in my ASP page, it runs for about 30 seconds then I got the error message:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired "

I ran the following command in Query Analyzer:

exec sp_configure 'resource timeout', 150
RECONFIGURE

But this doesn't help.

Also In my connection string, I set "connectionTimeout=100".

Can anyone tell me how to solve this problem?

 

[Ruairi]

Using ADO in VB i debugged long running procedures by setting the CommandTimeout (not connection timeout) to 0, which will avoid the error no matter how long the sp takes to execute. Obviously this is not a permanent solution, but will allow it to run from the asp page. Then, in the stored procedure, you can create a temporary table that tracks how long operations take. Example:

Create Procedure dbo.TimeOperations

@SomeInput1 int,
@someInput2 varchar(25)

AS

select 'OPERATION'='Start: SP', 'Time'=GETDATE()
into ##TimeOps

select 'OPERATION'='Start: First Querey', 'Time'=GETDATE()
into ##TimeOps

-- ** run a querey

select 'OPERATION'='Done: First Querey', 'Time'=GETDATE()
into ##TimeOps

select 'OPERATION'='Start: Someop ', 'Time'=GETDATE()
into ##TimeOps

-- ** do something else

select 'OPERATION'='Done: Someop ', 'Time'=GETDATE()
into ##TimeOps

select 'OPERATION'='Done: SP', 'Time'=GETDATE()
into ##TimeOps

GO

This is from memory, so the format may not be perfect. Anyway, GETDATE() will insert the current date and time into the temp table created with the select into. ## makes TimeOps a global temp table, if your asp page is releasing it's connection too soon (the table doesn't exist when you try to querey it) you might need to create a normal table with a string and datetime field and use insert instead of select into. This should tell you where the holdup is occuring. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[jluost1]

Thank you, Ruairi. You are exactly right. It is commandTime out, not connectiontimeout.

I figured it in my own way. Had I got this message earlier, it could have saved me an hour.

Thank you very much.