OLE DB Provider for SQL Server

The Microsoft OLE DB Provider for SQL Server provides an OLE DB interface to Microsoft® SQL Server™ databases. Using the OLE DB Provider for SQL Server, SQL Server distributed queries can query data in remote instances of SQL Server.

To set up a linked server to access a SQL Server database:

1. 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 SQL Server running on the server whose network name is NetSQLSrvr, execute:

   sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'NetSQLSrvr'

     

2. 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 an earlier version of SQL Server, 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 SQL Server version 6.5, the catalog stored procedures on the server must be upgraded to the 7.0 version of the procedures before it can be referenced in a distributed query from any server running SQL Server 7.0. For more information about upgrading the catalog stored procedures, see Upgrading the Catalog Stored Procedures (OLE DB).

When using four-part names, always specify the schema name. When referencing local tables, SQL Server uses defaults if an owner name is not specified. Not specifying a schema name in a distributed query, however, prevents OLE DB from finding tables. 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