Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Introduction To Linked Servers

By Krishna Sonti, (first published: 2004/06/02)

Introduction

What are Linked Servers?

A Linked Server is a Link to an external (remote) data source. The remote data source can be Oracle, Access, Excel or any other data source that uses an OLE DB provider.

What are Remote Servers?

  • Remote servers do not use OLE DB providers
  • A remote server configuration allows a client connected to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without establishing another connection
  • Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.
  • Use Linked Servers instead of Remote Servers. Remote servers are for backward compatibility only.

Note: Linked servers can run remote stored procedures for SQL Server and cannot run remote stored procedures for Oracle.

Adding Linked Servers

Syntax

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

Please refer BOL for syntax and more info

Example: To add Oracle as a linked server to SQL Server:

This example creates a linked server named OraLinkServ that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is OraProduction.

USE master
GO
EXEC sp_addlinkedserver

   @server = ‘OraLinkServ',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'OraProduction'
GO

Adding Linked Server Login

Sp_addlinkedsvrlogin

Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.

Syntax

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    
[ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    
[ , [ @rmtpassword = ] 'rmtpassword' ]

Please refer BOL for syntax and more info

Querying Linked Server

The following are the various methods of querying different linked servers.

LinkedServerName.Dbname.Owner.ObjectName

Select * from LinkSqlServ.Northwind.dbo.Employees

Select * from OraLinkServ..SCOTT.Stores 

Select * from LinkMdb...customers

OpenQuery: - Executes the specified pass-through query on the given linked server, which is an OLE DB data source.

Syntax:

OpenQuery ( linked_server, 'query' )

Examples:

SELECT * FROM OPENQUERY(OraLinkServ, 'SELECT Name, Title FROM Scott.Titles') 

INSERT OPENQUERY(LinkSqlServ, ‘select * from pubs.dbo.jobs’) values (15, ‘Technical Editor’, 100, 300)

OpenRowSet: - This method is an adhoc method of connecting and accessing remote data using OLE DB. It creates linked server on the fly.

Syntax

OPENROWSET ( 'provider_name'
    ,
{ 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog. ] [ schema. ] object
        | 'query' }
    )

Removing Linked Server and its Logins

Sp_dropserver - Removes a server from the list of known remote and linked servers on the local SQL Server.

Sp_droplinkedsvrlogin: Removes an existing mapping between a login on the local server running SQL Server and a login on the linked server.

Obtaining Meta data

Please refer BOL for complete syntax.

Sp_tables_ex: Returns table information about the tables from the specified linked server.

sp_columns_ex: Returns the column information, one row per column, for the given linked server table(s). sp_columns_ex returns column information only for the given column if column is specified.

sp_table_privileges_ex: Returns privilege information about the specified table from the specified linked server.

sp_column_privileges_ex: Returns column privileges for the specified table on the specified linked server.

Sp_linkedservers:Returns the list of linked servers defined in the local server.

Sp_helpserver: Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the server's network name, the server's replication status, the server's identification number, collation name, and time-out values for connecting to, or queries against, linked servers.

Sysservers: Contains one row for each server that SQL Server can access as an OLE DB data source. This table is stored in the master database.

Sysoledbusers: Contains one row for each user and password mapping for the specified linked server. This table is stored in the master database.

xp_enum_oledb_providers: Provides information about OLEDB providers.

Sp_serveroption: Sets server options for remote servers and linked servers.

Sp_serveroption has been enhanced with two new options, use remote collation and collation name, that support collations in linked servers.

Sp_setnetname: Sets the network names in sysservers to their actual network computer names for remote instances of  SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing invalid SQL Server identifiers.

Some Useful Knowledge Base Articles for Linked Servers

Q280106: HOWTO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server

Q203638: HOWTO: Return Information About SQL Server Linked Servers

Q270119: PRB: 7357/7320 Error While Performing UPDATE, INSERT, or DELETE on Remote Table Using OpenQuery Via Linked Server

Q306212: INF: Troubleshooting Linked Server Error 7391

Q329332: PRB: You Receive Error 7391 When You Run a Distributed Transaction Against a Linked Server

Total article views: 46955 | Views in the last 30 days: 53
 
Related Articles
FORUM

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

FORUM

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - C...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones