I administer a local SQL Server 2005 database that was recently updated from SQL Server 2000. There is a nightly synchronization with a linked server (also SQL Server) that we have query privileges on certain views.
Since updating our server to 2005, we just recently started having issues connecting to the linked server. I'm trying to run an insert into my local database by querying the linked server:
insert into mytable select * from linkedserver.db.dbo.tablename where id not in (select id from mytable)
The error message we are receiving is:
"OLE DB provider 'SQLNCLI' for linked server 'linkedserver' returned data that does not match expected data length for column '[linkedserver].[dbname].[dbo].[tablename].columname'. The (maximum) expected data length is 5, while the returned data length is 6.
From researching this online, it appears that the size of [columnname] was changed and the view was not recompiled. However, the company maintaining the remote server I am linking to does not (and won't) recompile their views after these changes.
One thing that is interesting is if I run the same exact query in SQL Server 2000, the insert runs fine without any error.
Has anyone seen this error, and if so, how to get around it (besides asking the linked server owner to recompile their views). Or, anyone know how to write a query like this using OPENQUERY with a where clause?