IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on each
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:
> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> is always the same but, the location of the error in the output moves on each
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp all
> of the data to a new database. Are there any other options for this?
> -Alan-|||The Object ID
992722589 does not exist so there is nothing to check. Each time DBCC
CHECKALLOC is run it appears in the results of a different table.
"Peter Nolan" wrote:
> Couple of things...
> If the 'ID' column is still unique, then check if its got a unique
> constraint on, if not then put it on.
> If that doe't work take off the foreign key from all tables referencing the
> table which ID is part of and do your CHECKALLOC again.
> If that doesn't work then have a look at
> http://support.microsoft.com/kb/816084
> Peter
>
>
> "sqlsleuth" wrote:
> > Server: Msg 2576, Level 16, State 1, Line 1
> > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > object ID 992722589 index ID 0 but was not detected in the scan.
> > Server: Msg 2577, Level 16, State 1, Line 1
> > Chain sequence numbers are out of order in IAM chain for object ID
> > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > (1:48122) sequence number 0.
> >
> > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > is always the same but, the location of the error in the output moves on each
> > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > Going to a previous backup is not an option so I am getting ready to bcp all
> > of the data to a new database. Are there any other options for this?
> >
> > -Alan-|||What about http://support.microsoft.com/kb/816084 ?
Do you have auto statisics turned on ?
Are there any unique constraints in the tables themselves ?
The reason I'm going down this route is that occasionally when bulk loading
stuff I take off primary keys, indexes ect but somehow the system doesn't
register it properly and still thinks there something there, it could be as
part of the sysindexes or the statistics.
When it happened to me I re-established my primary key indexes ect.
You could also try dropping you statistics are re-establishing them.
Anyways thats about all I can help you, good luck and I hope you get a
successful resolution.
Peter
Peter
"sqlsleuth" wrote:
> The Object ID
> 992722589 does not exist so there is nothing to check. Each time DBCC
> CHECKALLOC is run it appears in the results of a different table.
> "Peter Nolan" wrote:
> > Couple of things...
> >
> > If the 'ID' column is still unique, then check if its got a unique
> > constraint on, if not then put it on.
> >
> > If that doe't work take off the foreign key from all tables referencing the
> > table which ID is part of and do your CHECKALLOC again.
> >
> > If that doesn't work then have a look at
> > http://support.microsoft.com/kb/816084
> >
> > Peter
> >
> >
> >
> >
> > "sqlsleuth" wrote:
> >
> > > Server: Msg 2576, Level 16, State 1, Line 1
> > > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > > object ID 992722589 index ID 0 but was not detected in the scan.
> > > Server: Msg 2577, Level 16, State 1, Line 1
> > > Chain sequence numbers are out of order in IAM chain for object ID
> > > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > > (1:48122) sequence number 0.
> > >
> > > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > > is always the same but, the location of the error in the output moves on each
> > > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > > Going to a previous backup is not an option so I am getting ready to bcp all
> > > of the data to a new database. Are there any other options for this?
> > >
> > > -Alan-|||Auto Statistics is on and there are a large number of unique constraints in
the tables. I can try to run the suggested fixes on the backup database and
see if it helps.
Thanks for the help.
"Peter Nolan" wrote:
> What about http://support.microsoft.com/kb/816084 ?
> Do you have auto statisics turned on ?
> Are there any unique constraints in the tables themselves ?
> The reason I'm going down this route is that occasionally when bulk loading
> stuff I take off primary keys, indexes ect but somehow the system doesn't
> register it properly and still thinks there something there, it could be as
> part of the sysindexes or the statistics.
> When it happened to me I re-established my primary key indexes ect.
> You could also try dropping you statistics are re-establishing them.
> Anyways thats about all I can help you, good luck and I hope you get a
> successful resolution.
> Peter
> Peter
> "sqlsleuth" wrote:
> > The Object ID
> > 992722589 does not exist so there is nothing to check. Each time DBCC
> > CHECKALLOC is run it appears in the results of a different table.
> >
> > "Peter Nolan" wrote:
> >
> > > Couple of things...
> > >
> > > If the 'ID' column is still unique, then check if its got a unique
> > > constraint on, if not then put it on.
> > >
> > > If that doe't work take off the foreign key from all tables referencing the
> > > table which ID is part of and do your CHECKALLOC again.
> > >
> > > If that doesn't work then have a look at
> > > http://support.microsoft.com/kb/816084
> > >
> > > Peter
> > >
> > >
> > >
> > >
> > > "sqlsleuth" wrote:
> > >
> > > > Server: Msg 2576, Level 16, State 1, Line 1
> > > > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > > > object ID 992722589 index ID 0 but was not detected in the scan.
> > > > Server: Msg 2577, Level 16, State 1, Line 1
> > > > Chain sequence numbers are out of order in IAM chain for object ID
> > > > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > > > (1:48122) sequence number 0.
> > > >
> > > > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > > > is always the same but, the location of the error in the output moves on each
> > > > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > > > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > > > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > > > Going to a previous backup is not an option so I am getting ready to bcp all
> > > > of the data to a new database. Are there any other options for this?
> > > >
> > > > -Alan-|||For complete troubleshooting of the above error message, you will need to firstly analyze the exact reason for the corruption of sequence numbers of IAM pages. If the corruption is caused due to hardware failure, then change the hardware component. However, in case of software corruption, run DBCC CHECKDB command  with appropriate repair clause  to repair the database.



DBCC CHECKDB command allows complete repair in most cases. But, if in case the command fails, then the only option is to use a advanced SQL Recovery software.

IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on each
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-
Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:

> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> is always the same but, the location of the error in the output moves on each
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp all
> of the data to a new database. Are there any other options for this?
> -Alan-

IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on eac
h
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBC
C
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:

> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page numbe
r
> is always the same but, the location of the error in the output moves on e
ach
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run D
BCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp a
ll
> of the data to a new database. Are there any other options for this?
> -Alan-sql

I/O error bad page ID (was "Help")

Is there any other solutions that can I attach my data
with and error of Msg 823 Level 24 State 1 Line 1
I/O error (bad page ID)do you have a backup?|||For the most part: No. Restore from the last good backup. It sounds as if the database files were copied off the SQL Server while the SQL Server service was running. (Assuming you are trying to re-attach databases that is).

I/O error 38(Reached the end of the file.)

We received this error last night:
Error: 823, Severity: 24, State: 2
I/O error 38(Reached the end of the file.) detected during read at offset
0x000000dfbce000 in file
'D:\FocalPoint\Databases\001_001\pdi_sta
ge_001_001.mdf'..
It occured during cube and dimision updates on analytical services. The D
drive resides on a SAN. Our network administrator is saying there are no
drive issues. Everything I have read on Error 823 is hardware related.
Any suggestions. Thanks.Take a look at http://support.microsoft.com/kb/313702
Linchi
"brymer28303" wrote:

> We received this error last night:
> Error: 823, Severity: 24, State: 2
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0x000000dfbce000 in file
> 'D:\FocalPoint\Databases\001_001\pdi_sta
ge_001_001.mdf'..
> It occured during cube and dimision updates on analytical services. The D
> drive resides on a SAN. Our network administrator is saying there are no
> drive issues. Everything I have read on Error 823 is hardware related.
> Any suggestions. Thanks.
>

I/O error 38(Reached the end of the file.)

We received this error last night:
Error: 823, Severity: 24, State: 2
I/O error 38(Reached the end of the file.) detected during read at offset
0x000000dfbce000 in file
'D:\FocalPoint\Databases\001_001\pdi_stage_001_001 .mdf'..
It occured during cube and dimision updates on analytical services. The D
drive resides on a SAN. Our network administrator is saying there are no
drive issues. Everything I have read on Error 823 is hardware related.
Any suggestions. Thanks.
Take a look at http://support.microsoft.com/kb/313702
Linchi
"brymer28303" wrote:

> We received this error last night:
> Error: 823, Severity: 24, State: 2
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0x000000dfbce000 in file
> 'D:\FocalPoint\Databases\001_001\pdi_stage_001_001 .mdf'..
> It occured during cube and dimision updates on analytical services. The D
> drive resides on a SAN. Our network administrator is saying there are no
> drive issues. Everything I have read on Error 823 is hardware related.
> Any suggestions. Thanks.
>

I/O error 38(Reached the end of the file.)

We received this error last night:
Error: 823, Severity: 24, State: 2
I/O error 38(Reached the end of the file.) detected during read at offset
0x000000dfbce000 in file
'D:\FocalPoint\Databases\001_001\pdi_stage_001_001.mdf'..
It occured during cube and dimision updates on analytical services. The D
drive resides on a SAN. Our network administrator is saying there are no
drive issues. Everything I have read on Error 823 is hardware related.
Any suggestions. Thanks.Take a look at http://support.microsoft.com/kb/313702
Linchi
"brymer28303" wrote:
> We received this error last night:
> Error: 823, Severity: 24, State: 2
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0x000000dfbce000 in file
> 'D:\FocalPoint\Databases\001_001\pdi_stage_001_001.mdf'..
> It occured during cube and dimision updates on analytical services. The D
> drive resides on a SAN. Our network administrator is saying there are no
> drive issues. Everything I have read on Error 823 is hardware related.
> Any suggestions. Thanks.
>

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/pr...ntsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.
You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>
|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Do a find for Torn I/O.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Do a find for Torn I/O.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>sql

I/O error (Torn page)

I run a sp in sqlserver 2000 and got this error message.
(1 row affected)
Msg 823, Level 24, State 2, Server DBINT02, Procedure
KundAvpris_Insert, Line 13
I/O error (torn page) detected during read at offset
0x0000013a29a000 in file
'E:\Program Files\Microsoft SQL
Server\MSSQL\data\MARKISDATA_Data.MDF'.
Does anybody know how i do to correct this error?I suggest you perform a log backup. Then restore the latest clean database backup and all subsequent
log backups (including this last one). This will most probably give you zero data loss.
If you don't have log backups in place, then just go for the last clean database backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Robert
Ask your system
administrator to check for disk corruption.You should make sure to run DBCC
CHECKDB or DBCC CHECKTABLE on that table.
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Torn pages does most likely occur because a partially performed write operation. CHECKDB or
CHECKTABLE does not help here, as it will only confirm what we already know: a corruption in the
database. Also, this does, unfortunately, temp some to try the repair options (which in most cases
doesn't help), and possibly hinder the ability to do the vital last log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uhUFzm4dDHA.2340@.TK2MSFTNGP09.phx.gbl...
> Robert
> Ask your system
> administrator to check for disk corruption.You should make sure to run DBCC
> CHECKDB or DBCC CHECKTABLE on that table.
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||That might not all be necessary. If the torn page is in a non-clustered
index you can just rebuild the index and everything will be fine. If it is
in a clustered index or it is a page that is used by SQL Server internally,
Tibor's method is the safest way to go.
DBCC CHECKDB will tell you in which object the torn page is located. You can
run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
which can otherwise take a long time.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> I suggest you perform a log backup. Then restore the latest clean database
backup and all subsequent
> log backups (including this last one). This will most probably give you
zero data loss.
> If you don't have log backups in place, then just go for the last clean
database backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine.
Does above apply to torn pages as well?
I thought that torn pages are "corrupted beyond repair", even if a page can, technically, be dropped
as part of an index...
I.e., a torn page marks a "hands off - something is fishy here" to SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine. If it is
> in a clustered index or it is a page that is used by SQL Server internally,
> Tibor's method is the safest way to go.
> DBCC CHECKDB will tell you in which object the torn page is located. You can
> run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
> which can otherwise take a long time.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > I suggest you perform a log backup. Then restore the latest clean database
> backup and all subsequent
> > log backups (including this last one). This will most probably give you
> zero data loss.
> >
> > If you don't have log backups in place, then just go for the last clean
> database backups.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > I run a sp in sqlserver 2000 and got this error message.
> > >
> > > (1 row affected)
> > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > KundAvpris_Insert, Line 13
> > > I/O error (torn page) detected during read at offset
> > > 0x0000013a29a000 in file
> > > 'E:\Program Files\Microsoft SQL
> > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > >
> > > Does anybody know how i do to correct this error?
> >
> >
>|||Only thing a torn page tells you as far as I understand it, is that it was
written to disk partially but not completely, i.e. the check bits for all
the 512 byte sectors is the page are not the same, which means that some of
the sectors have changed the last time the page was written and some
haven't. It's a "logical" rather than a physical error, it doesn't tell you
anything about the current physical state of the page only about the current
logical state of the page (inconsistent) and that the last write operation
on that page didn't succeed completely. The page being torn in itself
doesn't make the harddisk space where it is located unusable. (The torn page
can ofcourse be caused by a harddisk problem which makes the disk space
unusable, but that's a separate issue.)
If the torn page has been cause by a power failure or a similar problem,
that is not a permanent hardware problem, like a bad sector on a disk, I see
no reason why you could not reuse the page?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine.
> Does above apply to torn pages as well?
> I thought that torn pages are "corrupted beyond repair", even if a page
can, technically, be dropped
> as part of an index...
> I.e., a torn page marks a "hands off - something is fishy here" to SQL
Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine. If it
is
> > in a clustered index or it is a page that is used by SQL Server
internally,
> > Tibor's method is the safest way to go.
> >
> > DBCC CHECKDB will tell you in which object the torn page is located. You
can
> > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
process,
> > which can otherwise take a long time.
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > I suggest you perform a log backup. Then restore the latest clean
database
> > backup and all subsequent
> > > log backups (including this last one). This will most probably give
you
> > zero data loss.
> > >
> > > If you don't have log backups in place, then just go for the last
clean
> > database backups.
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > I run a sp in sqlserver 2000 and got this error message.
> > > >
> > > > (1 row affected)
> > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > KundAvpris_Insert, Line 13
> > > > I/O error (torn page) detected during read at offset
> > > > 0x0000013a29a000 in file
> > > > 'E:\Program Files\Microsoft SQL
> > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > >
> > > > Does anybody know how i do to correct this error?
> > >
> > >
> >
> >
>|||I agree, Jacco. My point as only the SQL Server code (design of-). Whether SQL Server will never
re-uses/repairs a torn page or not, even though it can safely drop the page (because the HW might be
OK). I guess the answer is inside the SQL Server code, which I don't have access to... ;-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> Only thing a torn page tells you as far as I understand it, is that it was
> written to disk partially but not completely, i.e. the check bits for all
> the 512 byte sectors is the page are not the same, which means that some of
> the sectors have changed the last time the page was written and some
> haven't. It's a "logical" rather than a physical error, it doesn't tell you
> anything about the current physical state of the page only about the current
> logical state of the page (inconsistent) and that the last write operation
> on that page didn't succeed completely. The page being torn in itself
> doesn't make the harddisk space where it is located unusable. (The torn page
> can ofcourse be caused by a harddisk problem which makes the disk space
> unusable, but that's a separate issue.)
> If the torn page has been cause by a power failure or a similar problem,
> that is not a permanent hardware problem, like a bad sector on a disk, I see
> no reason why you could not reuse the page?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine.
> >
> > Does above apply to torn pages as well?
> > I thought that torn pages are "corrupted beyond repair", even if a page
> can, technically, be dropped
> > as part of an index...
> > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> Server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine. If it
> is
> > > in a clustered index or it is a page that is used by SQL Server
> internally,
> > > Tibor's method is the safest way to go.
> > >
> > > DBCC CHECKDB will tell you in which object the torn page is located. You
> can
> > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> process,
> > > which can otherwise take a long time.
> > >
> > > --
> > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > Database Administrator
> > > Eurostop Ltd.
> > >
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > I suggest you perform a log backup. Then restore the latest clean
> database
> > > backup and all subsequent
> > > > log backups (including this last one). This will most probably give
> you
> > > zero data loss.
> > > >
> > > > If you don't have log backups in place, then just go for the last
> clean
> > > database backups.
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > >
> > > > > (1 row affected)
> > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > KundAvpris_Insert, Line 13
> > > > > I/O error (torn page) detected during read at offset
> > > > > 0x0000013a29a000 in file
> > > > > 'E:\Program Files\Microsoft SQL
> > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > >
> > > > > Does anybody know how i do to correct this error?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry Tibor, your post is not really clear to me...
I don't think that you can't drop an index because it contains a torn page,
if that is what you mean? (Too bad the problem is quite difficult to
replicate). Dropping an index only deallocates the index pages and deletes
the rows from the system tables and doesn't do anything to the actual pages,
so whether they are torn or not should not make any difference.
> I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
But you have access to people who have access (or at least have access to
people who have access) ;-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyZU7n5dDHA.1448@.TK2MSFTNGP12.phx.gbl...
> I agree, Jacco. My point as only the SQL Server code (design of-). Whether
SQL Server will never
> re-uses/repairs a torn page or not, even though it can safely drop the
page (because the HW might be
> OK). I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> > Only thing a torn page tells you as far as I understand it, is that it
was
> > written to disk partially but not completely, i.e. the check bits for
all
> > the 512 byte sectors is the page are not the same, which means that some
of
> > the sectors have changed the last time the page was written and some
> > haven't. It's a "logical" rather than a physical error, it doesn't tell
you
> > anything about the current physical state of the page only about the
current
> > logical state of the page (inconsistent) and that the last write
operation
> > on that page didn't succeed completely. The page being torn in itself
> > doesn't make the harddisk space where it is located unusable. (The torn
page
> > can ofcourse be caused by a harddisk problem which makes the disk space
> > unusable, but that's a separate issue.)
> >
> > If the torn page has been cause by a power failure or a similar problem,
> > that is not a permanent hardware problem, like a bad sector on a disk, I
see
> > no reason why you could not reuse the page?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine.
> > >
> > > Does above apply to torn pages as well?
> > > I thought that torn pages are "corrupted beyond repair", even if a
page
> > can, technically, be dropped
> > > as part of an index...
> > > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> > Server.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine. If
it
> > is
> > > > in a clustered index or it is a page that is used by SQL Server
> > internally,
> > > > Tibor's method is the safest way to go.
> > > >
> > > > DBCC CHECKDB will tell you in which object the torn page is located.
You
> > can
> > > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> > process,
> > > > which can otherwise take a long time.
> > > >
> > > > --
> > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > > Database Administrator
> > > > Eurostop Ltd.
> > > >
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > > I suggest you perform a log backup. Then restore the latest clean
> > database
> > > > backup and all subsequent
> > > > > log backups (including this last one). This will most probably
give
> > you
> > > > zero data loss.
> > > > >
> > > > > If you don't have log backups in place, then just go for the last
> > clean
> > > > database backups.
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > > ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > > >
> > > > > > (1 row affected)
> > > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > > KundAvpris_Insert, Line 13
> > > > > > I/O error (torn page) detected during read at offset
> > > > > > 0x0000013a29a000 in file
> > > > > > 'E:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > > >
> > > > > > Does anybody know how i do to correct this error?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

I want to revert back to original state of database before i performed restore database

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?

Any suggestion will be highly appriciated.

Regards,
S. Domadia."san" <shdomadia@.gmail.comwrote in message
news:1163592758.514890.277030@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?
>

If I understand you correctly, no.

You restored a backup over a copy of your database?

That completely overwrites it.

Quote:

Originally Posted by

Any suggestion will be highly appriciated.
>
>
Regards,
S. Domadia.
>

|||You can't rollback a restore. Your only hope is that the SQL Server created
new files during the restore (e.g. backup was from a different server) and
the original deleted database files still exist on the drive. It's a long
shot but an undelete utility might allow you go recover those.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"san" <shdomadia@.gmail.comwrote in message
news:1163592758.514890.277030@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?
>
Any suggestion will be highly appriciated.
>
>
Regards,
S. Domadia.
>

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> >Out of the 3 tables below. I need to get a tabular report like this
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
--
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>> Server: Msg 245, Level 16, State 1, Line 1
>> Syntax error converting the varchar value 'David' to a column of data type
>> int.
>> SELECT SurveyResultID,
>> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
>> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
>> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
>> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
>> FROM FormCreator_Results
>> GROUP BY SurveyResultID
>> GO
>> --
>>
>> David Fetrow
>> Helixpoint LLC.
>> http://www.helixpoint.com
>> davef@.helixpoint.com
>> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>