Microsoft – SQL Server – Linked Server – SQL Server v2012 -to- SQL Server v2000 (SP3)

Introduction

Got in a bit of trouble trying to prepare\use a Linked Server connection from MS SQL Server 2012 SP1 to MS SQL Server v2000 SP3.

Background

BTW, same Linked Server Connection works from MS SQL Server 2008/R2 (SP1) to MS SQL Server v2000 (SP3).

Scenario

Here is how our Linked Server Connections were attempted.

Scenario – Microsoft OLE DB Provider for SQL Server

When we tried using “Microsoft OLE DB Provider for SQL Server”:

SQL Server Linked Connection – GUI

NewLinkedServer

SQL Server Linked Connection – SQL Script


USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_OLEDBMS'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLOLEDB'
		, @datasrc=N'DBLABJ'

GO
EXEC master.dbo.sp_serveroption 
           @server=N'DBLABJ_OLEDBMS'
         , @optname=N'collation compatible'
         , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
            @server=N'DBLABJ_OLEDBMS'
          , @optname=N'data access'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
             @server=N'DBLABJ_OLEDBMS'
           , @optname=N'rpc'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
              @server=N'DBLABJ_OLEDBMS'
            , @optname=N'rpc out'
            , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                @server=N'DBLABJ_OLEDBMS'
              , @optname=N'lazy schema validation'
              , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
                 @server=N'DBLABJ_OLEDBMS'
               , @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption 
                 @server=N'DBLABJ_OLEDBMS'
               , @optname=N'use remote collation'
               , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                  @server=N'DBLABJ_OLEDBMS'
                , @optname=N'remote proc transaction promotion'
                , @optvalue=N'false'
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
                   @rmtsrvname = N'DBLABJ_OLEDBMS'
                 , @locallogin = NULL 
                 , @useself = N'True'
GO

Error

Error Message Text:

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or 
earlier versions.

OLE DB provider "SQLNCLI11" for linked server "DBLABJ_OLEDBMS" returned message 
"Client unable to establish connection". (Microsoft SQL Server, Error: 22)</code>
<code>Error Message Screen:

GUI:
SQLServerNativeClientDoesNotSupport

Scenario – SQL Server Native Client 11.0

When we tried using “SQL Server Native Client 11.0”:

SQL Server Linked Connection – GUI

SQLServerNativeClient-v11


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_NC_11'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLNCLI11'
		, @datasrc=N'DBLABJ'
GO

EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
         , @optname=N'collation compatible'
	 , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
         , @optname=N'data access'
	 , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
        , @optname=N'rpc'
	, @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption 
                        @server=N'DBLABJ_NC_11'
                       , @optname=N'rpc out'
			, @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                       @server=N'DBLABJ_NC_11'
                     , @optname=N'collation name'
		     , @optvalue=null
GO

EXEC master.dbo.sp_serveroption 
                       @server=N'DBLABJ_NC_11'
                     , @optname=N'lazy schema validation'
		     , @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption 
                @server=N'DBLABJ_NC_11'
              , @optname=N'use remote collation'
	      , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
          @server=N'DBLABJ_NC_11'
         , @optname=N'remote proc transaction promotion'
	 , @optvalue=N'false'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
			  @rmtsrvname = N'DBLABJ_NC_11'
			, @locallogin = NULL 
			, @useself = N'True'
GO

Image:

SQLServerNativeClient-v11-error

Textual:

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.

OLE DB provider “SQLNCLI11” for linked server “DBLABJ_NC_11” returned message “Client unable to establish connection”. (Microsoft SQL Server, Error: 22)

Scenario – SQL Server Native Client 10.0

When we tried using “SQL Server Native Client 10.0”:

SQL Server Linked Connection – GUI

SQLServerNativeClient_v10


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_NC_10'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLNCLI10'
		, @datasrc=N'DBLABJ'

GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
          , @optname=N'collation compatible'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
          , @optname=N'data access'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'rpc'
         , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
        , @optname=N'rpc out'
        , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'collation name'
         , @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'lazy schema validation'
         , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
        , @optname=N'query timeout'
        , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
       , @optname=N'use remote collation'
       , @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
			, @optname=N'remote proc transaction promotion'
			, @optvalue=N'false'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
			  @rmtsrvname = N'DBLABJ_NC_10'
			, @locallogin = NULL 
			, @useself = N'True'
GO

Image:

CannotObtainTheRequiredInterface
Textual:


The OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10" reported an error. Access denied.

Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10". (Microsoft SQL Server, Error: 7399)

Textual – 4 part name query – Error 7339/ Error 7330

Query


select *
from DBLABJ_NC_10.tempdb.dbo.sysobjects

 


Error Message

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10" reported an error. 
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10".

Textual - Openquery - Error 7339/ Error 7330

Query


select * from openquery(DBLABJ_NC_10, 'select * from tempdb.dbo.sysobjects')  

Message
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI10" for linked server"DBLABJ_NC_10" reported an error. The provider reported an unexpected catastrophic failure. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10".

Fixes

Fixes - ODBC

  • Using ODBC Administrator, create an ODBC Connection
  • Using SQL Server Manager Studio, create Linked Server (LS) Connection

ODBC Administrator

Create a New Data Source

CreateANewDataSource

How should SQL Server Authenticate

CreateANewDataSource_Authentication

Change Database Name & Connection ANSI' settings

CreateANewDataSource_ChangeDefaultDatabase

Language

CreateANewDataSource_Language

Confirm Configuration Settings

CreateANewDataSource_Confirmation

Validation

CreateANewDataSource_Validation

Linked Server

Here is the Linked Server

DBLABJ_NC_10_ODBC

Using ODBC work!

Fixes - SQL Server Patches

This is a possible fix; but I have yet to try it -- Apply Microsoft SQL Server 2000 /Service Pack 4 to upgrade SQL Server 20003 from SP3 to SP4.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s