Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11

Global temporary table

Dynamic SQL

sp_executesql (Transact-SQL)

sp_addlinkedserver

sp_attach_db

sp_addrole

sp_addrolemember

sp_droprolemember

sp_droprole

sp_addlogin

sp_password

Using Join Hints in Sql Server

Using Query Hints

Using Table Hints in Sql Server

Using Common Table Expressions

Using a Recursive Common Table Expression (CTE)

Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

sp_addlinkedserver in Sql Server

In this topic we will learn how to use sp_addlinkedserver in Sql Server to Creates a linked server with sp_addlinkedserver, this server can then execute distributed queries.

Create a linked server connection to another SQL Server 2005 instance.
Linked servers allow you to query external data sources from within a SQL Server instance. The external data source can either be a different SQL Server instance, or non-SQL Server data source such as Oracle, MS Access, DB2, or MS Excel.
To create the linked server, use the system stored procedure sp_addlinkedserver.

sp_addlinkedserver Syntax:

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

sp_addlinkedserver Arguments

Server

Local name used for the linked server. Instance names are also allowed, for example MYSERVER\SQL1.

product_name

Product name of the OLE DB data source. For SQL Server instances, the product_name is 'SQL Server'.

provider_name

This is the unique programmatic identifier for the OLE DB provider. When not specified, the provider name is the SQL Server data source. The explicit provider_name for SQL Server is SQLNCLI (for Microsoft SQL Native Client OLE DB Provider). MSDAORA is used for Oracle, OraOLEDB.Oracle for Oracle versions 8 and higher, Microsoft.Jet.OLEDB.4.0 for MS Access and MS Excel, DB2OLEDB for IBM DB2, and MSDASQL for an ODBC data source.

data_source

This is the data source as interpreted by the specified OLE DB provider. For SQL Server, this is the network name of the SQL Server (servername or servername\instancename). For Oracle, this is the SQL*Net alias. For MS Access and MS Excel, this is the full path and name of the file. For an ODBC data source, this is the system DSN name.

Location

The location as interpreted by the specified OLE DB provider.

provider_string

The connection string specific to the OLE DB provider. For an ODBC connection, this is the ODBC connection string. For MS Excel, this is Excel 5.0.

Catalog

The catalog definition varies based on the OLE DB provider implementation. For SQL Server, this is the optional database name. For DB2, this catalog is the name of the database.

In a network environment with multiple SQL Server instances, linked servers provide a convenient method for sharing SQL Server data without having to physically push or pull the data and replicate the schema.

The configurations used to connect to heterogeneous data sources vary, based on the OLE DB provider. If you’re just connecting to a different SQL Server instance, however, Microsoft makes it easy for you. In this recipe, I demonstrate creating a linked server connection to another SQL Server instance:

EXEC sp_addlinkedserver @server= 'JOEPROD',
@srvproduct= 'SQL Server'

You can also create linked servers to connect to SQL Server named instances, for example:

EXEC sp_addlinkedserver @server= 'JOEPROD\NODE2',
@srvproduct= 'SQL Server'

How sp_addlinkedserver Works

Adding a linked server to an external data source allows you to perform distributed queries (distributed queries are reviewed later in this chapter). When adding a SQL Server linked server to a SQL Server instance, whether it’s a default or named instance, Microsoft makes it easy for you by requiring just the server and product_name values.
What about the security method for connecting to the SQL Server instance? When creating a new linked server, the current user’s login security credentials (SQL or Windows) will be used to connect to the linked server. You can also create explicit remote login mapping for the linked server, which you’ll see discussed later on in the chapter.

Sponsored Links