Stored procedure doesn't return dataset unless recompiled

  • Thread starter Thread starter Dewey
  • Start date Start date
D

Dewey

I have a web app that calls a SP. Twice in the last month
the app has locked up. The first time we ran a SQL Trace
which showed that the SP had "completed". But no dataset
was returned and ultimately the app "timed out" by
presenting an NT challenge dialog.

Here's the weird part: if we recompile the SP everything
works fine again for the time being.

We're running SQL 2000, Windows 2000 Server, .NET
Framework 1.1. Anything else I can add?

Have you heard of this before?

Thanks,
Dewey
 
Have you changed any of the objects called by the stored procedure? For
example, altered a table or view? If so, the stored procedure actually
errored internally but did not return an error message. If this is the case,
you can avoid this embarrassment, at least in SQL Server 2000, by indicating
that the table has dependencies. SQL Server will not allow you to change the
object until you pull off this "dependency lock".

The same can be accomplished by always recompiling sprocs any time you
change an object, even if there is no changes to the sprocs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
 
Back
Top