Stored procedure time out - but fine when run from SSMS

Question

I have a stored procedure which is erroring with "Timeout expired".

The code involved is ADO/VB6.

The stored procedure itself is not a problem, you can run it in a query window and it takes less than a second.

The code used to get the connection etc is also modularised and in use all over a huge application. It is only in this one place that the timeout occurs, on one particular database.

The error will be reproducable every time for hunderds of attempts, whether running the VB6 code in debug or not, then suddenly everything will magically start working again. Then some time in the future the same problem will show up again.

I'm not sure how much code to put here, there's nothing complex about it; it's basically;

Set adoCommandObject.ActiveConnection = ...{open ADODB.Connection object}
Set rs = CreateObject("ADODB.Recordset")
Call rs.Open(adoCommandObject, , adOpenForwardOnly, adLockReadOnly)'Timeout occurs here

I've been watching in profiler but that hasn't given any clues, except for occasionally seeing "SET NO_BROWSETABLE ON" / "SET NO_BROWSETABLE OFF" statements occurring before and after the sp runs.

I've searched the net but not been able to find any satisfactory help for this; I'm willing to try anything at this point (except rewrite in .NET, unfortunately that's not an option!)

Answer 1

• Maybe there's some code lurking around that accidentally sets the connection or command's timeout to a really small value.
• Maybe the procedure really does occassionally take a while to run, e.g. if the server is doing something else or the statistics are out of date
• Can you capture a case of the timeout using profiler, and if so does the proc actually take a long time to execute?

As described here SET NO_BROWSETABLE ON is similar to using FOR BROWSE in selects. I guess it's auto-generated by ado when it thinks you might want to update that record set. There's probably a property of the Recordset you could set to stop those being issued, but it seems unlikely that's the problem.

Answer 2

I think you're over thinking this.No offense, but if you are using MSSQL, it's as simple as someone leaving a query window open and it ties up the database. This is easily tested. I've had the same trouble before. I've run stored procedures before that had no timeout, that normally would run immediately but would sit overnight and not run. Only to find out another employee left their query window open. Close their window and poof it finally runs. Check this out, you'd be suprised what a table lock can do to your application.

I say this because you said that the problem is intermitten. It comes and goes. I suspect a table lock. Whether it be the application doing it, or it is being done by another user making queries to the DB. If it's not another user, then check to make sure your application is closing connections to the DB each time their being used.

Answer 3

Did you identify the solution as I am in a similer situation!