Three database drivers for sqlserverOpenRowSet

When using the OpenRowSet function of the SQL Server database, three driving methods are available: 1. msdasql driver SELECTTOP10 * FROMOPENROWSET (MSDASQL, DRIVER {SQLServer}; SERVERxxx; U, select * fromAdventureWorks. humanResources. employee) 2. SQLNCLI drive

When using the OpenRowSet function of the SQL Server database, three driving methods are available: 1. msdasql driver select top 10 * from openrowset ('msdasql ', 'driver = {SQL Server}; SERVER = xxx; UID = xx; PWD = xxx ;', 'select * from AdventureWorks. humanResources. employee ') 2. SQLNCLI drive

When using the SQL Server database's OpenRowSet function, you may encounter three driving methods:

1. MSDASQL driver
Select top 10 *
From openrowset ('msdasql ', 'driver = {SQL Server}; SERVER = xxx; UID = xx; PWD = xxx;', 'select * from AdventureWorks. HumanResources. Employee ')

2. SQLNCLI driver
Select top 10 *
From openrowset ('sqlncl', 'server = xxx; UID = xx; PWD = xxxxx; ', 'select * from AdventureWorks. HumanResources. Employee ')

3. SQLOLEDB driver
Select top 10 *
FROM OpenRowSet ('sqloledb', 'xxx'; 'xx'; 'xxx', AdventureWorks. HumanResources. Employee)

Let's take a look at the definition.

MSDASQL:Microsoft ole db Provider for ODBC Drivers

Technology that allows applications built on OLEDB and ADO (which use OLEDB internally) to access data sources through ODBC drivers.
MSDASQL is an OLEDB access interface used to connect to ODBC instead of a database.

SQLOLEDB:Microsoft ole db Provider for SQL Server, it can be seen that SQLOLEDB is connected to the database sqlserver, not 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) introduced in SQL Server 2005 for OLE DB and ODBC ). The SQL Server Native Client combines the SQL ole db access interface and the SQL ODBC driver into a local dynamic link library (DLL ). To take advantage of new features in SQL Server 2005 and later versions, such as multiple active result sets (MARS), query notifications, user-defined types (UDT), or new xml data types, existing applications that use ActiveX Data Objects (ADO) should use the SQL Server Native Client OLE DB access interface as its Data access interface.

If you do not need to use any new features introduced in SQL Server 2005 or later versions, you do not need to use the SQL Server Native Client OLE DB access interface; you can continue to use the current data access interface (usually SQLOLEDB ). To enhance the functions of existing applications and use new functions introduced in SQL Server 2005 and later versions, use the SQL Server Native Client OLE DB access interface.

Appendix: OPENROWSET syntax format

OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'    | 'provider_string' }       , {   [ catalog. ] [ schema. ] object        | 'query'      }    | BULK 'data_file' ,        { FORMATFILE = 'format_file_path' [ 
 
   ]       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }} 
  ) 
  
    ::=   [ 
   , CODEPAGE 
   = { 
   'ACP
   ' | 
   'OEM
   ' | 
   'RAW
   ' | 
   '
   code_page
   ' } ]    [ 
   , ERRORFILE 
   = 
   '
   file_name
   ' ]   [ 
   , FIRSTROW 
   = f
   irst_row ]    [ 
   , LASTROW 
   = la
   st_row ]    [ 
   , MAXERRORS 
   = m
   aximum_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