ezkhan
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.
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.
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?
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>\<Instan ceName> -i <Location>\instcat.sql
Microsoft Reference:
http://support.microsoft.com/kb/906954/
Thanks.
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>\<Instan
Microsoft Reference:
http://support.microsoft.com/kb/906954/
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Why 0 points are being awarded????
Creating Linked Server from SQL 2008 to SQL 2000
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/0782364d-dcb0-4c45-8343-5bfc4406ead4/
http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx