Tag: sql server 2000

Type In-row data is incorrect. Run DBCC UPDATEUSAGE.

The Issue:

Integrity Check (DBCC CHECKDB) produces the error

The In-row data USED page count for object “my_table”, index ID 9, partition ID 2609137898356736, alloc unit ID 2609137898356736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 2 consistency errors in table ‘my_table’ (object ID 1157579162).

CHECKDB found 0 allocation errors and 2 consistency errors in database ‘my_database’.
Outcome: Failed

BACKGROUND:

Database was just moved/upgraded from SQL Server 2000. SQL Server 2000 used to update the page space used metadata. After SQL Server 2005 the part of the migration/upgrade plan was to run DBCC UPDATEUSAGE. If you didn’t the error will appear when you run DBCC CHECKDB.

The output message above tell us that there is 1 consistency error in the database “my_database” and is a logical inconsistency. The error indicates that there are pages and row counts inaccuracies for that table and this type of errors can be fixed by running DBCC UPDATEUSAGE command.

HOW TO FIX:

Execute the DBCC UPDATEUSAGE(my_database) command in the context of your database you will see the following message indicating that were fixed the inaccuracies.

DBCC UPDATEUSAGE: Usage counts updated for table ‘my_table’ (index ‘my_table’ , partition 1):        RSVD pages (In-row Data): changed from … to … pages.DBCC execution completed. If DBCC printed error messages, contact your system administrator

 

After the page counts have been corrected your SQL Server will be back to normal. You can run DBCC CHECKDB after and it should not return any error messages.

 

Linked Server Error “Cannot obtain the schema rowset”

I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008  & SQL server 2008 R2.

The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”

After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.

 

Main Cause of this issue:

  • Problem may occur because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded. I don’t think I ever done this.

Possible resolution Solution 1:

  1. Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
  2. Back up the master database
  3. Click Run, type cmd.exe, and then click OK.
  4. At the command prompt, type one of the following commands, and then press ENTER
 osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server) 
Linked server error
  • Test to see that, you can run all distributed queries

Reference Links

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954#appliesto

http://technet.microsoft.com/en-us/library/aa215459%28SQL.80%29.aspx

 

Possible resolution Solution 2:

  1. Create the following stored procedure on your linked SQL Server 2000 instance
USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

2. GRANT EXECUTE the procedure to the Public in the MASTER database

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Reference Links

http://blogs.xtivia.com/home/-/blogs/21924

I actually tried both suggested resolutions, however only Solution 2 successfully worked for me

fixing link server problem for SQL Server 2000