mysterious corruption of stored procedures

Hi everyone,

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.

The problem is, that you usually have to do it in the middle of the
business day after you spent few hours trying to realize what went
wrong before you realize that you got another mysterious corruption. Of
cause I have no clue of how to detect such things in advance and to
prevent them from occurring in the future.

I can guarantee that the SQL code in those procedures was absolutely
bug free, fully tested and was working fine for a long time.

For the first time I thought that internal compiled code might corrupt.
In this case altering or recompiling should help.
I also thought about execution plan, but it should be also fixed by
doing things above.
DBCC checkdb does not find any errors
The issue never goes away until stored procedure is manually dropped
and recreated with the same SQL code.

So, I'm asking all if someone experienced something similar and can
explain how to prevent it, please share the knowledge. I would
appreciate any type of help.

Thank you.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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usually when you have a production problem everybody whant
a) have it fixed ASAP and only then b) provide them with reasonable
explanation. I have a problm with b) ;-)|||Sergey (afanas01@.gmail.com) writes:
> No numbered procedures, no different schema/owner. Everything is "plain
> vanilla". Unfortunately, I did not have chance to play with corrupted
> procedures.Usually when you have a production problem everybody whant
> a) have it fixed ASAP and only then b) provide them with reasonable
> explanation. I have a problm with b) ;-)

I would still encourage you to examine sysobjects next time it happens.
Maybe there is something in your system, you did not know of. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again

My INSERT statements works... 8/10 times

I'm using the following code to add some data to a table:
Dim rand As Random = New Random
Dim num As Int32 = rand.Next(10000000)
Dim strConn as string = "......"
Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
Try
conn.Open()
Catch SQLExp as SQLException
Response.Write ("An SQL Server Error Occurred: " & e.toString())
Finally
cmd.ExecuteNonQuery
conn.Close()
End Try

As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss.
If youd like to see more code let me know...
Thanks,
Scottscott, cmd.ExecuteNonQuery() should be in the try, not finally. because if you're getting an sql error, it'll error again when the cmd tries to execute w/o an open connection.

that's one. try stepping through it. Furthermore, don't use close, use conn.Dispose(), and cmd.Dispose(), and equal them to null.

Also, are you sure it's going through that part of the code?|||Are you sure all the vars have the correct data in them? I've not used the .net Rand function yet, should you be seeding it or you may get repeated numbers there too easily?|||I ended up getting it working, thanks guys. Turns out it was some javascript frather down in the code that was f'ing it all up.