Help! Index corrupting? DBCC CheckTable failing?

We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)

Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
TotalValue(decimal(8))

Run DBCC CheckTable - no issues.
Create 4 non clustered indexes (3 single column, 1 two-column). All
indexes create fine.

Run DBCC CheckTable again and receive the following:
Server: Msg 8951, Level 16, State 1, Line 1

Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
key in index 'idx_totalvalue' (ID 7) for the row:

Server: Msg 8955, Level 16, State 1, Line 1

Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
index values (TotalValue = -10).

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
244195920) (index ID 7). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:20855652:338) with values (TotalValue = -0

4) points to the data row identified by (RID = (1:11154499:98)).

DBCC results for 'CD_Assets_bad2'.

There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.

CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'CD_Assets_bad2' (object ID 244195920).

repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (CD.dbo.CD_Assets_bad2 ).

Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.

Thanks,
David
david.schwartz@.schwab.com[posted and mailed, please reply in news]

David Schwartz (david.schwartz@.schwab.com) writes:
> Any ideas? It seems like some sort of corruption, but the index
> creates fine. If anyone can help please let me know. If I can provide
> any addtional information that might help, please let me know.

Both errors 8252 and 8956 have articles in Books Online. (Just search
for the numbers.)

Both articles says that the index must be reparied or dropped.

Since your indexes appears to be corrupted when you build them, it smells
a hardware problem to me. I would probably open a case with Microsoft
to investigate this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try Creating clusterd index and import the data after dropping all the
existing indexes in the destination table.

david.schwartz@.schwab.com (David Schwartz) wrote in message news:<32d60003.0312021706.10bee3d6@.posting.google.com>...
> We are having quite a time since moving a large database to a new
> server (actually built new server, renamed as old to make seamless for
> users, etc.)
> Import 104 million row database (5 column) into table (CD_Assets_bad2)
> from existing (CD_Assets):
> Account(varchar(8))
> TransactionDate(datetime(8)
> Flow(varchar(1))
> Category(varchar(7))
> TotalValue(decimal(8))
> Run DBCC CheckTable - no issues.
> Create 4 non clustered indexes (3 single column, 1 two-column). All
> indexes create fine.
> Run DBCC CheckTable again and receive the following:
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
> key in index 'idx_totalvalue' (ID 7) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
> index values (TotalValue = -10).
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
> 244195920) (index ID 7). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:20855652:338) with values (TotalValue = -0
> 4) points to the data row identified by (RID = (1:11154499:98)).
> DBCC results for 'CD_Assets_bad2'.
> There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'CD_Assets_bad2' (object ID 244195920).
> repair_fast is the minimum repair level for the errors found by DBCC
> CHECKTABLE (CD.dbo.CD_Assets_bad2 ).
> Any ideas? It seems like some sort of corruption, but the index
> creates fine. If anyone can help please let me know. If I can provide
> any addtional information that might help, please let me know.
> Thanks,
> David
> david.schwartz@.schwab.com

Help! - Error in moving data from AS400

I have a process that simply copies data from a table on an AS400 to SQL Server. This process executes fine until it gets to around 44 million rows copied out of 48 million rows in the source table. At this point, the process exits with the following error messages:

The attempt to send a row to SQL Server failed with error code 0x80004005.

The ProcessInput method on component "SQL Server Destination" (40) failed with error code 0xC02020C7.
The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Thread "WorkThread0" has exited with error code 0xC02020C7.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Thread "SourceThread0" has exited with error code 0xC0047038.

I am using the Microsoft OLEDB Provider for DB2 to access the AS400 and have tried the Table/View and SQL Command access modes with the same result.

Any help will be Greatly Appreciated!

-Mark Redman.

Hi Mark

The error was from your SQL Server Destination rather than from the DB2 source.

Which connection you have at your destination and which provider?

Thanks
Wenyang|||Hi Mark

This is not directly related to your problem, but I am also trying to use the MS OLE DB Provider for DB2 for connecting to an AS400, but I cannot make it work... How are your parameters setup? Could you perhaps post a screenshot?|||I am using the SQL Native Provider as the destination. It is a SQL Server 2005 Release Version server.|||The Parameters I have filled in are:

APPC Mode Name: QPCSUPP
APPC Security Type: Program
Defer Prepare: False
Derive Parameters False
Host CCSID 37
Network Address BKL400 <this would be different for you>
Network Port 446
Network Transport Library TCPIP
Package Collection QSYS2
PC Code Page 1252
Data Source bkl400
Initial Catalog bkl400
I hope this helps!|||The error was from your destination provider. Seems to me it's not related to the source you used in your package. I'm not sure whether you are using MS OLEDB for ODBC, if that's the case, search online using the error code 0x80004005 may bring you to some helpful web sites.

Thanks
Wenyang|||

The destination provider I am using is the Native OLEDB/SQL Native Client (SQLNCLI).