Re: Nevermind: Problem Between the Chair and the Keyboard

From: Ron Talmage (rtalmage_at_prospice.com)
Date: 11/01/04 

Date: Sun, 31 Oct 2004 16:20:28 -0800

John,

There is a situation where you can end up with deleted data from Enterprise
Manager. If you increase a constraint level in a table using Enteprise
Manager, and then apply the script that the table designer creates, you can
lose data.

For example, if you change a column constraint from NULL to NOT NULL, and
the data has some NULLs in it, the resulting script, when you run it, will
    begin a transaction
    rename the original table
    create the new table with NOT NULL
    insert the data from the renamed table into the new table
        the insert will fail, because of NULLs in the data
        the error is not trapped
    the renamed table is dropped
    the transaction is committed.
... and voila! a new table with no data in it.

So I'm with Kalen, don't use Entperprise Manager to edit table structures.

Ron 

-- 
Ron Talmage
SQL Server MVP
"John Bishop" <ugradfrnd@aol.com> wrote in message
news:OBZZrjcvEHA.1520@TK2MSFTNGP11.phx.gbl...
> Thank you, Geoff!  That did the trick!  The data WAS there all along.
>
> It is more than a little disorienting though, when one is tired and
> ill-acquainted with SQL and a data table seems to LOSE all of its records!
>
> But these test runs are all about learning the ideosyncracies of a product
> before trying to use it in production!
>
> The problem appears to have been between the chair and the keyboard all
> along!!
>
> "Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
> news:e9Z36acvEHA.3872@TK2MSFTNGP11.phx.gbl...
> > Run DBCC UPDATEUSAGE to correct the row count display in Enterprise
> Mangler.
> >
> > -- 
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "John Bishop" <ugradfrnd@aol.com> wrote in message
> > news:%231Z4HYcvEHA.2016@TK2MSFTNGP15.phx.gbl...
> > > Your explanation makes SENSE and yes, it appears that I was checking
> > > immediately after the change.  (Bear in mind that I am still feeling
my
> > way
> > > along.)
> > >
> > > The indication that the records were DELETED is that Server Enterprise
> > > Manager reports that the table has ZERO Rows.  I had made the change
in
> > Data
> > > Type in Server Enterprise Manager and then was making notes and
> > documenting
> > > what I had done.  I went back to note the record count (which I had
> > > previously found was conveniently reported in the Table Properties in
> > Server
> > > Enterprise Manager and it gave me the sensation that ALL of the data
had
> > > been deleted.
> > >
> > > Now, I am thinking that it is doing precisely as you describe.  I was
> able
> > > to use the Import and Export Data tool to COPY all of the data from
that
> > > table to another table in a different database, which reports that it
> has
> > > 387,825 records (CORRECT).  The original table STILL says in the
> > Properties
> > > that it has ZERO records, but that table has several Indices that are
> > > probably being rebuilt.
> > >
> > > But, the larger database from last night STILL SAYS that it has ZERO
> > records
> > > nine hours after the records appeared to all be deleted.  But it has
> nine
> > > million records and more than a few indices.  And I am running this
SQL
> > > Evaulation on an older 866 MHz single processor system with only 512
Mbs
> > of
> > > memory.
> > >
> > > Is there some way to monitor the things that SQL is doing in the
> > BACKGROUND
> > > to these tables?
> > >
> > > "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message
> > > news:ORaVrEcvEHA.2584@TK2MSFTNGP10.phx.gbl...
> > > > Of course you backed up your database, prior to making major changes
> to
> > > > tables, correct?
> > > >
> > > > Whew..thank goodness.
> > > >
> > > > And no, generally records are not deleted. However, behind the
scenes,
> > > it's
> > > > probably renaming the table, creating a new structure, then
inserting
> > the
> > > > records, then dropping and renaming the temp table.
> > > >
> > > > Did you check if the data was there immediately after changing the
> > column
> > > > datatype?
> > > >
> > > > Jeff
> > > > "John Bishop" <ugradfrnd@aol.com> wrote in message
> > > > news:eDbrR9bvEHA.3896@TK2MSFTNGP09.phx.gbl...
> > > > > Last night, I posted the message "HELP!:  A Disasterous Delete"
> > (Friday,
> > > > > October 29, 2004 1:20 AM).  This morning, as I pressed ahead with
my
> > > work
> > > > > with my smaller data extract, I have had a recurrence of the
problem
> > of
> > > > the
> > > > > mysterious deletion of ALL RECORDS, but now with the smaller data
> > > extract.
> > > > >
> > > > > But I have a better sense now of what may have CAUSED the problem
(I
> > > > think).
> > > > > I had just used SQL Server Enterprise Manager to Change the Data
> Type
> > of
> > > > two
> > > > > columns from char to nvarchar.  I was then re-running a query.
> > > > Thereafter,
> > > > > in noticed that ALL ROWS of the table seemed to be missing.
> > > > >
> > > > > Perhaps I am SPOILED by the way MS Access handles changes to a
> > > > datastructure
> > > > > with data records in place.
> > > > >
> > > > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE
> COLUMN
> > > IS
> > > > > ALTERED???  This seems like a rather radical treatment to the data
> > > within
> > > > a
> > > > > table if this is the case.
> > > > >
> > > > > Now I seem to have lost NOT ONLY the original larger 9 million
> record
> > > data
> > > > > table, but also the smaller 350,000 record data extract.
> > > > >
> > > > > Maybe I should just throw in the towel!
> > > > >
> > > > > Any suggestions are appreciated!!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>