Attached SQL 2000 DB in a SQL 2005 Server, Now getting VB6 App throwing errors

Question

A SQL 2000 db was detached, SQL 2000 was uninstalled. Then SQL 2005 installed in the same machine. Then the database was attached to the newly installed SQL 2005 server. It seemed to work at first but after the users started to poke around, error related to null started to pop. Null values from datetime columns that DID NOT have nulls, and things like that. The db has a compatibility level to 2000 (8.0). What could be happening.

Answer 1

Have you you run DBCC UPDATEUSAGE?

In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. Therefore, databases that were created on versions prior to SQL Server 2005 may contain incorrect counts. After you upgrade a database to SQL Server 2005, we recommend that you run DBCC UPDATEUSAGE to correct any invalid counts. This DBCC statement corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. For more information, see DBCC UPDATEUSAGE (Transact-SQL).

Have you rebuilt all indexes?

exec sp_msforeachtable "dbcc dbreindex('?')"

Note: be careful if this is in production, as it might take a while. Perhaps talk to your DBA.