From BAD To WORSE: A Disasterous Delete

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!!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!!
>|||> DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS
> ALTERED?
No. You have either hit a bug, or something went very wrong. But without a repro, it is hard for us
to comment any further. Most of us prefer to execute TSQL statements from Query Analyzer for these
types of changes as it gives us better control, and we know exactly what commands are executed...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"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!!
>|||Enterprise Mangler often does a table shuffle under the covers when making
changes to tables. You can see exactly what it intends to do by saving the
change script before saving changes. As Tibor points out, many of us prefer
to do things via T-SQL because we know exactly what is happening. EM is a
good tool, but sometimes the way it wants to do something is not the way I
want it done.
--
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: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!!
>|||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!!
> >
> >
>|||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!!
> > >
> > >
> >
> >
>|||> Enterprise Mangler
First time I've seen that one. LOL...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
> Enterprise Mangler often does a table shuffle under the covers when making
> changes to tables. You can see exactly what it intends to do by saving the
> change script before saving changes. As Tibor points out, many of us prefer
> to do things via T-SQL because we know exactly what is happening. EM is a
> good tool, but sometimes the way it wants to do something is not the way I
> want it done.
> --
> 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: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!!
>>
>|||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!!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you, too, Tibor!
DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
trick. ENterprise Manager was simply misreporting the record count!
As I use the Wizards and tools to do certain tasks, I also tend to learn
more about the underlying SQL, which I will no doubt apply directly from
TSQL when I am more knowledgable and experienced.
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
IS
> > ALTERED?
> No. You have either hit a bug, or something went very wrong. But without a
repro, it is hard for us
> to comment any further. Most of us prefer to execute TSQL statements from
Query Analyzer for these
> types of changes as it gives us better control, and we know exactly what
commands are executed...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "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!!
> >
> >
>|||> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
Yes, EM picks up the row count from sysindexes which isn't reliable. Good catch by Geoff.
> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
Good plan. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bishop" <ugradfrnd@.aol.com> wrote in message news:eQGc0mcvEHA.3272@.TK2MSFTNGP12.phx.gbl...
> Thank you, too, Tibor!
> DBCC UPDATEUSAGE ('database') WITH COUNT_ROWS (suggested by Geof) did the
> trick. ENterprise Manager was simply misreporting the record count!
> As I use the Wizards and tools to do certain tasks, I also tend to learn
> more about the underlying SQL, which I will no doubt apply directly from
> TSQL when I am more knowledgable and experienced.
> Thanks again!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
>> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
> IS
>> > ALTERED?
>> No. You have either hit a bug, or something went very wrong. But without a
> repro, it is hard for us
>> to comment any further. Most of us prefer to execute TSQL statements from
> Query Analyzer for these
>> types of changes as it gives us better control, and we know exactly what
> commands are executed...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "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!!
>> >
>> >
>>
>|||I see this when people talk about using EM for data manipulation, because
that's what it does to your data.
John, I suggest you start learning basic SQL soon. EM is a good tool for
working with your objects, but NOT for working with the data itself.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
>> Enterprise Mangler
> First time I've seen that one. LOL...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uVR4iYcvEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Enterprise Mangler often does a table shuffle under the covers when
>> making
>> changes to tables. You can see exactly what it intends to do by saving
>> the
>> change script before saving changes. As Tibor points out, many of us
>> prefer
>> to do things via T-SQL because we know exactly what is happening. EM is
>> a
>> good tool, but sometimes the way it wants to do something is not the way
>> I
>> want it done.
>> --
>> 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: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!!
>>
>>
>|||"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.
SQL Server is really good about not deleting stuff it shouldn't. :-)
> 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!
It's worse when you realize you just copied the EMPTY DB on top of the
production DB. THEN realized you had no backups. :-)
> But these test runs are all about learning the ideosyncracies of a product
> before trying to use it in production!
Good plan.
> The problem appears to have been between the chair and the keyboard all
> along!!
>
PEBKAC :-)
> "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!!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
> > Enterprise Mangler
> First time I've seen that one. LOL...
>
Really? Wow. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||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!!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>