|
|
Posted by Erland Sommarskog on 03/18/06 12:55
Sergey (afanas01@gmail.com) writes:
> It looks like a mystery, but I hope there should be some explanation to
> the issue I experience. Once in a blue moon a random stored procedure
> stops working the way it was designed. The stored procedure code looks
> unchanged. Recompiling, altering the code do not help. It looks like it
> is simply does not execute some part of it or does not run at all.
> However it returns no errors.
> One time a procedure entered into infinite loop and almost hang the
> whole server.
>
> When I copy procedure code and save it under different name, it works
> as designed. But nothing helps with existing procedure. The only way
> how to fix it is to completely drop and recreate.
Have you ruled out the possibility there are two procedures with the same
name, but different schema? (= different owner in SQL 2000?) Or for some-
thing really exotic, what about numbered procedures? (That is, some_sp;2)
If you for instance change the code of the corrupted procedure to say
"PRINT 'Hello!" and nothing else, does print the message, or does it
continue what it used to do?
When this happens a SELECT * FROM sysobjects WHERE name = 'procname'
may reveal something.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|