Loading Data from a Linked Server - SQLNCLI errors

Question

Loading Data from a Linked Server - SQLNCLI errors

daren_sanborn

Old Hand

Points: 320
More actions
March 24, 2008 at 9:56 am

#128384

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?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

If the source data has a length of 6 shouldn't the destination have a length of 6? Is the actual data guaranteed to be only 5 characters? If so instead of using select * use a column list with a Convert(varchar(5), columnname) or Substring(ColumnName, 1,5) in your query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

daren_sanborn Old Hand Points: 320 More actions · Answer 2

Even if I change the destination field to match, I'm still getting the error. I think the error is saying that the results returned from the query don't match the length it was expecting when the view was compiled. I get the same error just trying to run a select against the remote table:

select * from linkedserver.dbname.dbo.viewname

--> gives me error message

select * from OPENQUERY(linkedserver,'select * from viewname')

--> works fine

Jaco Pieterse SSC Enthusiast Points: 104 More actions · Answer 3

This worked for me:

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

born2bongo SSCrazy Points: 2477 More actions · Answer 4

I'm having the same problem, and have been posting about it on the SSIS and general SQL Server forums.

I haven't got the option to run anything on the remote server, and I don't think Darren has either. I'm going to try the openquery solution as soon as I get in tomorrow, but I feel that might be something of a cop-out, so if you get any other info, Darren, or if anyone else has a theory, please post

danial_i Mr or Mrs. 500 Points: 508 More actions · Answer 5

Here's what you need to do to fix this problem:

1- Drop and recreate the view on the Linked Server (use SQL Server Management Studio)

2- Also, drop and recreate the view on the Local server.

Or, recompile both object through SQL Server 2005.

This works for me.

Good Luck!

D. Danial

daren_sanborn Old Hand Points: 320 More actions · Answer 6

Unfortunately I don't have permissions to do anything but select from the remote server.

danial_i Mr or Mrs. 500 Points: 508 More actions · Answer 7

You must have permission to do so! Or, ask the DBA that manages the remote server to do that, make sure you tell him to use SQL Management Studio (SQL 2005)..

D. Danial

daren_sanborn Old Hand Points: 320 More actions · Answer 8

I don't have permission. It's a third party real estate database that gives users the ability to query certain views that they have created for developers. They don't recompile on request (I've tried asking).