SQL Server Native Client OLE DB Provider
The Microsoft SQL Server Native Client OLE DB Provider provides an OLE DB interface to Microsoft SQL Server databases. By using the SQL Server Native Client OLE DB Provider, SQL Server distributed queries can query data in remote instances of SQL Server.
To create a linked server to access a SQL Server database
Execute sp_addlinkedserver to create the linked server, specifying the network name of the server running the remote instance of SQL Server as data_source. For SQL Server linked servers, specifying the provider (SQLNCLI) is optional. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)
For example, to create a linked server named LinkSQLSrvr that operates against the instance of SQL Server running on the server whose network name is NetSQLSrvr, execute either of the following:
sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLNCLI', N'NetSQLSrvr' sp_addlinkedserver N'LinkSQLSrvr', ' ', ' ', N'NetSQLSrvr'Alternatively, you can define a SQL Server linked server as its network name, as shown in the following example:
sp_addlinkedserver N'NetSQLSrvr'
When distributed queries are executed against a server running an instance of SQL Server 2000, we recommend that you apply SQL Server 2000 Service Pack 4 (SP4). If SP4 has not been applied, you should run the Instcat.sql script that is included with SP4 to upgrade the catalog stored procedures.
When distributed queries are executed against a server running SQL Server version 7.0 or earlier, the catalog stored procedures on the earlier version must be upgraded to ensure the proper operation of the distributed queries. For example, if a server is running an instance of SQL Server 7.0, the catalog stored procedures on the server must be upgraded to SQL Server 2008 before it can be referenced in a distributed query from a server running an instance of SQL Server 2008.
When a remote SQL Server table is updated, the local server or client will not receive any result sets or messages resulting from triggers fired for that update.
When you use four-part names, always specify the schema name. Not specifying a schema name in a distributed query prevents OLE DB from finding tables. When referencing local tables, SQL Server uses defaults if an owner name is not specified. The following SELECT statement would generate a 7314 error, even if the linked server login mapped to a dbo user in the AdventureWorks2008R2 database on the linked server:
sp_addlinkedserver @server = N'LinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'ServerNetName',
@catalog = N'AdventureWorks2008R2'
GO
SELECT *
FROM LinkServer.AdventureWorks2008R2.dbo.Vendor
The following example defines a linked server and a remote server that both access the same computer whose network name is othersite. The linked server definition uses the same name as the network name of the remote server; the remote server definition uses another name.
/* Create a linked server definition to othersite. */
EXEC sp_addlinkedserver 'othersite', N'SQL Server'
/* Create a remote server definition using a
fictitious name. */
EXEC sp_addserver 'RPCothersite'
/* Set the fictitious name to the network name far away. */
EXEC sp_setnetname 'RPCothersite', 'othersite'
These names can be referenced in distributed queries or remote procedure calls.
/* A distributed query referencing othersite. */
SELECT *
FROM othersite.Northwind.dbo.Employees
/* A remote procedure call to the same server. */
EXEC RPCothersite.master.dbo.sp_who
/* Distributed queries can be used to execute
stored procedures on the other server. */
EXEC othersite.master.dbo.sp_who
There are differences in the login mapping mechanism between stored procedures executed through linked server and stored procedures executed through remote servers. For more information, see Linking Servers.
Transaction Considerations with Distributed Queries
The Microsoft SQL Server Native Client OLE DB Provider does not support nested transactions. Therefore, XACT_ABORT should be set to ON for data modification operations inside a transaction involving multiple Transact-SQL statements and for data modification operations against distributed partitioned views.