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.
5  3972 
Sergey (af******@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usua lly 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 (af******@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.Usua lly 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: M Wells |
last post by:
Hi All,
I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'
To explain our situation:
We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.
|
by: jrefactors |
last post by:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?
Please advise good references for Oracle stored procedures also.
thanks!!
|
by: scott |
last post by:
Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the...
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.
I wanted to implemented a generic "Helper" class like this:
/**
* Helper
|
by: ship |
last post by:
Hi
We need some advice: We are thinking of upgrading our Access database
from Access 2000 to Access 2004.
How stable is MS Office 2003? (particularly Access 2003).
We are just a small company and this is a big decision for us(!) It's
not just the money it's committing to an new version of Access!
| | |
by: Tim Marshall |
last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with
interest and on reading the post describing Lauren Quantrell's
SmartTree, I've run into something I don't understand: Stored
Procedures. I thought stored pricedures were an Oracle/MS SQL Server
thing and don't know how they work with Access Jet. I've looked at some
of the help on stored procedures in A2003, but really don't understand
what's going on.
Can someone...
|
by: mooreit |
last post by:
The purpose for my questions is accessing these technologies from
applications. I develop both applications and databases. Working with
Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test
Environments.
What is the purpose of a view if I can just copy the vode from a view
and put it into a stored procedure?
Should I be accessing views from stored procedures?
|
by: robert.waters |
last post by:
Hello,
I have been experiencing crashes and code corruption in my project
(vbe6.dll; a decompile fixes the corruption); for the life of me I
cannot figure out why, and I can't pin down the offending code because
each crash immediately shuts down the database and forces me to
recompile in order to open it again. I am following proper coding
procedures, e.g. creating and destroying objects properly (AFAIK),
closing adodb recordsets...
|
by: peter |
last post by:
I am trying to get a SQL stored procedure to use user maintained MQT
implicitly which raises questions on when they are used or not used.
In theory you would expect the stored procedure to pick up the MQT at
the time it is bound on the creation of the static SQL. This raises
the question on how you stop it or start it using a MQT as there is no
option on the bind. What happens when it is rebound? What happens if
the plan is made invalid...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
