Is there any known SQL Server bug whereby a record can be successfully
inserted and committed, but then later be found not to be in the
database? For example, if there was a server crash just after the
commit, could committed data be lost?
I'm sure the answer must be "no", but a client is telling me this is
happening, and I said I'd enquire. 4 2499
Tony (an******@onete l.net.uk) writes: Is there any known SQL Server bug whereby a record can be successfully inserted and committed, but then later be found not to be in the database? For example, if there was a server crash just after the commit, could committed data be lost?
I'm sure the answer must be "no", but a client is telling me this is happening, and I said I'd enquire.
Of course, in case of a crash or a hardware error, the database could
become corrupt, in which case you could lose data. But in such cases
you should run DBCC CHECKDB to investigate.
If data appears to be lost, but the database appears to be safe and sound,
there are two possibilities:
1) Data was never committed, but was believed to because of an application
error. (There are a few gotchas that could lead to this.)
2) There is some other process that deletes the rows for one reason or
another.
In case of the latter, Lumgients Log Explorer ( www.lumigent.com) can
be invaluable tool to track down what is happening.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Tony" <an******@onete l.net.uk> wrote in message
news:c0******** *************** **@posting.goog le.com... Is there any known SQL Server bug whereby a record can be successfully inserted and committed, but then later be found not to be in the database? For example, if there was a server crash just after the commit, could committed data be lost?
Very unlikely, but possible.
I'm sure the answer must be "no", but a client is telling me this is happening, and I said I'd enquire.
What makes them think this is happening?
> If data appears to be lost, but the database appears to be safe and
sound, there are two possibilities: 1) Data was never committed, but was believed to because > of an
application error. (There are a few gotchas that could lead to this.) 2) There is some other process that deletes the rows for one reason or
another.
Yes, my suspicion is that it is possibility (1). The application is
written in ASP using ODBC to connect to the database. Autocommit is
used. The logic is something like this:
1) Insert record into table
2) Redirect to another page to print out a certificate
Allegedly, there are a few people holding certificates where no
corresponding record exists in the database. There is no code in the
app. that deletes this data, though of course it could be deleted
manually.
I have verified that if the insert fails (e.g. if I add a check
constraint that will always be violated), the ASP page aborts and does
not redirect to the next page.
Are there any particular scenarios I should look out for?
Thanks for your quick response.
Tony
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Tony Andrews (an******@onete l.net.uk) writes: Yes, my suspicion is that it is possibility (1). The application is written in ASP using ODBC to connect to the database. Autocommit is used. The logic is something like this: 1) Insert record into table 2) Redirect to another page to print out a certificate
Allegedly, there are a few people holding certificates where no corresponding record exists in the database. There is no code in the app. that deletes this data, though of course it could be deleted manually.
I have verified that if the insert fails (e.g. if I add a check constraint that will always be violated), the ASP page aborts and does not redirect to the next page.
Are there any particular scenarios I should look out for?
There are a few nasty situations, which must be handled properly in
application code.
Say that you call a stored procedure which starts a user-defined
transaction. That stored procedure runs for a longer time, because of
the query or because of blocking. By default, all client libraries
have a timeout which sets in after 30 seconds (if no data have been
seen), and cancels the query. Contrary to what you may think, this DOES
NOT rollback the transaction! Thus, in your error-handling code you
need to issue "IF @@trancount > 0 ROLLBACK TRANSACTION".
Note here that even you don't use stored procedures but issue plain
INSERT statements, this can happen if there is a trigger on the table.
A trigger alwyas executes in the context of a transaction. (You may
get an automatic rollback when you cancel execution in a trigger; I
have not tested this case, but I would not trust on getting a ROLLBACK.)
There are some variations of this theme. If a stored procedure refers
to a non-existing table, the procedure will abort on that statement,
without rollback any transactions it may have started. Again, the
client could as a matter of routine always issue rollback in case of
an error.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jim the Bad |
last post by:
Hi, I wonder if anyone can help me with this.
Bog standard SuSE 8.2 Linux install.
I have installed mySQL (off the SuSE 8.2 distro CDs). I can connect locally,
e.g:
bealzebub> mysql -u root -p mysql -h localhost
....logs me in just fine. However, this:
|
by: marius |
last post by:
Hi!
I'm currently trying to move from MS SQLServer to MySQL, running on
windows XP.
When trying to connect to mysql on localhost with mysql control center
I get the 2013-error "Lost connection to MySQL server during query".
I can connect from command line. If I do so, and change passwords by
using OLD_PASSWORD('<password>'), control center runs nicely, but then
ByteFX fails to connect with "Access denied for user...".
|
by: Yuri Vanzine |
last post by:
When I read and then write an xml file using XmlTextReader and
XmlTextWriter, the resulting file has the funky ???characters appended to
the very beginning of file.
The file otherwise comes out to be very useable, the xml is valid. It just
bugs me that some garbage characters can be inserted by the xml parser
without me consenting to it. The chars are not visible in VS.NET, look them
up in hex.
Is this a MS problem?????
|
by: Jan Keller |
last post by:
Hello all,
I am using an XmlTextReader and a DataSet to populate a DataGrid:
ds = New DataSet
xr = New XmlTextReader(path)
ds.ReadXml(xr)
xr.Close()
dv = New DataView(ds.Tables.Item(0))
|
by: TJRobertsJob |
last post by:
Was wondering if someone could help. Over the last month I've been developing a
small database application, using Access 2000, for use in a friends shop.
Everything was going well until about a week ago when I started getting the
following error when I would click any buttons on my main form that ran either
VB code or an Access Macro:
"Error accessing file. Network connection may have been lost.".
I know for sure it's not a network...
| |
by: Carlo Razzeto |
last post by:
Hello there, I am currently working on an email archiving program that is
supposed to copy the entier contents of an email (as downloaded via webdav
from exchange) into a mysql database. This project is being written in C#
with the use of the MySQLDriverCS driver for database connectivity
(originally I was trying oledb with a mysql oledb driver but this didn't
work well). My problem is when I try to insert email attachments of a
"large"...
|
by: Micah Williamson |
last post by:
Does anyone know how to make it so that a time entered
as a string, and stored in a string record in an access
2000 database can be kept as a string?
When ever I enter a time like 1:00:00 PM into a bound text
box, then I update the access database, the value changes
to a date with time attached. Access then takes the time
portion away, and only leaves the date.
The dataset recognizes the field in the database as text,
|
by: TJS |
last post by:
in vbscript there was a command called "execute" which would process a
dynamic string, vb.net dropped that feature ...
does anybody have a working solution for sale or free that will execute a
string in vb.net
|
by: Stefan De Schepper |
last post by:
Dear NG,
I created a gridlike usercontrol. When clicking on a cell a textbox (or
other control, depending on the cell's editortype) is shown. When some other
control gets the focus, the textbox is hidden. So far, no problem.
But when the textbox is visible and, for instance, I click on a
toolbarbutton, the textbox' Lostfocus event is not fired. The situation
should be that wherever I click, apart from the cell or textbox itself, the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: 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...
| |