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