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 r
epro, it is hard for us
to comment any further. Most of us prefer to execute TSQL statements from Qu
ery Analyzer for these
types of changes as it gives us better control, and we know exactly what com
mands 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.gb
l...
> 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 th
e
> 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 t
wo
> 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 datastructu
re
> 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!!
>|||> 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 th
e
> change script before saving changes. As Tibor points out, many of us pref
er
> 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...
> the
> think).
> two
> Thereafter,
> datastructure
> a
>|||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...
IS[vbcol=seagreen]
> 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...
work[vbcol=seagreen]
the[vbcol=seagreen]
extract.[vbcol=seagreen]
think).[vbcol=seagreen]
two[vbcol=seagreen]
Thereafter,[vbcol=seagreen]
datastructure[vbcol=seagreen]
IS[vbcol=seagreen]
within a[vbcol=seagreen]
data[vbcol=seagreen]
>|||> 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 ca
tch 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.gb
l...
> 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 i
n
> message news:uwVHGHcvEHA.1564@.TK2MSFTNGP09.phx.gbl...
> IS
> repro, it is hard for us
> Query Analyzer for these
> commands are executed...
> news:eDbrR9bvEHA.3896@.TK2MSFTNGP09.phx.gbl...
> work
> the
> extract.
> think).
> two
> Thereafter,
> datastructure
> IS
> within a
> data
>|||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...
> 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...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJ90CjcvEHA.1452@.TK2MSFTNGP11.phx.gbl...
> 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/