Systems Engineering and RDBMS

Linked Servers in SQL Server

Posted by decipherinfosys on July 16, 2007

Linked servers are a way to link to different OLEDB compatible data sources from within SQL Server. One can create these links to either other SQL Server instances or even to other heterogeneous sources like Oracle, DB2 LUW, text files, MS Excel etc. This is akin to database links in Oracle which we had covered in one of our previous blog post.

A linked server has two major components:

An OLE DB data source: It can be any database (Oracle, DB2, Informix, MySQL, Access etc.) which can be accessed through an OLE DB provider or it can be a file in different formats (Excel spreadsheet, Text file) for which an OLE DB provider exists.

An OLE DB provider: This is the DLL that interacts with one of the above mentioned data sources. If you are using SQL Server 2000, then you will make use of either the ODBC driver or the OLE DB providers that are available with that version. In SQL Server 2005, you would use SQL Native Client (SQLNCLI) when making OLE DB connections to other SQL Server 2005 instances.

So, essentially a linked server is used to connect an instance of SQL Server to other data sources (Remote servers) which uses an OLEDB provider.  Linked Servers can be managed using Enterprise Manager or Management Studio but we will use T-SQL commands to demonstrate the use of linked servers. There are certain system stored procedures which we can use to create, maintain and to review the metadata related to linked servers. When you use the GUI, these are the ones that get used for creating the linked servers. It is always good to know the details rather than just using the GUI.

The first step in this case is to create a linked server using sp_addlinkedserver system stored procedure. Following is the complete syntax from BOL, followed by an example of creating linked server for Oracle data source.  For a complete description of the syntax, please refer to BOL.

sp_addlinkedserver [ @server = ] ‘server’
[ , [ @srvproduct = ] ‘product_name’ ]
[ , [ @provider = ] ‘provider_name’ ]
[ , [ @datasrc = ] ‘data_source’ ]
[ , [ @location = ] ‘location’ ]
[ , [ @provstr = ] ‘provider_string’ ]
[ , [ @catalog = ] ‘catalog’ ]

Using this system stored procedure, we can now create a linked server with Oracle:

EXEC sp_addlinkedserver
@server=’SQL_TO_ORA’, /* linked server name */
@srvproduct=’ORACLE’, /* Product name of data source */
@provider=’MSDAORA’, /* OLE DB provider */
@datasrc=’ORCL’ /* instance name if oracle*/
GO

In the above example, we are using Microsoft’s OLE DB provider for Oracle (MSDAORA). We can also use Oracle’s OLE DB provider (ORAOLEDB) as well by changing @provider value.  Once the linked server is created successfully, a new record is created in sysservers table in master database which can be accessed using following query. This system table contains one row for each server that SQL Server can access as an OLE DB data source.

SELECT * FROM sys.syservers — (SQLServer 2005)
OR
SELECT * FROM master..sysservers — (SQLServer 2000)

Next step is to create mapping between local login and remote login on the linked server. For this, we will have to use the sp_addlinkedsrvlogin system stored procedure. The syntax from BOL is:

sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname’
[ , [ @useself = ] ‘useself’ ]
[ , [ @locallogin = ] ‘locallogin’ ]
[ , [ @rmtuser = ] ‘rmtuser’ ]
[ , [ @rmtpassword = ] ‘rmtpassword’ ]

This procedure creates mapping between logins on the local instance and remote login on the linked server:

• First parameter is Linked server name to which the login mapping applies.
• Second Parameter indicates whether we want to use a local login to connect to the linked server or not. If this value is set to true, local sql server authenticated logins will be used to connect to the remote server. If false, then remote user and remote password will be used to connect to remote server.
• Third parameter indicates either specific login name or null. If value is null means, all local logins can connect to remote server.
• Fourth and fifth parameters are remote user name and remote password to connect to remote server. For our example, we are using Oracle and the remote user name and password should be oracle username and password to connect to database.

Whenever a login mapping is created, a new record is created into sysoledbusers table in master database. Following is the syntax to create login mapping for linked server created for Oracle data source.

EXEC sp_addlinkedsrvlogin
‘SQL_TO_ORA’, /** remote server name **/
false, /** True or False **/
‘sa’, /** local login Name **/
‘DECIPHER’, /** Remote userName **/
‘DECIPHER’ /** Remote password **/
GO

In above example, DECIPHER/DECIPHER is the remote username/password to connect to the Oracle schema. ‘sa’ is local login we have used to connect to the SQL Server instance. Normally it will be a different login since ‘sa’ is sysadmin and regular database users will not have access to this login.

Now that we have created a linked server and created associated login mapping, how can we access the linked server? There are various ways to handle data using linked servers.

Using four part naming convention (linked_server_name.catalog.schema.object_name):

We can access the data from a linked server using four part naming convention. In case of Oracle, we don’t have catalog so it is linked_server_name..UserName.Table_name. For our example following is the syntax. Query will return data from the sample table owned by Oracle user DECIPHER via linked server SQL_TO_ORA.

SELECT count(*) FROM sql_to_ora..DECIPHER.SAMPLE
GO

Using OPENQUERY function:

OPENQUERY function takes two arguments linked_Server_Name and query text as shown below.

SELECT * From openquery
(SQL_TO_ORA,’SELECT * FROM USER_TABLES WHERE ROWNUM < 3′)
GO

Using OPENROWSET function:

This is an impromptu method of accessing data from a remote server. It is like creating linked server on demand without actually storing it’s definition. The parameters used in creating a linked server definition are used directly with OPENROWSET. Instead of creating linked server, we define all the connection parameters in query it self. For complete syntax, refer to BOL. Following is an example of how to access Oracle database using OPENROWSET function.

SELECT * FROM OPENROWSET
(‘MSDAORA’, — Provider Name
‘ORCL’;’DECIPHER’;’DECIPHER’, — Remote db connection information
‘SELECT * FROM USER_TABLES WHERE ROWNUM < 3’) — Actual query
GO

In the above query, ORCL is a SQL*Net alias (tnsnames.ora entry) for the Oracle database. DECIPHER/DECIPHER is userid and password to connect to schema owned by user. This method can be used for ad hoc occurrences. If there is a need of frequent access to remote oracle server, it is better to create linked server.

Behavior of OPENROWSET is determined by two parameters:

• DisallowAdhocAccess registry option should be set to 0 for specific OLEDB provider entry. For our case, registry entry is as shown. (HKEY_LOCAL_MACHINE/ SOFTWARE/ MICROSOFT/ MSSQLSERVER/ PROVIDERS/ SQLOLEDB)
• ‘Ad Hoc Distributed Queries’ advance option should be enabled using sp_configure system stored procedure for SQL Server instance.

If the above two options are not set, then the default behavior prevents the adhoc access to the remote server.

We can also drop the linked server using system stored procedure sp_dropserver. Following is the syntax to drop servers including removal of mapping of its associated logins. Logins can be removed separately using sp_droplinkedsrvlogin stored procedure also.

EXEC sp_dropserver ‘SQL_TO_ORA’,’droplogins’

sp_helpserver and sp_helplinkedsrvlogin are two procedures available for checking the existing liked servers and its login mappings.

Once the linked servers are created, one can then use them in queries including join conditions, example:

Select *
From tableA as X
Inner join sql_to_ora..DECIPHER.SAMPLE as Y
On X.COL1 = Y.COL2

There are a lot of things that need to be considered when using linked servers besides just configuration of the linked server and the security. That is especially true if you are going to use it for accessing heterogeneous data sources (sources other than SQL Server). Data-Types are different across RDBMS or when you access text/Excel files, the precision for datetime columns, the rounding mechanisms, the collation etc. In addition, what if that SAMPLE table in the Oracle schema has 10 million rows in it and you want to filter the records and get only say 10 records out of it. Depending upon the way you write your linked server query and which OLE DB provider you are using, the filter could be getting executed at the source or the destination. In future blog posts, we will cover those details as well.

5 Responses to “Linked Servers in SQL Server”

  1. […] Linked Servers in SQL Server […]

  2. […] a date fieldDifference between UTF8 and AL32UTF8 character sets in OracleTemporary tables – OracleLinked Servers in SQL Server « Virtual Columns in […]

  3. […] Top Posts Temporary Tables – MS SQL ServerFunctional difference between “NOT IN” vs “NOT EXISTS” clauses Update data in one table with data from another tableDisable/Enable Foreign Key and Check constraints in SQL ServerUpdate text/ntext or varchar(max) data-type columns in SQL ServerRunning Parameterized SQL Commands Using the Execute SQL Task – ICOALESCE(), ISNULL(), NVL()SSIS: Exporting data to a text file using a packageDifference between UTF8 and AL32UTF8 character sets in OracleLinked Servers in SQL Server […]

  4. […] alternative is to use either linked servers or just use the openrowset or opendatasource queries in the case of SQL Server.  In the case of […]

  5. […] part – (d).  Since it resides in a database on a separate instance, we can first create a linked server to it first and then either reference it using a couple of ways as was shown in that blog post on […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: