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

nullsql-server-2005vb6

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.

Best Answer

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.

Related Solutions

SQL Server 2005 – clicking on job->Properties yields “New Job” window

I would suggest the following path:

Make sure that you have current backups for the server
Try to get a clean install of the XP service pack
Try reinstalling the client tools on the machine
If that fails, try to install (or reinstall) SP2 for SQL Server

Sql – Simulating group_concat MySQL function in Microsoft SQL Server 2005

No REAL easy way to do this. Lots of ideas out there, though.

Best one I've found:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

Or a version that works correctly if the data might contain characters such as <

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names)

Best Answer

Related Solutions

SQL Server 2005 – clicking on job->Properties yields “New Job” window

Sql – Simulating group_concat MySQL function in Microsoft SQL Server 2005

Related Topic