OLE DB Provider for SQL Server
The Microsoft OLE DB Provider for SQL Server provides an OLE DB interface to Microsoft® SQL Server™ 2000 databases. Using the OLE DB Provider for SQL Server, 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 SQLOLEDB as provider_name, and the network name of the server running the remote instance of SQL Server as data_source.
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:
sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'NetSQLSrvr' - Map each local SQL Server login that needs access to the linked server to a SQL Server Authentication login on the linked server.
This example maps access for the local login Joe to the SQL Server Authentication login Visitor on the linked server named LinkedSQLSrvr.
sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'Joe', N'Visitor', N'VisitorPwd'
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 2000 before it can be referenced in a distributed query from a server running an instance of SQL Server 2000. For more information, see Upgrading the Catalog Stored Procedures (OLE DB).
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 using 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 Northwind database on the linked server:
sp_addlinkedserver @server = N'LinkServer',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'ServerNetName',
@catalog = N'Northwind'
GO
SELECT *
FROM LinkServer.Northwind..Shippers
This example defines both 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 nameto the network name faraway. */
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.
Transaction Considerations With Linked SQL Servers
The Microsoft OLE DB Provider for SQL Server does not support nested transactions. Therefore, XACT_ABORT should be set to ON for data modification operations inside implicit or explicit transactions and for data modification operations against distributed partitioned views.
Loopback connections to the same instance of SQL Server are not supported when inside an implicit or explicit transaction or distributed partitioned view.