Nevermind: Problem Between the Chair and the Keyboard

From: John Bishop (ugradfrnd_at_aol.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 11:07:51 -0400

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!!
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages
Loading