Link to home
Start Free TrialLog in
Avatar of ezkhan
ezkhanFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Linked server from SQL Server 2008 R2 64 bit to SQL Server 2000 32 bit is failing on updated statement

linked server problem when I run update statement from SQL Server 2008 R2 64 bit to SQL Server 2000 32 bit. It returns error given below;

Msg 682, Level 22, State 148, Line 1 Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

I have tried every thing and have created sp_tables_info_rowset_64 stored procedure in master database of SQL Server 2000. It enabled select and insert into the SQL Server 2000 database tables but when I try to Update records on SQL Server 2000 database tables above error comes up.

Any comments and suggestions are appreciated.

Thanks.
Avatar of lcohan
lcohan
Flag of Canada image

Can you try to copy your SQL 2008 record set data from SQL 2008 to the SQL 2000 in a "staging" table by using INSERT then run the UPDATE at SQL 2000 side in a stored proc by using both these tables - staging and destination - that are in SQL 2000?
Avatar of ezkhan

ASKER

Hi,

I have figured out the solution. To update or insert statement with multiple joins involved in it across linked servers from SQL Server 2008 R2 64 bit to SQL Server 2000 32 bit requires the installation of service pack 3 or 4 on SQL Server 2000 machine. Once it is installed then under windows authentication or under SQL Server authentication with administrative rights user needs to run INSTCAT.SQL script.

For a default instance
osql -E -S <LinkedServerName> -i <Location>\instcat.sql



For a named instance
osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql


Microsoft Reference:
http://support.microsoft.com/kb/906954/

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of ezkhan
ezkhan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ezkhan

ASKER

Why 0 points are being awarded????