473,721 Members | 1,803 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

WARNING. A simple cut and paste of 8 records can distroy a SQL Server table

Today I need to copy 8 records in a table. I have to use Access 200 because
of the limitation of Enterprise Manager's inability to cope with field with
more than 900 characters. Selected records, cut, paste. I got an erroor
message about not being able to have a null Key_ID (I copied the reords and
tried to paste the Key_ID as part of the records - normally I hide the
Key_ID).
Now I can't access either the new records or the originals that I was trying
to copy (because, it would seem, they have identical primary keys). I also
cannot export the table via DTS 'unspecified error' and 'integrity
violation'.
Or delete the offending records with a Query Anaylyser delete query.
Basically the entire SQL Server database has been destroyed with a couple of
keystrokes.
Now, I've being developing database applications for over 20years and the
one thing, maybe the only thing I expect from a database server is to
protect the integrity of my data. SQL Server does not, it would seem. These
records aren't just any random unimportant records either. They contain the
'create views' that my entire application require to function and each one
approaches the 8000 record limit and have take years to perfect and just
checking that the table is valid could take me days.
Jul 20 '05 #1
5 1818
SWu
sorry if this sounds like a couple of dumb, obvious questions:
- do you have backups of this important code?
- are you able to drop the constraints on the table, remove the offending
records and reapply the constraints?

regards,
stephen
"pete" <pe**@madpete.f reeserve.co.uk> wrote in message
news:40******** **************@ news.zen.co.uk. ..
Today I need to copy 8 records in a table. I have to use Access 200 because of the limitation of Enterprise Manager's inability to cope with field with more than 900 characters. Selected records, cut, paste. I got an erroor
message about not being able to have a null Key_ID (I copied the reords and tried to paste the Key_ID as part of the records - normally I hide the
Key_ID).
Now I can't access either the new records or the originals that I was trying to copy (because, it would seem, they have identical primary keys). I also
cannot export the table via DTS 'unspecified error' and 'integrity
violation'.
Or delete the offending records with a Query Anaylyser delete query.
Basically the entire SQL Server database has been destroyed with a couple of keystrokes.
Now, I've being developing database applications for over 20years and the
one thing, maybe the only thing I expect from a database server is to
protect the integrity of my data. SQL Server does not, it would seem. These records aren't just any random unimportant records either. They contain the 'create views' that my entire application require to function and each one
approaches the 8000 record limit and have take years to perfect and just
checking that the table is valid could take me days.

Jul 20 '05 #2
Yes I have a backup from yesterday, but it was a 14hour day and I don't ever
want to reapeat it.. But your sugguestion about removing the constraint
helped, I could delete them. Thanks.

"SWu" <sw@rgrzz.com.a u> wrote in message
news:uu******** ******@TK2MSFTN GP11.phx.gbl...
sorry if this sounds like a couple of dumb, obvious questions:
- do you have backups of this important code?
- are you able to drop the constraints on the table, remove the offending
records and reapply the constraints?

regards,
stephen
"pete" <pe**@madpete.f reeserve.co.uk> wrote in message
news:40******** **************@ news.zen.co.uk. ..
Today I need to copy 8 records in a table. I have to use Access 200 because
of the limitation of Enterprise Manager's inability to cope with field

with
more than 900 characters. Selected records, cut, paste. I got an erroor
message about not being able to have a null Key_ID (I copied the reords

and
tried to paste the Key_ID as part of the records - normally I hide the
Key_ID).
Now I can't access either the new records or the originals that I was

trying
to copy (because, it would seem, they have identical primary keys). I also cannot export the table via DTS 'unspecified error' and 'integrity
violation'.
Or delete the offending records with a Query Anaylyser delete query.
Basically the entire SQL Server database has been destroyed with a couple of
keystrokes.
Now, I've being developing database applications for over 20years and

the one thing, maybe the only thing I expect from a database server is to
protect the integrity of my data. SQL Server does not, it would seem.

These
records aren't just any random unimportant records either. They contain

the
'create views' that my entire application require to function and each one approaches the 8000 record limit and have take years to perfect and just
checking that the table is valid could take me days.


Jul 20 '05 #3
pete (pe**@madpete.f reeserve.co.uk) writes:
Today I need to copy 8 records in a table. I have to use Access 200
because of the limitation of Enterprise Manager's inability to cope with
field with more than 900 characters. Selected records, cut, paste. I
got an erroor message about not being able to have a null Key_ID (I
copied the reords and tried to paste the Key_ID as part of the records -
normally I hide the Key_ID).

Now I can't access either the new records or the originals that I was
trying to copy (because, it would seem, they have identical primary
keys). I also cannot export the table via DTS 'unspecified error' and
'integrity violation'.
Or delete the offending records with a Query Anaylyser delete query.
Basically the entire SQL Server database has been destroyed with a
couple of keystrokes.
Now, I've being developing database applications for over 20years and the
one thing, maybe the only thing I expect from a database server is to
protect the integrity of my data. SQL Server does not, it would seem.
SQL Server protects the integrity of your data, as far as you tell it
what to protect. It cannot make random guesses.

In any case, it is impossible to tell what happened, since you also
involved Access. "Cut and paste" is not a concept that exists in
SQL Server proper. If Access implemented the cut-and-paste with a
DELETE and INSERT, and the INSERT failed, and did not use a transaction,
then SQL Server have to plead innocent.
These records aren't just any random unimportant records either. They
contain the 'create views' that my entire application require to
function and each one approaches the 8000 record limit and have take
years to perfect and just checking that the table is valid could take me
days.


It seems a little funny to me that you store source code in a database
table. I would use a version-control system.

But assuming that you had created the views, you can use sp_helptext
to get SQL Server's version of the views. There are also scripting
facilities in Enterprise Manager.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
If you have a field name of more than 900 characters, then its your own
silly fault.
On the other hand it could be that you had a unique index on that was
created with(nocheck), which would explain why you could delete records, but
couldn't put them back in again.

"pete" <pe**@madpete.f reeserve.co.uk> wrote in message
news:40******** **************@ news.zen.co.uk. ..
Today I need to copy 8 records in a table. I have to use Access 200 because of the limitation of Enterprise Manager's inability to cope with field with more than 900 characters. Selected records, cut, paste. I got an erroor
message about not being able to have a null Key_ID (I copied the reords and tried to paste the Key_ID as part of the records - normally I hide the
Key_ID).
Now I can't access either the new records or the originals that I was trying to copy (because, it would seem, they have identical primary keys). I also
cannot export the table via DTS 'unspecified error' and 'integrity
violation'.
Or delete the offending records with a Query Anaylyser delete query.
Basically the entire SQL Server database has been destroyed with a couple of keystrokes.
Now, I've being developing database applications for over 20years and the
one thing, maybe the only thing I expect from a database server is to
protect the integrity of my data. SQL Server does not, it would seem. These records aren't just any random unimportant records either. They contain the 'create views' that my entire application require to function and each one
approaches the 8000 record limit and have take years to perfect and just
checking that the table is valid could take me days.

Jul 20 '05 #5
Beeeeeves (beeeeeeeeev@ve s) writes:
On the other hand it could be that you had a unique index on that was
created with(nocheck), which would explain why you could delete records,
but couldn't put them back in again.


There is NOCHECK for indexes. You can say NOCHECK with UNIQUE and PRIMARY
KEY constraints, but it does not have any effect. NOCHECK only applies
to CHECK and FOREIGN KEY constraints.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

109
5904
by: Andrew Thompson | last post by:
It seems most people get there JS off web sites, which is entirely logical. But it is also a great pity since most of that code is of such poor quality. I was looking through the JS FAQ for any question that identifies the warning signs to look out for, the things that most easily and clearly identify the author of code as something less than a master of the art. I did not find an FAQ that answered it, but I think the FAQ
1
4768
by: andree | last post by:
Hello, I have a form where a user may paste multiple records. The form has an AfterInsert procedure. The procedure copies the newly inserted record into a different table for audit purposes. When one record is pasted, all is working fine. If multiple records are pasted, the AfterInsert event gets fired before the Paste Confirmation Dialog Box, therefore no records are inserted into my audit table.
9
12333
by: Pete | last post by:
Does anyone have a simple html vbscript or other type of snippet they can share that appends a record to a access database via ADO or DAO? I would like to allow users that don't have Microsoft Access a way of adding records to a access database from a simple web page. I don't want to have to setup ODBC or anything like that I just want to put the web page on the network for anyone to access. Most of the users would use the local access...
1
4208
by: PalJoey | last post by:
I am having a problem copying and pasting records into the same table. When I copy a record that has a zero length string for a field Access seems to convert it to NULL when pasted. One of the fields does not allow NULLS so I cannot paste the record. I get the error "You tried to assign the null value to a variable that is not a variant data type". I know which field is causing the error b/c when I change it from a zero length string to...
2
2392
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for suffix ID where 1=Phd, 2= MD. To display all of these to the user, I created a form with an underlying query. The problem I am encountering is this, when we have an empty field, for example where ID="", the query returns nothing. How do i work around...
6
1847
by: dimitris.papastamos | last post by:
Hello everyone, I've been working on a simple editor myself and I seem to be having some problems with ncurses or so. I have been debugging this program quite a lot trying to detect where the bug is hiding. If you try to compile it gcc -o rosy rosy.cpp -lncurses and run it with an input file, the delete and backspace characters function great however when you start pressing keys like, right left or so to navigate through the file, it...
7
8694
kcdoell
by: kcdoell | last post by:
Good morning everyone: I created a form and set the default view as a continuous form. Basically the form is displaying records in which the user can add or edit new ones. The record source for this form is a query that I built that is based on a table. I have been working on this for several weeks and now I have been told that many times when a user wants to create a new record, much of the information that is displayed in a...
5
11143
by: phill86 | last post by:
Hi I have a main form that holds records for scheduled meetings, date time location etc... in that form i have a sub form that has a list of equipment resources that you can assign to the meeting in the main form. I have two buttons in the sub form one for selecting and copying all the records and another for pasting the records this enables me to copy and paste the equipment resources from one scheduled meeting to another. The buttons are...
6
9594
by: Phil Stanton | last post by:
My Ak2 databas has been running successfully for many years althogh it gets upgraded regularly. I now find on this, and other dbs, when I delete a record I no longer get the message "You are about to delete 1 record.....". What has gone wrong / got changed? Thanks Phil
0
8834
marktang
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...
0
8727
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,...
0
9056
tracyyun
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...
0
8002
agi2029
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...
1
6664
isladogs
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...
0
5973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4483
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...
2
2569
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2127
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.