OLE DB Provider for ODBC

The OLE DB Provider for ODBC provides an OLE DB interface to ODBC data sources. Using the OLE DB Provider for ODBC, Microsoft® SQL Server™ distributed queries can access all ODBC data.

To set up a linked server to access an ODBC database when using an ODBC data source:

1. Create a System data source on the computer on which SQL Server is installed.
2. Execute sp_addlinkedserver to create the linked server, specifying MSDASQL or NULL as provider_name, and the name of an ODBC system data source as data_source.

   ODBC user data sources cannot be used for distributed queries because SQL Server runs as a service on Microsoft Windows NT®, and services do not always have access to user data sources. For example, a system data source with a name of SystemDSN references a server that is running SQL Server and that has pubs as the default database:

   sp_addlinkedserver 'SQLPubs', ' ', 'MSDASQL', 'SystemDSN'

     

Linked servers can use the OLE DB Provider for ODBC without using an ODBC data source. The linked server is defined in one step. All the information the OLE DB Provider for ODBC needs to locate an ODBC driver and connect to a source of ODBC data must be defined in provider_string.

This example creates a linked server named SQLPubs on the pubs database of the SQL Server named SalesSvr through ODBC by specifying provider_string.

sp_addlinkedserver 'SQLPubs', ' ', 'MSDASQL', NULL, NULL,

'Driver={SQL Server};Database=pubs;Server=SalesSvr;UID=sa;PWD=;'

These restrictions exist if a linked server is defined using the OLE DB Provider for ODBC and accesses a SQL Server database:

• Tables cannot be referenced if they have one or more timestamp columns.
• Tables cannot be referenced if they have nullable char, varchar, nchar, nvarchar, binary, or varbinary columns and the ANSI_PADDING option was set OFF when the table was created.