SQL Server OpenRowSet corresponds to three database drivers

When you use the OPENROWSET function of the SQL Server database, you encounter three driving modes:

1. Msdasql Drive
SELECT TOP 10 *
From OPENROWSET (' msdasql ', ' Driver={sql Server}; server=xxx; Uid=xx; pwd=xxx; ', ' select * from AdventureWorks.HumanResources.Employee ')

2. SQLNCLI Drive
SELECT TOP 10 *
From OPENROWSET (' sqlncli ', ' server=xxx; Uid=xx; pwd=xxxxx; ', ' select * from AdventureWorks.HumanResources.Employee ')

3. SQLOLEDB Drive
SELECT TOP 10 *
From OpenRowSet (' SQLOLEDB ', ' xxx '; ' XX '; ' xxx ', AdventureWorks.HumanResources.Employee)

First look at the definition

MSDASQL: Microsoft OLE DB Provider for ODBC Drivers

The technology that allows applications built on OLE DB and ADO (which uses OLE DB internally) to access data sources through an ODBC driver.
MSDASQL is an OLE DB provider for connecting to ODBC (not a database).

SQLOLEDB: Microsoft OLE DB Provider for SQL Server, you can see that SQLOLEDB is connected to database SQL Server instead of the ODBC driver

SQLNCLI10 (SQLNCLI): SQL Server Native Client 10.0

Definition of SQL Server Native client:

SQL Server Native Client is an independent data Access application programming Interface (API) for OLE DB and ODBC introduced in SQL Server 2005. SQL Server Native Client combines the SQL OLE DB provider and SQL ODBC driver into a native dynamic-link library (DLL). To take advantage of new features in SQL Server 2005 and earlier, such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or new XML data types, existing applications that use ActiveX data Objects (ADO) should Use the SQL Server Native Client OLE DB provider as its data provider.

If you do not need to use any of the new features introduced in SQL Server 2005 and later versions, you do not need to use the SQL Server Native Client OLE DB provider; You can continue to use the current data provider (typically SQLOLEDB). If you want to enhance the functionality of an existing application and need to use the new features introduced in SQL Server 2005 and later versions, you should use the SQL Server Native Client OLE DB provider.

Attach OPENROWSET Syntax format

OPENROWSET({'provider_name' ,{'DataSource' ; 'user_id' ; 'Password'|'provider_string'},{   [Catalog.] [Schema. ]Object|'Query'}    | BULK'data_file' ,{FormatFile= 'Format_file_path'[<bulk_options>] | Single_blob | Single_clob | Single_nclob}})<bulk_options>:: = [,CODEPAGE={'ACP'|'Oem'|'RAW'|'Code_page'} ]    [,ErrorFile= 'file_name']   [,FirstRow=FIrst_row]    [,LastRow=LaSt_row]    [,Maxerrors=Maximum_errors]    [,Rows_per_batch= Rows_per_batch]

[,ORDER ({column [ASC | DESC]} [,...N]) [UNIQUE]

Reference: http://www.cnblogs.com/w-y-f/archive/2012/05/07/2488474.html

SQL Server OpenRowSet corresponds to three database drivers