Loading Data from a Linked Server - SQLNCLI errors

  • daren_sanborn

    Old Hand

    Points: 320

    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?

  • Jack Corbett

    SSC Guru

    Points: 184380

    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

    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

    This worked for me:

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

  • born2bongo

    SSCrazy

    Points: 2477

    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

    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


    D. Danial

  • daren_sanborn

    Old Hand

    Points: 320

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

  • danial_i

    Mr or Mrs. 500

    Points: 508

    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

    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).

  • danial_i

    Mr or Mrs. 500

    Points: 508

    Sorry, Can't help you with that! They need to recomile those objects.


    D. Danial

  • PGangwar

    SSC Enthusiast

    Points: 109

    It works for me!

    Thanks a million.

  • PGangwar

    SSC Enthusiast

    Points: 109

    It works for me!

    Thanks a lot.

  • vanu

    Newbie

    Points: 4

    It worked for me even just by Altering View statement (same definition) on source SQL instance.

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

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