Microsoft OLE DB Provider for SQL Server
Applies to: Access 2013, Office 2013
The Microsoft OLE DB Provider for SQL Server, SQLOLEDB, allows ADO to access Microsoft SQL Server.
Connection String Parameters
To connect to this provider, set the Provider argument to the ConnectionString property to:
SQLOLEDB
This value can also be set or read using the Provider property.
Typical Connection String
A typical connection string for this provider is:
"Provider=SQLOLEDB;Data Source=serverName;"
Initial Catalog=databaseName;
User ID=userName;Password=userPassword;"
The string consists of these keywords:
Keyword |
Description |
|---|---|
Provider |
Specifies the OLE DB Provider for SQL Server. |
Data Source or Server |
Specifies the name of a server. |
Initial Catalog or Database |
Specifies the name of a database on the server. |
User ID or uid |
Specifies the user name (for SQL Server Authentication). |
Password or pwd |
Specifies the user password (for SQL Server Authentication). |
Provider-Specific Connection Parameters
The provider supports several provider-specific connection parameters in addition to those defined by ADO. As with the ADO connection properties, these provider-specific properties can be set via the Properties collection of a Connection or can be set as part of the ConnectionString.
Parameter |
Description |
|---|---|
Trusted_Connection |
Indicates the user authentication mode. This can be set to Yes or No. The default value is No. If this property is set to Yes, then SQLOLEDB uses Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database specified by the Location and Datasource property values. If this property is set to No, then SQLOLEDB uses Mixed Mode to authorize user access to the SQL Server database. The SQL Server login and password are specified in the User Id and Password properties. |
Current Language |
Indicates a SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on the SQL Server, otherwise opening the connection will fail. |
Network Address |
Indicates the network address of the SQL Server specified by the Location property. |
Network Library |
Indicates the name of the network library (dynamic-link libraries) used to communicate with the SQL Server. The name should not include the path or the .dll file name extension. The default is provided by the SQL Server client configuration. |
Use Procedure for Prepare |
Determines whether SQL Server creates temporary stored procedures when Commands are prepared (by the Prepared property). |
Auto Translate |
Indicates whether OEM/ANSI characters are converted. This property can be set to True or False. The default value is True. If this property is set to True, then SQLOLEDB performs OEM/ANSI character conversion when multi-byte character strings are retrieved from, or sent to, the SQL Server. If this property is set to False, then SQLOLEDB does not perform OEM/ANSI character conversion on multi-byte character string data. |
Packet Size |
Indicates a network packet size in bytes. The packet size property value must be between 512 and 32767. The default SQLOLEDB network packet size is 4096. |
Application Name |
Indicates the client application name. |
Workstation ID |
A string identifying the workstation. |
Command Object Usage
SQLOLEDB accepts an amalgam of ODBC, ANSI, and SQL Server-specific Transact-SQL as valid syntax. For example, the following SQL statement uses an ODBC SQL escape sequence to specify the LCASE string function:
SELECT customerid={fn LCASE(CustomerID)} FROM Customers
LCASE returns a character string, converting all uppercase characters to their lowercase equivalents. The ANSI SQL string function LOWER performs the same operation, so the following SQL statement is an ANSI equivalent to the ODBC statement presented above:
SELECT customerid=LOWER(CustomerID) FROM Customers
SQLOLEDB successfully processes either form of the statement when specified as text for a command.
Stored Procedures
When executing a SQL Server stored procedure using a SQLOLEDB command, use the ODBC procedure call escape sequence in the command text. SQLOLEDB then uses the remote procedure call mechanism of SQL Server to optimize command processing. For example, the following ODBC SQL statement is the preferred command text over the Transact-SQL form:
ODBC SQL
{call SalesByCategory('Produce', '1995')}
Transact-SQL
EXECUTE SalesByCategory 'Produce', '1995'
Recordset Behavior
SQLOLEDB cannot use SQL Server cursors to support the multiple-result generated by many commands. If a consumer requests a recordset requiring SQL Server cursor support, an error occurs if the command text used generates more than a single recordset as its result.
Scrollable SQLOLEDB recordsets are supported by SQL Server cursors. SQL Server imposes limitations on cursors that are sensitive to changes made by other users of the database. Specifically, the rows in some cursors cannot be ordered, and attempting to create a recordset using a command containing an SQL ORDER BY clause can fail.
Dynamic Properties
The Microsoft OLE DB Provider for SQL Server inserts several dynamic properties into the Properties collection of the unopened Connection, Recordset, and Command objects.
The following tables are a cross-index of the ADO and OLE DB names for each dynamic property. The OLE DB Programmer's Reference refers to an ADO property name by the term "Description." You can find more information about these properties in the OLE DB Programmer's Reference. Search for the OLE DB property name in the Index or see Appendix C: OLE DB Properties.
Connection Dynamic Properties
The following properties are added to the Connection object's Properties collection.
ADO Property Name |
OLE DB Property Name |
|---|---|
Active Sessions |
DBPROP_ACTIVESESSIONS |
Asynchable Abort |
DBPROP_ASYNCTXNABORT |
Asynchable Commit |
DBPROP_ASYNCTNXCOMMIT |
Autocommit Isolation Levels |
DBPROP_SESS_AUTOCOMMITISOLEVELS |
Catalog Location |
DBPROP_CATALOGLOCATION |
Catalog Term |
DBPROP_CATALOGTERM |
Column Definition |
DBPROP_COLUMNDEFINITION |
Connect Timeout |
DBPROP_INIT_TIMEOUT |
Current Catalog |
DBPROP_CURRENTCATALOG |
Data Source |
DBPROP_INIT_DATASOURCE |
Data Source Name |
DBPROP_DATASOURCENAME |
Data Source Object Threading Model |
DBPROP_DSOTHREADMODEL |
DBMS Name |
DBPROP_DBMSNAME |
DBMS Version |
DBPROP_DBMSVER |
Extended Properties |
DBPROP_INIT_PROVIDERSTRING |
GROUP BY Support |
DBPROP_GROUPBY |
Heterogeneous Table Support |
DBPROP_HETEROGENEOUSTABLES |
Identifier Case Sensitivity |
DBPROP_IDENTIFIERCASE |
Initial Catalog |
DBPROP_INIT_CATALOG |
Isolation Levels |
DBPROP_SUPPORTEDTXNISOLEVELS |
Isolation Retention |
DBPROP_SUPPORTEDTXNISORETAIN |
Locale Identifier |
DBPROP_INIT_LCID |
Maximum Index Size |
DBPROP_MAXINDEXSIZE |
Maximum Row Size |
DBPROP_MAXROWSIZE |
Maximum Row Size Includes BLOB |
DBPROP_MAXROWSIZEINCLUDESBLOB |
Maximum Tables in SELECT |
DBPROP_MAXTABLESINSELECT |
Multiple Parameter Sets |
DBPROP_MULTIPLEPARAMSETS |
Multiple Results |
DBPROP_MULTIPLERESULTS |
Multiple Storage Objects |
DBPROP_MULTIPLESTORAGEOBJECTS |
Multi-Table Update |
DBPROP_MULTITABLEUPDATE |
NULL Collation Order |
DBPROP_NULLCOLLATION |
NULL Concatenation Behavior |
DBPROP_CONCATNULLBEHAVIOR |
OLE DB Version |
DBPROP_PROVIDEROLEDBVER |
OLE Object Support |
DBPROP_OLEOBJECTS |
Open Rowset Support |
DBPROP_OPENROWSETSUPPORT |
ORDER BY Columns in Select List |
DBPROP_ORDERBYCOLUMNSINSELECT |
Output Parameter Availability |
DBPROP_OUTPUTPARAMETERAVAILABILITY |
Pass By Ref Accessors |
DBPROP_BYREFACCESSORS |
Password |
DBPROP_AUTH_PASSWORD |
Persist Security Info |
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO |
Persistent ID Type |
DBPROP_PERSISTENTIDTYPE |
Prepare Abort Behavior |
DBPROP_PREPAREABORTBEHAVIOR |
Prepare Commit Behavior |
DBPROP_PREPARECOMMITBEHAVIOR |
Procedure Term |
DBPROP_PROCEDURETERM |
Prompt |
DBPROP_INIT_PROMPT |
Provider Friendly Name |
DBPROP_PROVIDERFRIENDLYNAME |
Provider Name |
DBPROP_PROVIDERFILENAME |
Provider Version |
DBPROP_PROVIDERVER |
Read-Only Data Source |
DBPROP_DATASOURCEREADONLY |
Rowset Conversions on Command |
DBPROP_ROWSETCONVERSIONSONCOMMAND |
Schema Term |
DBPROP_SCHEMATERM |
Schema Usage |
DBPROP_SCHEMAUSAGE |
SQL Support |
DBPROP_SQLSUPPORT |
Structured Storage |
DBPROP_STRUCTUREDSTORAGE |
Subquery Support |
DBPROP_SUBQUERIES |
Table Term |
DBPROP_TABLETERM |
Transaction DDL |
DBPROP_SUPPORTEDTXNDDL |
User ID |
DBPROP_AUTH_USERID |
User Name |
DBPROP_USERNAME |
Window Handle |
DBPROP_INIT_HWND |
Recordset Dynamic Properties
The following properties are added to the Recordset object's Properties collection.
ADO Property Name |
OLE DB Property Name |
|---|---|
Access Order |
DBPROP_ACCESSORDER |
Blocking Storage Objects |
DBPROP_BLOCKINGSTORAGEOBJECTS |
Bookmark Type |
DBPROP_BOOKMARKTYPE |
Bookmarkable |
DBPROP_IROWSETLOCATE |
Change Inserted Rows |
DBPROP_CHANGEINSERTEDROWS |
Column Privileges |
DBPROP_COLUMNRESTRICT |
Column Set Notification |
DBPROP_NOTIFYCOLUMNSET |
Command Time Out |
DBPROP_COMMANDTIMEOUT |
Defer Column |
DBPROP_DEFERRED |
Delay Storage Object Updates |
DBPROP_DELAYSTORAGEOBJECTS |
Fetch Backwards |
DBPROP_CANFETCHBACKWARDS |
Hold Rows |
DBPROP_CANHOLDROWS |
IAccessor |
DBPROP_IAccessor |
IColumnsInfo |
DBPROP_IColumnsInfo |
IColumnsRowset |
DBPROP_IColumnsRowset |
IConnectionPointContainer |
DBPROP_IConnectionPointContainer |
IConvertType |
DBPROP_IConvertType |
Immobile Rows |
DBPROP_IMMOBILEROWS |
IRowset |
DBPROP_IRowset |
IRowsetChange |
DBPROP_IRowsetChange |
IRowsetIdentity |
DBPROP_IRowsetIdentity |
IRowsetInfo |
DBPROP_IRowsetInfo |
IRowsetLocate |
DBPROP_IRowsestLocate |
IRowsetResynch |
|
IRowsetScroll |
DBPROP_IRowsetScroll |
IRowsetUpdate |
DBPROP_IRowsetUpdate |
ISequentialStream |
DBPROP_ISequentialStream |
ISupportErrorInfo |
DBPROP_ISupportErrorInfo |
Literal Bookmarks |
DBPROP_LITERALBOOKMARKS |
Literal Row Identity |
DBPROP_LITERALIDENTITY |
Maximum Open Rows |
DBPROP_MAXOPENROWS |
Maximum Pending Rows |
DBPROP_MAXPENDINGROWS |
Maximum Rows |
DBPROP_MAXROWS |
Notification Granularity |
DBPROP_NOTIFICATIONGRANULARITY |
Notification Phases |
DBPROP_NOTIFICATIONPHASES |
Objects Transacted |
DBPROP_TRANSACTEDOBJECT |
Others' Changes Visible |
DBPROP_OTHERUPDATEDELETE |
Others' Inserts Visible |
DBPROP_OTHERINSERT |
Own Changes Visible |
DBPROP_OWNUPDATEDELETE |
Own Inserts Visible |
DBPROP_OWNINSERT |
Preserve on Abort |
DBPROP_ABORTPRESERVE |
Preserve on Commit |
DBPROP_COMMITPRESERVE |
Quick Restart |
DBPROP_QUICKRESTART |
Reentrant Events |
DBPROP_REENTRANTEVENTS |
Remove Deleted Rows |
DBPROP_REMOVEDELETED |
Report Multiple Changes |
DBPROP_REPORTMULTIPLECHANGES |
Return Pending Inserts |
DBPROP_RETURNPENDINGINSERTS |
Row Delete Notification |
DBPROP_NOTIFYROWDELETE |
Row First Change Notification |
DBPROP_NOTIFYROWFIRSTCHANGE |
Row Insert Notification |
DBPROP_NOTIFYROWINSERT |
Row Privileges |
DBPROP_ROWRESTRICT |
Row Resynchronization Notification |
DBPROP_NOTIFYROWRESYNCH |
Row Threading Model |
DBPROP_ROWTHREADMODEL |
Row Undo Change Notification |
DBPROP_NOTIFYROWUNDOCHANGE |
Row Undo Delete Notification |
DBPROP_NOTIFYROWUNDODELETE |
Row Undo Insert Notification |
DBPROP_NOTIFYROWUNDOINSERT |
Row Update Notification |
DBPROP_NOTIFYROWUPDATE |
Rowset Fetch Position Change Notification |
DBPROP_NOTIFYROWSETFETCHPOSISIONCHANGE |
Rowset Release Notification |
DBPROP_NOTIFYROWSETRELEASE |
Scroll Backwards |
DBPROP_CANSCROLLBACKWARDS |
Server Cursor |
DBPROP_SERVERCURSOR |
Skip Deleted Bookmarks |
DBPROP_BOOKMARKSKIPPED |
Strong Row Identity |
DBPROP_STRONGITDENTITY |
Unique Rows |
DBPROP_UNIQUEROWS |
Updatability |
DBPROP_UPDATABILITY |
Use Bookmarks |
DBPROP_BOOKMARKS |
Command Dynamic Properties
The following properties are added to the Command object's Properties collection.
ADO Property Name |
OLE DB Property Name |
|---|---|
Access Order |
DBPROP_ACCESSORDER |
Base Path |
SSPROP_STREAM_BASEPATH |
Blocking Storage Objects |
DBPROP_BLOCKINGSTORAGEOBJECTS |
Bookmark Type |
DBPROP_BOOKMARKTYPE |
Bookmarkable |
DBPROP_IROWSETLOCATE |
Change Inserted Rows |
DBPROP_CHANGEINSERTEDROWS |
Column Privileges |
DBPROP_COLUMNRESTRICT |
Column Set Notification |
DBPROP_NOTIFYCOLUMNSET |
Content Type |
SSPROP_STREAM_CONTENTTYPE |
Cursor Auto Fetch |
SSPROP_CURSORAUTOFETCH |
Defer Column |
DBPROP_DEFERRED |
Defer Prepare |
SSPROP_DEFERPREPARE |
Delay Storage Object Updates |
DBPROP_DELAYSTORAGEOBJECTS |
Fetch Backwards |
DBPROP_CANFETCHBACKWARDS |
Hold Rows |
DBPROP_CANHOLDROWS |
IAccessor |
DBPROP_IAccessor |
IColumnsInfo |
DBPROP_IColumnsInfo |
IColumnsRowset |
DBPROP_IColumnsRowset |
IConnectionPointContainer |
DBPROP_IConnectionPointContainer |
IConvertType |
DBPROP_IConvertType |
Immobile Rows |
DBPROP_IMMOBILEROWS |
IRowset |
DBPROP_IRowset |
IRowsetChange |
DBPROP_IRowsetChange |
IRowsetIdentity |
DBPROP_IRowsetIdentity |
IRowsetInfo |
DBPROP_IRowsetInfo |
IRowsetLocate |
DBPROP_IRowsetLocate |
IRowsetResynch |
DBPROP_IRowsetResynch |
IRowsetScroll |
DBPROP_IRowsetScroll |
IRowsetUpdate |
DBPROP_IRowsetUpdate |
ISequentialStream |
DBPROP_ISequentialStream |
ISupportErrorInfo |
DBPROP_ISupportErrorInfo |
Literal Bookmarks |
DBPROP_LITERALBOOKMARKS |
Literal Row Identity |
DBPROP_LITERALIDENTITY |
Lock Mode |
DBPROP_LOCKMODE |
Maximum Open Rows |
DBPROP_MAXOPENROWS |
Maximum Pending Rows |
DBPROP_MAXPENDINGROWS |
Maximum Rows |
DBPROP_MAXROWS |
Notification Granularity |
DBPROP_NOTIFICATIONGRANULARITY |
Notification Phases |
DBPROP_NOTIFICATIONPHASES |
Objects Transacted |
DBPROP_TRANSACTEDOBJECT |
Others' Changes Visible |
DBPROP_OTHERUPDATEDELETE |
Others' Inserts Visible |
DBPROP_OTHERINSERT |
Output Encoding Property |
DBPROP_OUTPUTENCODING |
Output Stream Property |
DBPROP_OUTPUTSTREAM |
Own Changes Visible |
DBPROP_OWNUPDATEDELETE |
Own Inserts Visible |
DBPROP_OWNINSERT |
Preserve on Abort |
DBPROP_ABORTPRESERVE |
Preserve on Commit |
DBPROP_COMMITPRESERVE |
Quick Restart |
DBPROP_QUICKRESTART |
Reentrant Events |
DBPROP_REENTRANTEVENTS |
Remove Deleted Rows |
DBPROP_REMOVEDELETED |
Report Multiple Changes |
DBPROP_REPORTMULTIPLECHANGES |
Return Pending Inserts |
DBPROP_RETURNPENDINGINSERTS |
Row Delete Notification |
DBPROP_NOTIFYROWDELETE |
Row First Change Notification |
DBPROP_NOTIFYROWFIRSTCHANGE |
Row Insert Notification |
DBPROP_NOTIFYROWINSERT |
Row Privileges |
DBPROP_ROWRESTRICT |
Row Resynchronization Notification |
DBPROP_NOTIFYROWRESYNCH |
Row Threading Model |
DBPROP_ROWTHREADMODEL |
Row Undo Change Notification |
DBPROP_NOTIFYROWUNDOCHANGE |
Row Undo Delete Notification |
DBPROP_NOTIFYROWUNDODELETE |
Row Undo Insert Notification |
DBPROP_NOTIFYROWUNDOINSERT |
Row Update Notification |
DBPROP_NOTIFYROWUPDATE |
Rowset Fetch Position Change Notification |
DBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE |
Rowset Release Notification |
DBPROP_NOTIFYROWSETRELEASE |
Scroll Backwards |
DBPROP_CANSCROLLBACKWARDS |
Server Cursor |
DBPROP_SERVERCURSOR |
Server Data on Insert |
DBPROP_SERVERDATAONINSERT |
Skip Deleted Bookmarks |
DBPROP_BOOKMARKSKIP |
Strong Row Identity |
DBPROP_STRONGIDENTITY |
Updatability |
DBPROP_UPDATABILITY |
Use Bookmarks |
DBPROP_BOOKMARKS |
XML Root |
SSPROP_STREAM_XMLROOT |
XSL |
SSPROP_STREAM_XSL |
For specific implementation details and functional information about the Microsoft SQL Server OLE DB Provider, consult the OLE DB Provider for SQL Server documentation in the OLE DB section of the MDAC SDK.