when I try to add a job I am unable to use the wizard
the screen that holds the settings is corrupted
I can no longer define how many backups have to stay in the folder
(disk backup)
I can set the number but the listbox after that stays empty so I can
not set "hours" or "days"
the job itself doesn't work neither
the existing jobs keep happening but are no longer visible in the
enterprise manager
can I solve this some way?
maybe with queries?
I did try to use the "new" sql 2005 console for my 2k servers
could this be related?
thnxSQL2000 Maintenance plans are treated as legacy in SQL2005 and limited in
what they can do etc. The 2005 tool is not meant to edit 2000 plans. You
may have to redo them using 2000 EM since there is no telling what might
have changed.
--
Andrew J. Kelly SQL MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.3b897d6207852634.36062@.yahoo.com...
> when I try to add a job I am unable to use the wizard
> the screen that holds the settings is corrupted
> I can no longer define how many backups have to stay in the folder (disk
> backup)
> I can set the number but the listbox after that stays empty so I can not
> set "hours" or "days"
> the job itself doesn't work neither
> the existing jobs keep happening but are no longer visible in the
> enterprise manager
> can I solve this some way?
> maybe with queries?
> I did try to use the "new" sql 2005 console for my 2k servers
> could this be related?
> thnx
>|||> SQL2000 Maintenance plans are treated as legacy in SQL2005 and limited in
> what they can do etc. The 2005 tool is not meant to edit 2000 plans. You
> may have to redo them using 2000 EM since there is no telling what might have
> changed.
> --
> Andrew J. Kelly SQL MVP
> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
> news:mn.3b897d6207852634.36062@.yahoo.com...
>> when I try to add a job I am unable to use the wizard
>> the screen that holds the settings is corrupted
>> I can no longer define how many backups have to stay in the folder (disk
>> backup)
>> I can set the number but the listbox after that stays empty so I can not
>> set "hours" or "days"
>> the job itself doesn't work neither
>> the existing jobs keep happening but are no longer visible in the
>> enterprise manager
>> can I solve this some way?
>> maybe with queries?
>> I did try to use the "new" sql 2005 console for my 2k servers
>> could this be related?
>> thnx
>>
I didn't try to change anything using the 2k5 VS tool
but I can't change it using the 2k enterprise manager anymore
since the wizard got corrupted somehow
I am not sure if it is related
my question is how to repair it without interrupting the server
functionality (too much)
grtz|||Since I have no way to know what is corrupted all I can say is to delete any
that you suspect are corrupt and rebuild them.
--
Andrew J. Kelly SQL MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.42317d62800a030b.36062@.yahoo.com...
>> SQL2000 Maintenance plans are treated as legacy in SQL2005 and limited in
>> what they can do etc. The 2005 tool is not meant to edit 2000 plans.
>> You may have to redo them using 2000 EM since there is no telling what
>> might have changed.
>> --
>> Andrew J. Kelly SQL MVP
>> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
>> news:mn.3b897d6207852634.36062@.yahoo.com...
>> when I try to add a job I am unable to use the wizard
>> the screen that holds the settings is corrupted
>> I can no longer define how many backups have to stay in the folder (disk
>> backup)
>> I can set the number but the listbox after that stays empty so I can not
>> set "hours" or "days"
>> the job itself doesn't work neither
>> the existing jobs keep happening but are no longer visible in the
>> enterprise manager
>> can I solve this some way?
>> maybe with queries?
>> I did try to use the "new" sql 2005 console for my 2k servers
>> could this be related?
>> thnx
>>
> I didn't try to change anything using the 2k5 VS tool
> but I can't change it using the 2k enterprise manager anymore
> since the wizard got corrupted somehow
> I am not sure if it is related
> my question is how to repair it without interrupting the server
> functionality (too much)
> grtz
>|||> Since I have no way to know what is corrupted all I can say is to delete any
> that you suspect are corrupt and rebuild them.
> --
> Andrew J. Kelly SQL MVP
> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
> news:mn.42317d62800a030b.36062@.yahoo.com...
>>
>> I didn't try to change anything using the 2k5 VS tool
>> but I can't change it using the 2k enterprise manager anymore
>> since the wizard got corrupted somehow
>> I am not sure if it is related
>> my question is how to repair it without interrupting the server
>> functionality (too much)
>> grtz
>>
I'd like to but I can't
the interface doesn't show me any jobs
but they do still work
I just cannot add any new ones because the wizard is broken somehow
grtz|||You should still be able to create a new maintenance plan and add what ever
you want there. They don't have to all be in the same plan.
Andrew J. Kelly SQL MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.521c7d6216230948.36062@.yahoo.com...
>> Since I have no way to know what is corrupted all I can say is to delete
>> any that you suspect are corrupt and rebuild them.
>> --
>> Andrew J. Kelly SQL MVP
>> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
>> news:mn.42317d62800a030b.36062@.yahoo.com...
>>
>> I didn't try to change anything using the 2k5 VS tool
>> but I can't change it using the 2k enterprise manager anymore
>> since the wizard got corrupted somehow
>> I am not sure if it is related
>> my question is how to repair it without interrupting the server
>> functionality (too much)
>> grtz
>>
> I'd like to but I can't
> the interface doesn't show me any jobs
> but they do still work
> I just cannot add any new ones because the wizard is broken somehow
> grtz
>|||> You should still be able to create a new maintenance plan and add what ever
> you want there. They don't have to all be in the same plan.
> --
> Andrew J. Kelly SQL MVP
> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
> news:mn.521c7d6216230948.36062@.yahoo.com...
>>
>> I'd like to but I can't
>> the interface doesn't show me any jobs
>> but they do still work
>> I just cannot add any new ones because the wizard is broken somehow
>> grtz
>>
no I cannot,
for some reason the wizard doesn't allow me to set a value for how many
files I'd like to keep in the backup folder
I can set a number but not the type of value after that
I already tried to add a new job anyway but it keeps failing
since I have no idea how to fix the wizard I thought I'd give the
experts a try to tell me why,how and where the wizard might fail
and how to repair it's behaviour
perhaps thru sql?
grtz|||Without seeing what you have I really have no idea how to fix it. I rarely
advocate using the maintenance plan wizard at all for so many reasons, this
being just one. Anything you can do with he wizard can be done fairly
easily with simple tsql commands and a normal scheduled job. But you can use
the same utility that the wizard uses to execute these tasks and you don't
need to use the gui to do it. Create a new scheduled job and call the
sqlmaint utility with the appropriate parameters to do what you want done.
See "sqlmaint utility" in BooksOnLine for the details.
--
Andrew J. Kelly SQL MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.53927d6233044d14.36062@.yahoo.com...
>> You should still be able to create a new maintenance plan and add what
>> ever you want there. They don't have to all be in the same plan.
>> --
>> Andrew J. Kelly SQL MVP
>> "chriske911" <chriske911nospam@.yahoo.com> wrote in message
>> news:mn.521c7d6216230948.36062@.yahoo.com...
>>
>> I'd like to but I can't
>> the interface doesn't show me any jobs
>> but they do still work
>> I just cannot add any new ones because the wizard is broken somehow
>> grtz
>>
> no I cannot,
> for some reason the wizard doesn't allow me to set a value for how many
> files I'd like to keep in the backup folder
> I can set a number but not the type of value after that
> I already tried to add a new job anyway but it keeps failing
> since I have no idea how to fix the wizard I thought I'd give the experts
> a try to tell me why,how and where the wizard might fail
> and how to repair it's behaviour
> perhaps thru sql?
> grtz
>
Showing posts with label corrupted. Show all posts
Showing posts with label corrupted. Show all posts
Friday, February 17, 2012
corrupted(?) sql server 2000 table
Hi all,
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
--
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
--
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
corrupted(?) sql server 2000 table
Hi all,
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!
ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!
|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!
ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!
|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
Tuesday, February 14, 2012
corrupted(?) sql server 2000 table
Hi all,
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?
> Any insight would be great. Thanks!|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegroups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
Corrupted view
Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
Corrupted view
Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
Ferdinand
Ferdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
Ferdinand
Ferdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
Corrupted view
Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
corrupted values in database
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(
The database and its application are bought as is, I cannot modify fieldtypes or anything else.
Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??
P.S.
In extreme cases string contain both ',' AND '.' , like:
5.608,42Use
convert (float, replace(column_name,',',''))
or convert (int,replace(replace(column_name,',',''),'.','') if you are sure the value is an integer|||Note quite what I need,
The string can contain either a . or a ,
With the conversion you give all figures with a ',' come out 100 fold higher than they should be, while those with a '.' come out right.
Some sort of validation seems to be needed to get all them right.
Right now I am focussing on prices which means the highest value is just 900.00 (or 900.00) so I have no problem in this column with both a ',' and a '.'|||The following seems to work:
SELECT
CASE
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = ',' THEN
convert(float,replace(PRIJS,',',''))/100
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = '.' THEN
convert(dec(9,2),PRIJS)
ELSE 0 END
not getting very good feelings though on having to do these king of conversions :-(|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.
Another approach would be to identify your different formats, and make a union query, handling each of your formats in a separate branch.|||Can't do that.
Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,
but doctor maybe you can help me with this one:
I have all the differnent flavors in the DB:
1.203,56
1,203.56
456,67
456.67
You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'
Nice challenge ain't it?
Next private message next week, gotta cycle to the north this afternoon
:-)|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.
I dont uderstand the format "#.###,##" . What does it exactly mean ?|||Originally posted by blom0344
Can't do that.
Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,
I understand, that you don't have control of the import process, but who is preventing you from cleaning your data afterwards?
Originally posted by blom0344
I have all the differnent flavors in the DB:
1.203,56
1,203.56
456,67
456.67
You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'
Nice challenge ain't it?
As I said, determine first your possible flavors. What is imported when your price is 456.- or 456.50? Is is 456[,|.]00 or just 456? So, actually I'm asking whether your decimal point is always on the 3rd last position? Your algorithm depends on that. Another point is whether you can expect a maximum number of digits, or not. So, can you also have, for example 1,234,567.89?
Assuming, your possible flavors are those 4 you gave me, a query like I propose would look like (your price field is called p)
SELECT p FROM T WHERE len(p)=6 AND SubString(p, 4,1)="."
UNION
SELECT replace(p,',','.') FROM T WHERE len(p)=6 AND SubString(p, 4,1)=","
UNION
SELECT replace(p,',','') FROM T WHERE len(p)=8 AND SubString(p, 6,1)="."
UNION
SELECT replace(replace(p,'.',''), ',','.') FROM T WHERE len(p)=8 AND SubString(p, 6,1)=","
Calling this union query U you can do your accumulation like
SELECT sum(cast(p as decimal(10,2))) from (<U>)|||-- replace(replace(p,'.',''), ',','.') --
Yep,
I am working with the replace on replace in my solution as well.
Does not seem to work like it should.
I want to use the CASE instead of UNION solution , cause I am going to assign it to a BO object
We'll continue next week...|||If you know you have only two decimal places, how about something along the lines of eliminating all commas/periods, then dividing by 100?
select convert(numeric (10, 2), replace(replace(value, ',', ''), '.', '')))/100.00
A bit pressed for time here, so I have not tested that code snippet. Hope it helps.|||Elegant solution, MCrowley. :cool:
...but make sure the values don't just only have two decimal places, but that the ALWAYS have two decimal places.
blindman|||Originally posted by blom0344
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(
The database and its application are bought as is, I cannot modify fieldtypes or anything else.
Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??
P.S.
In extreme cases string contain both ',' AND '.' , like:
5.608,42
Dumb question ... but, any chance you are dealing with software that handles multi-currency? Do you have the CCSID translation turned on from the AS400 to the SQL server?|||Most certainly not a dumb question, but in this case we are talking about E-commerce orders from two euro countries, so every order amount is always just in one currency.
McCrowleys solution does indeed work for me, cause every order is calculated down to the euro-cent
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(
The database and its application are bought as is, I cannot modify fieldtypes or anything else.
Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??
P.S.
In extreme cases string contain both ',' AND '.' , like:
5.608,42Use
convert (float, replace(column_name,',',''))
or convert (int,replace(replace(column_name,',',''),'.','') if you are sure the value is an integer|||Note quite what I need,
The string can contain either a . or a ,
With the conversion you give all figures with a ',' come out 100 fold higher than they should be, while those with a '.' come out right.
Some sort of validation seems to be needed to get all them right.
Right now I am focussing on prices which means the highest value is just 900.00 (or 900.00) so I have no problem in this column with both a ',' and a '.'|||The following seems to work:
SELECT
CASE
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = ',' THEN
convert(float,replace(PRIJS,',',''))/100
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = '.' THEN
convert(dec(9,2),PRIJS)
ELSE 0 END
not getting very good feelings though on having to do these king of conversions :-(|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.
Another approach would be to identify your different formats, and make a union query, handling each of your formats in a separate branch.|||Can't do that.
Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,
but doctor maybe you can help me with this one:
I have all the differnent flavors in the DB:
1.203,56
1,203.56
456,67
456.67
You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'
Nice challenge ain't it?
Next private message next week, gotta cycle to the north this afternoon
:-)|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.
I dont uderstand the format "#.###,##" . What does it exactly mean ?|||Originally posted by blom0344
Can't do that.
Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,
I understand, that you don't have control of the import process, but who is preventing you from cleaning your data afterwards?
Originally posted by blom0344
I have all the differnent flavors in the DB:
1.203,56
1,203.56
456,67
456.67
You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'
Nice challenge ain't it?
As I said, determine first your possible flavors. What is imported when your price is 456.- or 456.50? Is is 456[,|.]00 or just 456? So, actually I'm asking whether your decimal point is always on the 3rd last position? Your algorithm depends on that. Another point is whether you can expect a maximum number of digits, or not. So, can you also have, for example 1,234,567.89?
Assuming, your possible flavors are those 4 you gave me, a query like I propose would look like (your price field is called p)
SELECT p FROM T WHERE len(p)=6 AND SubString(p, 4,1)="."
UNION
SELECT replace(p,',','.') FROM T WHERE len(p)=6 AND SubString(p, 4,1)=","
UNION
SELECT replace(p,',','') FROM T WHERE len(p)=8 AND SubString(p, 6,1)="."
UNION
SELECT replace(replace(p,'.',''), ',','.') FROM T WHERE len(p)=8 AND SubString(p, 6,1)=","
Calling this union query U you can do your accumulation like
SELECT sum(cast(p as decimal(10,2))) from (<U>)|||-- replace(replace(p,'.',''), ',','.') --
Yep,
I am working with the replace on replace in my solution as well.
Does not seem to work like it should.
I want to use the CASE instead of UNION solution , cause I am going to assign it to a BO object
We'll continue next week...|||If you know you have only two decimal places, how about something along the lines of eliminating all commas/periods, then dividing by 100?
select convert(numeric (10, 2), replace(replace(value, ',', ''), '.', '')))/100.00
A bit pressed for time here, so I have not tested that code snippet. Hope it helps.|||Elegant solution, MCrowley. :cool:
...but make sure the values don't just only have two decimal places, but that the ALWAYS have two decimal places.
blindman|||Originally posted by blom0344
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(
The database and its application are bought as is, I cannot modify fieldtypes or anything else.
Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??
P.S.
In extreme cases string contain both ',' AND '.' , like:
5.608,42
Dumb question ... but, any chance you are dealing with software that handles multi-currency? Do you have the CCSID translation turned on from the AS400 to the SQL server?|||Most certainly not a dumb question, but in this case we are talking about E-commerce orders from two euro countries, so every order amount is always just in one currency.
McCrowleys solution does indeed work for me, cause every order is calculated down to the euro-cent
Corrupted TDS packets
I have the same problem as this posting, but It has never
been answered. Could somebody help?
Here is the old posting:
I have repeatedly error message in the application log:
17805, severity:20, Stqte:3 invalid buffer received from
client"
The KB 295113 - fix offer to load the latest SQL SP, but I
already have SQL SP3. Do I need SP 3A?
What else I need to do to clean the errors,
Thanks a lot.
.do you know if the error occurs for all client computers
or for a particular one? try reinstalling sql client. try
to find out if the problem is with the server or client.
if you think there is something wrong with the sql server
then try uninstalling the sql server and then reinstall
sql server and sp3a. if you still get errors then call MS
support.
>--Original Message--
>I have the same problem as this posting, but It has never
>been answered. Could somebody help?
>Here is the old posting:
>I have repeatedly error message in the application log:
>17805, severity:20, Stqte:3 invalid buffer received from
>client"
>The KB 295113 - fix offer to load the latest SQL SP, but
I
>already have SQL SP3. Do I need SP 3A?
>What else I need to do to clean the errors,
>Thanks a lot.
>..
>
>.
>
been answered. Could somebody help?
Here is the old posting:
I have repeatedly error message in the application log:
17805, severity:20, Stqte:3 invalid buffer received from
client"
The KB 295113 - fix offer to load the latest SQL SP, but I
already have SQL SP3. Do I need SP 3A?
What else I need to do to clean the errors,
Thanks a lot.
.do you know if the error occurs for all client computers
or for a particular one? try reinstalling sql client. try
to find out if the problem is with the server or client.
if you think there is something wrong with the sql server
then try uninstalling the sql server and then reinstall
sql server and sp3a. if you still get errors then call MS
support.
>--Original Message--
>I have the same problem as this posting, but It has never
>been answered. Could somebody help?
>Here is the old posting:
>I have repeatedly error message in the application log:
>17805, severity:20, Stqte:3 invalid buffer received from
>client"
>The KB 295113 - fix offer to load the latest SQL SP, but
I
>already have SQL SP3. Do I need SP 3A?
>What else I need to do to clean the errors,
>Thanks a lot.
>..
>
>.
>
corrupted tables
Does anyone make a program that will detect and attempt to
correct corrupted tables in a sql database?
DBCC CHECKDB ?
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||Hi,
Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
status of your database.
If you found any errors on any table, you could use
DBCC CHECKTABLE with repair options to clear the errors. Please verify books
online for
command usage.
Thanks
Hari
MCDBA
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||You should always try to restore from your backups rather than run repair -
as repair does not preserve the constraints and business logic inherent in
your data.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
> status of your database.
> If you found any errors on any table, you could use
> DBCC CHECKTABLE with repair options to clear the errors. Please verify
books
> online for
> command usage.
> Thanks
> Hari
> MCDBA
>
> "dave" <davidn@.carlsonpaving.com> wrote in message
> news:986601c478b1$ca55c660$a301280a@.phx.gbl...
>
|||Hi Paul,
MS documentation states that DBCC with REPAIR_REBUILD option will never ends
in a data loss. One instance I had solved an issue
in my production server with out any data loss using the REPAIR_REBUILD
option. That is the reason I recommended Repair_option.
I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
data loss.
So will you please recommend the usage of Repair option?
Correct me if my logic is wrong.
Thanks
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> You should always try to restore from your backups rather than run
repair -
> as repair does not preserve the constraints and business logic inherent in
> your data.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> books
>
|||You are correct that REPAIR_REBUILD will not result in data loss - however,
with all due respect, you didn't say that in your reply - you just said 'If
you found any errors on any table, you could use DBCC CHECKTABLE with repair
options to clear the errors.'
Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration of
the effects. This is spelled out load and clear in the BOL for SQL Server
2005.
Some other things to consider:
1) using a repair option requires the database to be in single_user mode,
i.e. the whole thing is offline, not just the index being rebuilt
2) when any problems are found, root cause analysis should be done rather
than blindly fixing them using repair, even for relatively innocuous
problems involving non-clustered indexes.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Paul,
> MS documentation states that DBCC with REPAIR_REBUILD option will never
ends[vbcol=seagreen]
> in a data loss. One instance I had solved an issue
> in my production server with out any data loss using the REPAIR_REBUILD
> option. That is the reason I recommended Repair_option.
> I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
> data loss.
> So will you please recommend the usage of Repair option?
> Correct me if my logic is wrong.
> Thanks
> Hari
> MCDBA
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> repair -
in
> rights.
>
|||Than
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||THan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||Thanks Paul Randal for the detailed information.
Regards
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
correct corrupted tables in a sql database?
DBCC CHECKDB ?
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||Hi,
Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
status of your database.
If you found any errors on any table, you could use
DBCC CHECKTABLE with repair options to clear the errors. Please verify books
online for
command usage.
Thanks
Hari
MCDBA
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||You should always try to restore from your backups rather than run repair -
as repair does not preserve the constraints and business logic inherent in
your data.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
> status of your database.
> If you found any errors on any table, you could use
> DBCC CHECKTABLE with repair options to clear the errors. Please verify
books
> online for
> command usage.
> Thanks
> Hari
> MCDBA
>
> "dave" <davidn@.carlsonpaving.com> wrote in message
> news:986601c478b1$ca55c660$a301280a@.phx.gbl...
>
|||Hi Paul,
MS documentation states that DBCC with REPAIR_REBUILD option will never ends
in a data loss. One instance I had solved an issue
in my production server with out any data loss using the REPAIR_REBUILD
option. That is the reason I recommended Repair_option.
I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
data loss.
So will you please recommend the usage of Repair option?
Correct me if my logic is wrong.
Thanks
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> You should always try to restore from your backups rather than run
repair -
> as repair does not preserve the constraints and business logic inherent in
> your data.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> books
>
|||You are correct that REPAIR_REBUILD will not result in data loss - however,
with all due respect, you didn't say that in your reply - you just said 'If
you found any errors on any table, you could use DBCC CHECKTABLE with repair
options to clear the errors.'
Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration of
the effects. This is spelled out load and clear in the BOL for SQL Server
2005.
Some other things to consider:
1) using a repair option requires the database to be in single_user mode,
i.e. the whole thing is offline, not just the index being rebuilt
2) when any problems are found, root cause analysis should be done rather
than blindly fixing them using repair, even for relatively innocuous
problems involving non-clustered indexes.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Paul,
> MS documentation states that DBCC with REPAIR_REBUILD option will never
ends[vbcol=seagreen]
> in a data loss. One instance I had solved an issue
> in my production server with out any data loss using the REPAIR_REBUILD
> option. That is the reason I recommended Repair_option.
> I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
> data loss.
> So will you please recommend the usage of Repair option?
> Correct me if my logic is wrong.
> Thanks
> Hari
> MCDBA
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> repair -
in
> rights.
>
|||Than
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||THan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||Thanks Paul Randal for the detailed information.
Regards
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
Corrupted table?
We have a SQL Server 2000 database with a table we cannot delete. We first
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.DROP TABLE <table name>.
But what is the error you get?
--
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Have you tried running DBCC CHECKTABLE?
--
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...'
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > We have a SQL Server 2000 database with a table we cannot delete. We
first
> > try from Enterprise Manager, and then tried through VB code, and a
stored
> > procedure, but nothing is able to delete the table. Is there anything
else
> > to try?
> >
> > Thank you.
> >
> >
>|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > We have a SQL Server 2000 database with a table we cannot delete. We
first
> > try from Enterprise Manager, and then tried through VB code, and a
stored
> > procedure, but nothing is able to delete the table. Is there anything
else
> > to try?
> >
> > Thank you.
> >
> >
>|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try
with
> > REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> >
> > Take a backup of Database prior to execute DBCC with Repair options.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Dean J Garrett" <info@.amuletc.com> wrote in message
> > news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > > We have a SQL Server 2000 database with a table we cannot delete. We
> first
> > > try from Enterprise Manager, and then tried through VB code, and a
> stored
> > > procedure, but nothing is able to delete the table. Is there anything
> else
> > > to try?
> > >
> > > Thank you.
> > >
> > >
> >
> >
>|||Blocking? Did you check using sp_who, sp_who2 etc?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyzer.
> Each time when the command executes to drop the table, the program freezes,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> That is not what MS recommends. You should not repair without first
>> determining root cause of the errors and then you should use a backup as
> the
>> best way to recover from the error (unless the only repair needed is to
>> rebuild indexes). Primairly though, you need to work out why the error
>> happened in the first place.
>> Regards
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try
> with
>> > REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
>> >
>> > Take a backup of Database prior to execute DBCC with Repair options.
>> >
>> > Thanks
>> > Hari
>> > SQL Server MVP
>> >
>> > "Dean J Garrett" <info@.amuletc.com> wrote in message
>> > news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
>> > > We have a SQL Server 2000 database with a table we cannot delete. We
>> first
>> > > try from Enterprise Manager, and then tried through VB code, and a
>> stored
>> > > procedure, but nothing is able to delete the table. Is there anything
>> else
>> > > to try?
>> > >
>> > > Thank you.
>> > >
>> > >
>> >
>> >
>>
>
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.DROP TABLE <table name>.
But what is the error you get?
--
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Have you tried running DBCC CHECKTABLE?
--
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...'
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > We have a SQL Server 2000 database with a table we cannot delete. We
first
> > try from Enterprise Manager, and then tried through VB code, and a
stored
> > procedure, but nothing is able to delete the table. Is there anything
else
> > to try?
> >
> > Thank you.
> >
> >
>|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > We have a SQL Server 2000 database with a table we cannot delete. We
first
> > try from Enterprise Manager, and then tried through VB code, and a
stored
> > procedure, but nothing is able to delete the table. Is there anything
else
> > to try?
> >
> > Thank you.
> >
> >
>|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try
with
> > REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> >
> > Take a backup of Database prior to execute DBCC with Repair options.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Dean J Garrett" <info@.amuletc.com> wrote in message
> > news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > > We have a SQL Server 2000 database with a table we cannot delete. We
> first
> > > try from Enterprise Manager, and then tried through VB code, and a
> stored
> > > procedure, but nothing is able to delete the table. Is there anything
> else
> > > to try?
> > >
> > > Thank you.
> > >
> > >
> >
> >
>|||Blocking? Did you check using sp_who, sp_who2 etc?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyzer.
> Each time when the command executes to drop the table, the program freezes,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> That is not what MS recommends. You should not repair without first
>> determining root cause of the errors and then you should use a backup as
> the
>> best way to recover from the error (unless the only repair needed is to
>> rebuild indexes). Primairly though, you need to work out why the error
>> happened in the first place.
>> Regards
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try
> with
>> > REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
>> >
>> > Take a backup of Database prior to execute DBCC with Repair options.
>> >
>> > Thanks
>> > Hari
>> > SQL Server MVP
>> >
>> > "Dean J Garrett" <info@.amuletc.com> wrote in message
>> > news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
>> > > We have a SQL Server 2000 database with a table we cannot delete. We
>> first
>> > > try from Enterprise Manager, and then tried through VB code, and a
>> stored
>> > > procedure, but nothing is able to delete the table. Is there anything
>> else
>> > > to try?
>> > >
>> > > Thank you.
>> > >
>> > >
>> >
>> >
>>
>
Corrupted table?
We have a SQL Server 2000 database with a table we cannot delete. We first
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.
DROP TABLE <table name>.
But what is the error you get?
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Have you tried running DBCC CHECKTABLE?
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else
>
|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else
>
|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
with
> first
> stored
> else
>
|||Blocking? Did you check using sp_who, sp_who2 etc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyzer.
> Each time when the command executes to drop the table, the program freezes,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> the
> rights.
> with
>
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.
DROP TABLE <table name>.
But what is the error you get?
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Have you tried running DBCC CHECKTABLE?
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>
|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else
>
|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else
>
|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
with
> first
> stored
> else
>
|||Blocking? Did you check using sp_who, sp_who2 etc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyzer.
> Each time when the command executes to drop the table, the program freezes,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> the
> rights.
> with
>
Corrupted table?
We have a SQL Server 2000 database with a table we cannot delete. We first
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.DROP TABLE <table name>.
But what is the error you get?
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Have you tried running DBCC CHECKTABLE?
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...'
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else[vbcol=seagreen]
>|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else[vbcol=seagreen]
>|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
> first
> stored
> else
>|||Blocking? Did you check using sp_who, sp_who2 etc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.
gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyze
r.
> Each time when the command executes to drop the table, the program freezes
,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> the
> rights.
> with
>
try from Enterprise Manager, and then tried through VB code, and a stored
procedure, but nothing is able to delete the table. Is there anything else
to try?
Thank you.DROP TABLE <table name>.
But what is the error you get?
Jacco Schalkwijk
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Have you tried running DBCC CHECKTABLE?
Andrew J. Kelly SQL MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Hi,
First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
Take a backup of Database prior to execute DBCC with Repair options.
Thanks
Hari
SQL Server MVP
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
> We have a SQL Server 2000 database with a table we cannot delete. We first
> try from Enterprise Manager, and then tried through VB code, and a stored
> procedure, but nothing is able to delete the table. Is there anything else
> to try?
> Thank you.
>|||Actually, there is no error. Enterprise Manager just freezes, and we have to
stop the task. Query Analyzer, doing the DROP TABLE reacts the same way. We
give it several minutes to come back. It shouldnt' take more than 10 minutes
to drop a table.Maybe we can let it run ...'
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eYQLnegQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> DROP TABLE <table name>.
> But what is the error you get?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else[vbcol=seagreen]
>|||That is not what MS recommends. You should not repair without first
determining root cause of the errors and then you should use a backup as the
best way to recover from the error (unless the only repair needed is to
rebuild indexes). Primairly though, you need to work out why the error
happened in the first place.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> First Execute DBCC CHECKTABLE(TABLENAME), If it gives error then try with
> REPAIR OPTIONS. See DBCC CHECKTABLE command in books online.
> Take a backup of Database prior to execute DBCC with Repair options.
> Thanks
> Hari
> SQL Server MVP
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:%23kZjFGgQFHA.1236@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
stored[vbcol=seagreen]
else[vbcol=seagreen]
>|||There is no error, we just can't drop the table. We've tried to delete the
table inside Enterprise Manager, from a VB program, and from Query Analyzer.
Each time when the command executes to drop the table, the program freezes,
and we have to stop the task. Maybe we should wait longer (10 minutes) to
see if an error eventually occurs, but after about 10 mins. there is no
error and the program stops responding to the request to drop the table.
I ran DBCC checkTable and there are no reported errors.
ANy ideas? Thanks!
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> That is not what MS recommends. You should not repair without first
> determining root cause of the errors and then you should use a backup as
the
> best way to recover from the error (unless the only repair needed is to
> rebuild indexes). Primairly though, you need to work out why the error
> happened in the first place.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#rmRpFkQFHA.2876@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
> first
> stored
> else
>|||Blocking? Did you check using sp_who, sp_who2 etc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:epK3ZkFRFHA.1236@.TK2MSFTNGP14.phx.
gbl...
> There is no error, we just can't drop the table. We've tried to delete the
> table inside Enterprise Manager, from a VB program, and from Query Analyze
r.
> Each time when the command executes to drop the table, the program freezes
,
> and we have to stop the task. Maybe we should wait longer (10 minutes) to
> see if an error eventually occurs, but after about 10 mins. there is no
> error and the program stops responding to the request to drop the table.
> I ran DBCC checkTable and there are no reported errors.
> ANy ideas? Thanks!
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uTAONbERFHA.1392@.TK2MSFTNGP10.phx.gbl...
> the
> rights.
> with
>
Corrupted Table removal problem
I have a table that I can no longer make design changes too, remove
altogether, or view all rows for. Enterprise manager just hangs. I can do
a view top 1 record, but not view all. I'm testing some stored procedures
to fill ntext fields using writetext and seem to be corrupting it somehow.
All other tables and database work fine ... problems occur here only.
Ideas on how to fix or move forward with this.
Thanks,
Andrew
Your best bet is usually to restore from a previous good backup. If this is
a table in which the data is not critical or can be rebuilt you might want
to take a look at DBCC CHECKTABLE in BooksOnLine.
Andrew J. Kelly SQL MVP
"Andrew Jones" <ajones@.kvmhc.org> wrote in message
news:10fdjev1ikfg8ac@.corp.supernews.com...
> I have a table that I can no longer make design changes too, remove
> altogether, or view all rows for. Enterprise manager just hangs. I can
do
> a view top 1 record, but not view all. I'm testing some stored procedures
> to fill ntext fields using writetext and seem to be corrupting it somehow.
> All other tables and database work fine ... problems occur here only.
> Ideas on how to fix or move forward with this.
> Thanks,
> Andrew
>
altogether, or view all rows for. Enterprise manager just hangs. I can do
a view top 1 record, but not view all. I'm testing some stored procedures
to fill ntext fields using writetext and seem to be corrupting it somehow.
All other tables and database work fine ... problems occur here only.
Ideas on how to fix or move forward with this.
Thanks,
Andrew
Your best bet is usually to restore from a previous good backup. If this is
a table in which the data is not critical or can be rebuilt you might want
to take a look at DBCC CHECKTABLE in BooksOnLine.
Andrew J. Kelly SQL MVP
"Andrew Jones" <ajones@.kvmhc.org> wrote in message
news:10fdjev1ikfg8ac@.corp.supernews.com...
> I have a table that I can no longer make design changes too, remove
> altogether, or view all rows for. Enterprise manager just hangs. I can
do
> a view top 1 record, but not view all. I'm testing some stored procedures
> to fill ntext fields using writetext and seem to be corrupting it somehow.
> All other tables and database work fine ... problems occur here only.
> Ideas on how to fix or move forward with this.
> Thanks,
> Andrew
>
Corrupted table or bug or..?
SQL 7.0
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
..the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
..the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
DonHow many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
>> SQL 7.0
>> In QA, if I execute the following:
>> SELECT name FROM maindata WHERE ddate = '20041011'
>> ..the name 'vpipx' does NOT show up.
>> However, if I execute:
>> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
>> = '20041011'
>> ..the name 'vpipx' DOES show up.
>> This is on one server only. Two other servers don't
>> exhibit this. I'm trying to figure out if there is
>> something corrupted in this particular table or where
the
>> problem is, or if it could happen to my other tables,
or
>> if it's happening in my other tables right now, and i'm
>> not aware of it.
>> To see if 'vpipx' is showing up as a result: After I
>> execute the query, I then do Edit/Find for 'vpipx' and
it
>> turns up "not found". I've also cut and pasted the
>> results into NotePad and searched for 'vpipx', and it
>> wasn't found.
>> I've checked the execution plans of the two queries and
>> see nothing unusual. There is only one index.
>>
>> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May
29
>> 2003 15:21:25
>> Copyright (c) 1988-2002 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service
>> Pack 4)
>> (1 row(s) affected)
>> CREATE TABLE [dbo].[MAINDATA] (
>> [Name] [varchar] (32) NOT NULL ,
>> [DDate] [smalldatetime] NOT NULL ,
>> [DO] [decimal](18, 6) NOT NULL ,
>> [DH] [decimal](18, 6) NOT NULL ,
>> [DL] [decimal](18, 6) NOT NULL ,
>> [DC] [decimal](18, 6) NOT NULL ,
>> [DV] [int] NOT NULL ,
>> [DOI] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
>> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
>> [PRIMARY]
>> GO
>>
>> DBCC CHECKDB turns up no errors.
>> There are 98 million records.
>> Any help appreciated.
>> Thx,
>> Don
>
>.
>|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
> >--Original Message--
> >How many records are you getting back normally?
> >
> >QA has a size limit on it, so all of the rows may not be
> showing up.
> >
> >Try the query from another application and see what it
> does. I would pipe
> >in the query to osql and pipe the results out to a text
> file.
> >
> >osql /E /iC:\input.sql /oC:\Output.txt
> >
> >
> >HTH
> >
> >Rick Sawtell
> >MCT, MCSD, MCDBA
> >
> >
> >
> >
> >"Don D" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> >> SQL 7.0
> >> In QA, if I execute the following:
> >>
> >> SELECT name FROM maindata WHERE ddate = '20041011'
> >>
> >> ..the name 'vpipx' does NOT show up.
> >>
> >> However, if I execute:
> >>
> >> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> >> = '20041011'
> >>
> >> ..the name 'vpipx' DOES show up.
> >>
> >> This is on one server only. Two other servers don't
> >> exhibit this. I'm trying to figure out if there is
> >> something corrupted in this particular table or where
> the
> >> problem is, or if it could happen to my other tables,
> or
> >> if it's happening in my other tables right now, and i'm
> >> not aware of it.
> >>
> >> To see if 'vpipx' is showing up as a result: After I
> >> execute the query, I then do Edit/Find for 'vpipx' and
> it
> >> turns up "not found". I've also cut and pasted the
> >> results into NotePad and searched for 'vpipx', and it
> >> wasn't found.
> >>
> >> I've checked the execution plans of the two queries and
> >> see nothing unusual. There is only one index.
> >>
> >>
> >> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May
> 29
> >> 2003 15:21:25
> >> Copyright (c) 1988-2002 Microsoft Corporation
> >> Standard Edition on Windows NT 5.0 (Build 2195: Service
> >> Pack 4)
> >>
> >> (1 row(s) affected)
> >>
> >> CREATE TABLE [dbo].[MAINDATA] (
> >> [Name] [varchar] (32) NOT NULL ,
> >> [DDate] [smalldatetime] NOT NULL ,
> >> [DO] [decimal](18, 6) NOT NULL ,
> >> [DH] [decimal](18, 6) NOT NULL ,
> >> [DL] [decimal](18, 6) NOT NULL ,
> >> [DC] [decimal](18, 6) NOT NULL ,
> >> [DV] [int] NOT NULL ,
> >> [DOI] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> >> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> >> [PRIMARY]
> >> GO
> >>
> >>
> >> DBCC CHECKDB turns up no errors.
> >>
> >> There are 98 million records.
> >>
> >> Any help appreciated.
> >>
> >> Thx,
> >> Don
> >>
> >
> >
> >.
> >|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
>> Ok, the OSQL query method worked.
>> There were approx 75,000 rows returned by the query.
>> Does this OSQL test suggest that all is OK internally?
>> That the reason it's not showing up in the QA query is
>> because of the size limit?
>> thx,
>> Don
>> >--Original Message--
>> >How many records are you getting back normally?
>> >
>> >QA has a size limit on it, so all of the rows may not
be
>> showing up.
>> >
>> >Try the query from another application and see what it
>> does. I would pipe
>> >in the query to osql and pipe the results out to a
text
>> file.
>> >
>> >osql /E /iC:\input.sql /oC:\Output.txt
>> >
>> >
>> >HTH
>> >
>> >Rick Sawtell
>> >MCT, MCSD, MCDBA
>> >
>> >
>> >
>> >
>> >"Don D" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
>> >> SQL 7.0
>> >> In QA, if I execute the following:
>> >>
>> >> SELECT name FROM maindata WHERE ddate = '20041011'
>> >>
>> >> ..the name 'vpipx' does NOT show up.
>> >>
>> >> However, if I execute:
>> >>
>> >> SELECT * FROM maindata WHERE name = 'vpipx' and
ddate
>> >> = '20041011'
>> >>
>> >> ..the name 'vpipx' DOES show up.
>> >>
>> >> This is on one server only. Two other servers don't
>> >> exhibit this. I'm trying to figure out if there is
>> >> something corrupted in this particular table or
where
>> the
>> >> problem is, or if it could happen to my other
tables,
>> or
>> >> if it's happening in my other tables right now, and
i'm
>> >> not aware of it.
>> >>
>> >> To see if 'vpipx' is showing up as a result: After I
>> >> execute the query, I then do Edit/Find for 'vpipx'
and
>> it
>> >> turns up "not found". I've also cut and pasted the
>> >> results into NotePad and searched for 'vpipx', and
it
>> >> wasn't found.
>> >>
>> >> I've checked the execution plans of the two queries
and
>> >> see nothing unusual. There is only one index.
>> >>
>> >>
>> >> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May
>> 29
>> >> 2003 15:21:25
>> >> Copyright (c) 1988-2002 Microsoft Corporation
>> >> Standard Edition on Windows NT 5.0 (Build 2195:
Service
>> >> Pack 4)
>> >>
>> >> (1 row(s) affected)
>> >>
>> >> CREATE TABLE [dbo].[MAINDATA] (
>> >> [Name] [varchar] (32) NOT NULL ,
>> >> [DDate] [smalldatetime] NOT NULL ,
>> >> [DO] [decimal](18, 6) NOT NULL ,
>> >> [DH] [decimal](18, 6) NOT NULL ,
>> >> [DL] [decimal](18, 6) NOT NULL ,
>> >> [DC] [decimal](18, 6) NOT NULL ,
>> >> [DV] [int] NOT NULL ,
>> >> [DOI] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
>> >> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
>> >> [PRIMARY]
>> >> GO
>> >>
>> >>
>> >> DBCC CHECKDB turns up no errors.
>> >>
>> >> There are 98 million records.
>> >>
>> >> Any help appreciated.
>> >>
>> >> Thx,
>> >> Don
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
..the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
..the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
DonHow many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
>> SQL 7.0
>> In QA, if I execute the following:
>> SELECT name FROM maindata WHERE ddate = '20041011'
>> ..the name 'vpipx' does NOT show up.
>> However, if I execute:
>> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
>> = '20041011'
>> ..the name 'vpipx' DOES show up.
>> This is on one server only. Two other servers don't
>> exhibit this. I'm trying to figure out if there is
>> something corrupted in this particular table or where
the
>> problem is, or if it could happen to my other tables,
or
>> if it's happening in my other tables right now, and i'm
>> not aware of it.
>> To see if 'vpipx' is showing up as a result: After I
>> execute the query, I then do Edit/Find for 'vpipx' and
it
>> turns up "not found". I've also cut and pasted the
>> results into NotePad and searched for 'vpipx', and it
>> wasn't found.
>> I've checked the execution plans of the two queries and
>> see nothing unusual. There is only one index.
>>
>> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May
29
>> 2003 15:21:25
>> Copyright (c) 1988-2002 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service
>> Pack 4)
>> (1 row(s) affected)
>> CREATE TABLE [dbo].[MAINDATA] (
>> [Name] [varchar] (32) NOT NULL ,
>> [DDate] [smalldatetime] NOT NULL ,
>> [DO] [decimal](18, 6) NOT NULL ,
>> [DH] [decimal](18, 6) NOT NULL ,
>> [DL] [decimal](18, 6) NOT NULL ,
>> [DC] [decimal](18, 6) NOT NULL ,
>> [DV] [int] NOT NULL ,
>> [DOI] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
>> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
>> [PRIMARY]
>> GO
>>
>> DBCC CHECKDB turns up no errors.
>> There are 98 million records.
>> Any help appreciated.
>> Thx,
>> Don
>
>.
>|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
> >--Original Message--
> >How many records are you getting back normally?
> >
> >QA has a size limit on it, so all of the rows may not be
> showing up.
> >
> >Try the query from another application and see what it
> does. I would pipe
> >in the query to osql and pipe the results out to a text
> file.
> >
> >osql /E /iC:\input.sql /oC:\Output.txt
> >
> >
> >HTH
> >
> >Rick Sawtell
> >MCT, MCSD, MCDBA
> >
> >
> >
> >
> >"Don D" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> >> SQL 7.0
> >> In QA, if I execute the following:
> >>
> >> SELECT name FROM maindata WHERE ddate = '20041011'
> >>
> >> ..the name 'vpipx' does NOT show up.
> >>
> >> However, if I execute:
> >>
> >> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> >> = '20041011'
> >>
> >> ..the name 'vpipx' DOES show up.
> >>
> >> This is on one server only. Two other servers don't
> >> exhibit this. I'm trying to figure out if there is
> >> something corrupted in this particular table or where
> the
> >> problem is, or if it could happen to my other tables,
> or
> >> if it's happening in my other tables right now, and i'm
> >> not aware of it.
> >>
> >> To see if 'vpipx' is showing up as a result: After I
> >> execute the query, I then do Edit/Find for 'vpipx' and
> it
> >> turns up "not found". I've also cut and pasted the
> >> results into NotePad and searched for 'vpipx', and it
> >> wasn't found.
> >>
> >> I've checked the execution plans of the two queries and
> >> see nothing unusual. There is only one index.
> >>
> >>
> >> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May
> 29
> >> 2003 15:21:25
> >> Copyright (c) 1988-2002 Microsoft Corporation
> >> Standard Edition on Windows NT 5.0 (Build 2195: Service
> >> Pack 4)
> >>
> >> (1 row(s) affected)
> >>
> >> CREATE TABLE [dbo].[MAINDATA] (
> >> [Name] [varchar] (32) NOT NULL ,
> >> [DDate] [smalldatetime] NOT NULL ,
> >> [DO] [decimal](18, 6) NOT NULL ,
> >> [DH] [decimal](18, 6) NOT NULL ,
> >> [DL] [decimal](18, 6) NOT NULL ,
> >> [DC] [decimal](18, 6) NOT NULL ,
> >> [DV] [int] NOT NULL ,
> >> [DOI] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> >> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> >> [PRIMARY]
> >> GO
> >>
> >>
> >> DBCC CHECKDB turns up no errors.
> >>
> >> There are 98 million records.
> >>
> >> Any help appreciated.
> >>
> >> Thx,
> >> Don
> >>
> >
> >
> >.
> >|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
>> Ok, the OSQL query method worked.
>> There were approx 75,000 rows returned by the query.
>> Does this OSQL test suggest that all is OK internally?
>> That the reason it's not showing up in the QA query is
>> because of the size limit?
>> thx,
>> Don
>> >--Original Message--
>> >How many records are you getting back normally?
>> >
>> >QA has a size limit on it, so all of the rows may not
be
>> showing up.
>> >
>> >Try the query from another application and see what it
>> does. I would pipe
>> >in the query to osql and pipe the results out to a
text
>> file.
>> >
>> >osql /E /iC:\input.sql /oC:\Output.txt
>> >
>> >
>> >HTH
>> >
>> >Rick Sawtell
>> >MCT, MCSD, MCDBA
>> >
>> >
>> >
>> >
>> >"Don D" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
>> >> SQL 7.0
>> >> In QA, if I execute the following:
>> >>
>> >> SELECT name FROM maindata WHERE ddate = '20041011'
>> >>
>> >> ..the name 'vpipx' does NOT show up.
>> >>
>> >> However, if I execute:
>> >>
>> >> SELECT * FROM maindata WHERE name = 'vpipx' and
ddate
>> >> = '20041011'
>> >>
>> >> ..the name 'vpipx' DOES show up.
>> >>
>> >> This is on one server only. Two other servers don't
>> >> exhibit this. I'm trying to figure out if there is
>> >> something corrupted in this particular table or
where
>> the
>> >> problem is, or if it could happen to my other
tables,
>> or
>> >> if it's happening in my other tables right now, and
i'm
>> >> not aware of it.
>> >>
>> >> To see if 'vpipx' is showing up as a result: After I
>> >> execute the query, I then do Edit/Find for 'vpipx'
and
>> it
>> >> turns up "not found". I've also cut and pasted the
>> >> results into NotePad and searched for 'vpipx', and
it
>> >> wasn't found.
>> >>
>> >> I've checked the execution plans of the two queries
and
>> >> see nothing unusual. There is only one index.
>> >>
>> >>
>> >> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May
>> 29
>> >> 2003 15:21:25
>> >> Copyright (c) 1988-2002 Microsoft Corporation
>> >> Standard Edition on Windows NT 5.0 (Build 2195:
Service
>> >> Pack 4)
>> >>
>> >> (1 row(s) affected)
>> >>
>> >> CREATE TABLE [dbo].[MAINDATA] (
>> >> [Name] [varchar] (32) NOT NULL ,
>> >> [DDate] [smalldatetime] NOT NULL ,
>> >> [DO] [decimal](18, 6) NOT NULL ,
>> >> [DH] [decimal](18, 6) NOT NULL ,
>> >> [DL] [decimal](18, 6) NOT NULL ,
>> >> [DC] [decimal](18, 6) NOT NULL ,
>> >> [DV] [int] NOT NULL ,
>> >> [DOI] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
>> >> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
>> >> [PRIMARY]
>> >> GO
>> >>
>> >>
>> >> DBCC CHECKDB turns up no errors.
>> >>
>> >> There are 98 million records.
>> >>
>> >> Any help appreciated.
>> >>
>> >> Thx,
>> >> Don
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Corrupted table or bug or..?
SQL 7.0
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
...the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
...the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
Don
How many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>
|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
the[vbcol=seagreen]
or[vbcol=seagreen]
it[vbcol=seagreen]
29
>
>.
>
|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...[vbcol=seagreen]
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
> showing up.
> does. I would pipe
> file.
> message
> the
> or
> it
> 29
|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
be[vbcol=seagreen]
text[vbcol=seagreen]
ddate[vbcol=seagreen]
where[vbcol=seagreen]
tables,[vbcol=seagreen]
i'm[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
and[vbcol=seagreen]
May[vbcol=seagreen]
Service
>
>.
>
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
...the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
...the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
Don
How many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>
|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
the[vbcol=seagreen]
or[vbcol=seagreen]
it[vbcol=seagreen]
29
>
>.
>
|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...[vbcol=seagreen]
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
> showing up.
> does. I would pipe
> file.
> message
> the
> or
> it
> 29
|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
be[vbcol=seagreen]
text[vbcol=seagreen]
ddate[vbcol=seagreen]
where[vbcol=seagreen]
tables,[vbcol=seagreen]
i'm[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
and[vbcol=seagreen]
May[vbcol=seagreen]
Service
>
>.
>
Corrupted table or bug or..?
SQL 7.0
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
..the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
..the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
DonHow many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
the[vbcol=seagreen]
or[vbcol=seagreen]
it[vbcol=seagreen]
29[vbcol=seagreen]
>
>.
>|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...[vbcol=seagreen]
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
>
> showing up.
> does. I would pipe
> file.
> message
> the
> or
> it
> 29|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
be[vbcol=seagreen]
text[vbcol=seagreen]
ddate[vbcol=seagreen]
where[vbcol=seagreen]
tables,[vbcol=seagreen]
i'm[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
and[vbcol=seagreen]
May[vbcol=seagreen]
Service[vbcol=seagreen]
>
>.
>
In QA, if I execute the following:
SELECT name FROM maindata WHERE ddate = '20041011'
..the name 'vpipx' does NOT show up.
However, if I execute:
SELECT * FROM maindata WHERE name = 'vpipx' and ddate
= '20041011'
..the name 'vpipx' DOES show up.
This is on one server only. Two other servers don't
exhibit this. I'm trying to figure out if there is
something corrupted in this particular table or where the
problem is, or if it could happen to my other tables, or
if it's happening in my other tables right now, and i'm
not aware of it.
To see if 'vpipx' is showing up as a result: After I
execute the query, I then do Edit/Find for 'vpipx' and it
turns up "not found". I've also cut and pasted the
results into NotePad and searched for 'vpipx', and it
wasn't found.
I've checked the execution plans of the two queries and
see nothing unusual. There is only one index.
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
[MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
DBCC CHECKDB turns up no errors.
There are 98 million records.
Any help appreciated.
Thx,
DonHow many records are you getting back normally?
QA has a size limit on it, so all of the rows may not be showing up.
Try the query from another application and see what it does. I would pipe
in the query to osql and pipe the results out to a text file.
osql /E /iC:\input.sql /oC:\Output.txt
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
> SQL 7.0
> In QA, if I execute the following:
> SELECT name FROM maindata WHERE ddate = '20041011'
> ..the name 'vpipx' does NOT show up.
> However, if I execute:
> SELECT * FROM maindata WHERE name = 'vpipx' and ddate
> = '20041011'
> ..the name 'vpipx' DOES show up.
> This is on one server only. Two other servers don't
> exhibit this. I'm trying to figure out if there is
> something corrupted in this particular table or where the
> problem is, or if it could happen to my other tables, or
> if it's happening in my other tables right now, and i'm
> not aware of it.
> To see if 'vpipx' is showing up as a result: After I
> execute the query, I then do Edit/Find for 'vpipx' and it
> turns up "not found". I've also cut and pasted the
> results into NotePad and searched for 'vpipx', and it
> wasn't found.
> I've checked the execution plans of the two queries and
> see nothing unusual. There is only one index.
>
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
> 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
> Pack 4)
> (1 row(s) affected)
> CREATE TABLE [dbo].[MAINDATA] (
> [Name] [varchar] (32) NOT NULL ,
> [DDate] [smalldatetime] NOT NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [DV] [int] NOT NULL ,
> [DOI] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [SDate] ON [dbo].
> [MAINDATA]([Name], [DDate]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>
> DBCC CHECKDB turns up no errors.
> There are 98 million records.
> Any help appreciated.
> Thx,
> Don
>|||Ok, the OSQL query method worked.
There were approx 75,000 rows returned by the query.
Does this OSQL test suggest that all is OK internally?
That the reason it's not showing up in the QA query is
because of the size limit?
thx,
Don
>--Original Message--
>How many records are you getting back normally?
>QA has a size limit on it, so all of the rows may not be
showing up.
>Try the query from another application and see what it
does. I would pipe
>in the query to osql and pipe the results out to a text
file.
>osql /E /iC:\input.sql /oC:\Output.txt
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05fe01c4b6c0$569d9340$a401280a@.phx.gbl...
the[vbcol=seagreen]
or[vbcol=seagreen]
it[vbcol=seagreen]
29[vbcol=seagreen]
>
>.
>|||That would be my guess. In QA, check the Tools->Options tab. You can make
modifications to the amount of data that is returned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Don D" <anonymous@.discussions.microsoft.com> wrote in message
news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...[vbcol=seagreen]
> Ok, the OSQL query method worked.
> There were approx 75,000 rows returned by the query.
> Does this OSQL test suggest that all is OK internally?
> That the reason it's not showing up in the QA query is
> because of the size limit?
> thx,
> Don
>
> showing up.
> does. I would pipe
> file.
> message
> the
> or
> it
> 29|||Thanks for your help...greatly appreciated :-)
Don
>--Original Message--
>That would be my guess. In QA, check the Tools->Options
tab. You can make
>modifications to the amount of data that is returned.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>"Don D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1aa001c4b6e0$92ab5cd0$a501280a@.phx.gbl...
be[vbcol=seagreen]
text[vbcol=seagreen]
ddate[vbcol=seagreen]
where[vbcol=seagreen]
tables,[vbcol=seagreen]
i'm[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
and[vbcol=seagreen]
May[vbcol=seagreen]
Service[vbcol=seagreen]
>
>.
>
Subscribe to:
Posts (Atom)