Re: Stored procedure doesn't return dataset unless recompiled

From: Cowboy \(Gregory A. Beamer\) (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 12/19/04 

Date: Sun, 19 Dec 2004 14:12:58 -0600

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!
*************************************************
"Dewey" <anonymous@discussions.microsoft.com> wrote in message 
news:3f2501c4e2c7$781444a0$a301280a@phx.gbl...
>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

Relevant Pages

  • Re: Just for kicks, try this
    ... Robbe Morris - 2004-2006 Microsoft MVP C# ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... app using ado.net execution time of some stored procedures has increased ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MSDE Slow in executing Stored Procedures
    ... App that creates an ADO connection object and execute a simple ... stored procedure and its equivalent insert statement. ... MSDE 7.0) vs. SQL Server 2000. ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... I have 2 apps doing the same job, one is older and using adodb to query ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
Loading