Creating Linked Servers in SQL Server 2012 to SQLServer 2000

 Linked server is Equivalent to Database link in Oracle database.

It is easy to create linked Server in SQL Server 2012 to any version till SQL Server 2008 using SQL Server management studio.

Challenges are encountered while creating linked server for a DB from SQL2012 to SQL2000

While creating a Linked Server, it throws following error reason being that SQL Server 2012 does not support Native Client 11 to connect to SQL 2000.

Resolution Steps:

• Downloaded and installed the SQL Native Client 10.
• Install the SQL Native client 10 and create Linked Server as below.

EXEC sp_addlinkedserver @server = N'TESTLNSVR',

                        @srvproduct=N'MSSQL',

                        @provider=N'SQLNCLI10',

                        @provstr=N'PROVIDER=SQLOLEDB;SERVER=xx.xx.xx.xx'

• This will create the  link server  but while accessing it could throw error as:

To rectify the above error , following steps should be followed:

Define ODBC data source using SQL native client 10 in ODBC tool in Windows server.

Once the ODBC Data source is completed ,perform the connectivity test and it should be successful.

1)     Now create the linked server using script.

EXEC sp_addlinkedserver @server = N'TESTLNK_SVR',

                        @srvproduct=N'',

                        @provider=N'MSDASQL',

                        @provstr=N'DRIVER={SQL Server};SERVER=xx.xx.xx.xx;UID=test;PWD=Test123;',

                        @catalog=N'DBNAME'

Server:  TESTLNK_SVR is the link server name through which DB on target server is accessed.

Provider: MSDASQL is an OLEDB provider that connects to ODBC, instead of a database.

Provider string: SQL Server Is the OLE DB provider-specific connection string that identifies a unique data source.

Server in Provstr: Server name where the SQL Server 2000 instance is setup

Catalog: DBName is the database name that we are trying to access in target SQL Server instance.

Once the Linked Server is created, it can be accessed using below SQL.

SELECT * FROM OPENQUERY(TESTLNK_SVR,’SELECT * FROM TABLE’)

Author:

Mohammed Fazulullah

Kovaion Consulting-PeopleSoft Practice

Email: mohammed.fazulullah@kovaion.com