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

[pete]

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.

[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.freeserve.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.

[pete]

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" <[e-mail]> wrote in message
news:uu**************@TK2MSFTNGP11.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.freeserve.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.

[Erland Sommarskog]

pete (pe**@madpete.freeserve.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Beeeeeves]

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.freeserve.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.

[Erland Sommarskog]

Beeeeeves (beeeeeeeeev@ves) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp