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]

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

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" <pete@madpete.f reeserve.co.uk> wrote in message
news:40d2c64e$0 $4584$db0fefd9@ news.zen.co.uk. ..[color=blue]
> Today I need to copy 8 records in a table. I have to use Access 200[/color]
because[color=blue]
> of the limitation of Enterprise Manager's inability to cope with field[/color]
with[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
trying[color=blue]
> 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[/color]
of[color=blue]
> 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.[/color]
These[color=blue]
> records aren't just any random unimportant records either. They contain[/color]
the[color=blue]
> '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.
>
>[/color]

[pete]

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

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:uu3pgQSVEH A.1888@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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" <pete@madpete.f reeserve.co.uk> wrote in message
> news:40d2c64e$0 $4584$db0fefd9@ news.zen.co.uk. ..[color=green]
> > Today I need to copy 8 records in a table. I have to use Access 200[/color]
> because[color=green]
> > of the limitation of Enterprise Manager's inability to cope with field[/color]
> with[color=green]
> > 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[/color]
> and[color=green]
> > 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[/color]
> trying[color=green]
> > to copy (because, it would seem, they have identical primary keys). I[/color][/color]
also[color=blue][color=green]
> > 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[/color][/color]
couple[color=blue]
> of[color=green]
> > keystrokes.
> > Now, I've being developing database applications for over 20years and[/color][/color]
the[color=blue][color=green]
> > 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.[/color]
> These[color=green]
> > records aren't just any random unimportant records either. They contain[/color]
> the[color=green]
> > 'create views' that my entire application require to function and each[/color][/color]
one[color=blue][color=green]
> > approaches the 8000 record limit and have take years to perfect and just
> > checking that the table is valid could take me days.
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

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

pete (pete@madpete.f reeserve.co.uk) writes:[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Beeeeeves]

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

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" <pete@madpete.f reeserve.co.uk> wrote in message
news:40d2c64e$0 $4584$db0fefd9@ news.zen.co.uk. ..[color=blue]
> Today I need to copy 8 records in a table. I have to use Access 200[/color]
because[color=blue]
> of the limitation of Enterprise Manager's inability to cope with field[/color]
with[color=blue]
> 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[/color]
and[color=blue]
> 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[/color]
trying[color=blue]
> 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[/color]
of[color=blue]
> 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.[/color]
These[color=blue]
> records aren't just any random unimportant records either. They contain[/color]
the[color=blue]
> '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.
>
>[/color]

[Erland Sommarskog]

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

Beeeeeves (beeeeeeeeev@ve s) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp