Linked Server from SQL 2016 to SQL 2005

• datsun

  SSCrazy

  Points: 2397

  More actions

  September 8, 2019 at 11:21 am

  #3678326

  Hello,

  I have created this new linked server on SQL 2016 to connect to SQL 2005 and the openQuery select gives me this error.

  The linked server is created with: @provider=N'SQLNCLI'

  The remote query is a View.

  OLE DB provider "SQLNCLI11" for linked server "SERVERB" returned message "Deferred prepare could not be completed.".

  Msg 8180, Level 16, State 1, Line 10

  Statement(s) could not be prepared.

  Msg 208, Level 16, State 1, Line 10

  Invalid object name 'SERVERB_databaseName.dbo.vw_users_view'.

  How to resolve this?

  Many thanks,

  Vinay

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  September 8, 2019 at 3:30 pm

  #3678341

  SQL 2005 its not supported by SQL Client 11 https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/support-policies-for-sql-server-native-client?view=sql-server-2016 for supported versions.

   

  you will need to use an older driver (2008 R2 works) https://www.microsoft.com/en-US/download/details.aspx?id=30440 or 2012 https://www.microsoft.com/en-us/download/details.aspx?id=29065

  Note sure how you make it available on the server though as a linked server.

  Other alternative is to use ODBC instead of the sql native clien

   

• datsun

  SSCrazy

  Points: 2397

  More actions

  September 8, 2019 at 4:19 pm

  #3678345

  Fredrico,

  I think I resolved this using this way. Still need to test different sql calls using this method, but it seems to be working for my main call.

  EXEC master.dbo.sp_addlinkedserver @server = N'SERVERB', @srvproduct=N'SQL Server'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVERB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

  Thanks for your help too.

  Vinay