OLE DB Provider for ODBC (Database Engine)
The OLE DB Provider for ODBC provides an OLE DB interface to ODBC data sources. By using the OLE DB Provider for ODBC, distributed queries can access all ODBC data.
Note
For SQL Server 2000 data sources, use the SQL Server Native Client OLE DB Provider. Do not use the OLE DB Provider for ODBC.
To create a linked server to access an ODBC database when you are using an ODBC data source:
Create a System data source on the computer on which SQL Server is installed.
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, and services do not always have access to user data sources. For example, a system data source that has a name of SystemDSN references a server that is running SQL Server and that has AdventureWorks2008R2 as the default database:
sp_addlinkedserver 'SQLAdWorks', ' ', '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 requires to locate an ODBC driver and connect to a source of ODBC data must be defined in provider_string.
The following restrictions exist if a linked server is defined by 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.