Microsoft Ole Db Provider For Odbc Drivers Download
Dec 08, 2021 In the provider tab I only see ' ORACLE Provider for OLE DB' I do not see ' Microsoft. Most versions of Windows have the Microsoft OLE DB Provider for ODBC. Click 'Run' in the file download window that. How to Turn on the Sound Driver in. During my recent conversation about Oracle linked server with one of my client, I learned something new. Download ODBC Driver for SQL Server.; 2 minutes to read; In this article Microsoft ODBC Driver 17 for SQL Server Windows. Download the Microsoft ODBC Driver 17 for SQL Server on Windows. Fixing ODBC issues with Excel 2016 So appears that’s the ODBC drivers in the click-to-run Office 2016 doesn’t install properly or it’s possible it’s. Jul 05, 2016 This document describes the Microsoft OLE DB Provider for ODBC, explains the procedures for installing, configuring, and testing the provider, and lists the limitations of the current implementation. Although using this provider is a convenient way to access data through ODBC data sources, it is possible to obtain better performance.
- Microsoft Ole Db Provider For Sql Server 2005 Download
- Download Microsoft Ole Db Provider For Odbc Drivers For Windows 7
- Microsoft Ole Db Provider For Odbc Drivers Download Free
- Microsoft Ole Db Provider For Odbc Drivers Download Torrent
I am setting up Sparx's Enterprise Architect to work in centralized mode using:
- EA version 10.0.1007
- Database: MySQL CE version 5.6.13.1
- Connector: mysql-connector-odbc-5.3.2-win32.msi
I've successfully setup the database and connector in Data Sources. Test connection is working.
On the first project, EA requesting to import project, in which I imported EABase.eap from C:Program Files (x86)Sparx SystemsEA. This is success also.
I click the Add New Model from Pattern, choose the Name, and click OK, then I got the following error (shown three times):
If I close the EA and then reopen, the packages and diagrams are still there, meaning they are saved to MySQL database.
The error is also shown whenever I first clicked on the diagram.
How to solve this problem?
DavidDavidDid you use the correct ODBC driver?
32 vs 64 bit is relevant here.You should use the 32bit driver even if you are on 64bit Windows.
If you are using a 64-bit operating system (see here):
• Enterprise Architect requires 32-bit ODBC drivers to connect to a repository through ODBC; to set up the ODBC configuration on 64-bit clients, run the 32-bit ODBC Data Source Administrator from C:WindowsSysWOW64odbcad32.exe
• You can quickly load the correct 32-bit ODBC Data Source Administrator by selecting Tools ODBC Data Sources in the Enterprise Architect menu bar
Also: Did you check the two checkboxes as described in the installation of the driver? (Select the Return matched rows instead of affected rows and Allow big result sets checkboxes.) See here for a detailed description
I had the same error while I migrated some projects from eap files to a MySQL database.
The problem is caused by Cast(Partition as Signed);
The Enterprise Architect uses a column name 'Partition' but it is a reserved key word since version 5.6.2 (see the manual) of the MySQL dbms.
Switch to the MySQL version 5.5.46 (x64) and the error will disappear.
Not the answer you're looking for? Browse other questions tagged mysqlodbcenterprise-architect or ask your own question.
-->The Microsoft OLE DB Provider for SQL Server, SQLOLEDB, allows ADO to access Microsoft SQL Server.
The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. Indicates that the ODBC driver generates a recordset that. The Microsoft OLE DB Provider for ODBC inserts several dynamic properties into the Properties. How to install DB2 ODBC or OLEDB Driver. On how to install either OLEDB or ODBC Driver in order to. ODBC' and 'OLE DB Provider' You can download it.
Important
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.
Connection String Parameters
To connect to this provider, set the Provider argument to the ConnectionString property to:
This value can also be set or read using the Provider property.
Typical Connection String
A typical connection string for this provider is:
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). |
Note
If you are connecting to a data source provider that supports Windows authentication, you should specify Trusted_Connection=yes or Integrated Security = SSPI instead of user ID and password information in the connection string.
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, 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, 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 (DLL) 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, 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, 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:
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 earlier:
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
Transact-SQL
Microsoft Ole Db Provider For Sql Server 2005 Download
SQL Server Features
With SQL Server, ADO can use XML for Command input and retrieve results in XML stream format instead of in Recordset objects. For more information, see Using Streams for Command Input and Retrieving Resultsets Into Streams.
Accessing sql_variant data using MDAC 2.7, MDAC 2.8, or Windows DAC 6.0
Microsoft SQL Server has a data type called sql_variant. Similar to OLE DB's DBTYPE_VARIANT, the sql_variant data type can store data of several different types. However, there are a few key differences between DBTYPE_VARIANT and sql_variant. ADO also handles data stored as a sql_variant value differently than how it handles other data types. The following list describes issues to consider when you access SQL Server data stored in columns of type sql_variant.
In MDAC 2.7, MDAC 2.8, and Windows Data Access Components (Windows DAC) 6.0, the OLE DB Provider for SQL Server supports the sql_variant type. The OLE DB Provider for ODBC does not.
The sql_variant type does not exactly match the DBTYPE_VARIANT data type. The sql_variant type supports a few new subtypes not supported by DBTYPE_VARIANT, including GUID, ANSI (non-UNICODE) strings, and BIGINT. Using subtypes other than those listed earlier will work correctly.
The sql_variant subtype NUMERIC does not match the DBTYPE_DECIMAL in size.
Multiple data type coercions will result in types that do not match. For example, coercing a sql_variant with a subtype of GUID to a DBTYPE_VARIANT will result in a subtype of safearray(bytes). Converting this type back to a sql_variant will result in a new subtype of array(bytes).
Recordset fields that contain sql_variant data can be remoted (marshaled) or persisted only if the sql_variant contains specific subtypes. Attempting to remote or persist data with the following unsupported subtypes will cause a run-time error (unsupported conversion) from the Microsoft Persistence Provider (MSPersist): VT_VARIANT, VT_RECORD, VT_ILLEGAL, VT_UNKNOWN, VT_BSTR, and VT_DISPATCH.
The OLE DB Provider for SQL Server in MDAC 2.7, MDAC 2.8, and Windows DAC 6.0 has a dynamic property called Allow Native Variants which, as the name implies, allows developers to access the sql_variant in its native form as opposed to a DBTYPE_VARIANT. If this property is set, and a Recordset is opened with the Client Cursor Engine (adUseClient), the Recordset.Open call will fail. If this property is set and a Recordset is opened with server cursors (adUseServer), the Recordset.Open call will succeed, but accessing columns of type sql_variant will produce an error.
In client applications that use MDAC 2.5, sql_variant data can be used with queries against Microsoft SQL Server. However, the values of the sql_variant data are treated as strings. Such client applications should be upgraded to MDAC 2.7, MDAC 2.8, or Windows DAC 6.0.
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.
Microsoft Ole Db Provider For Odbc Drivers Download Windows Xp
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 Properties collection of the Connection object.
| 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 Properties collection of the Recordset object.
| 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 Properties collection of the Command object.
| 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, see the SQL Server Provider.
See Also
-->The Microsoft OLE DB Provider for SQL Server, SQLOLEDB, allows ADO to access Microsoft SQL Server.
Important
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.
Connection String Parameters
To connect to this provider, set the Provider argument to the ConnectionString property to:
This value can also be set or read using the Provider property.
Typical Connection String
A typical connection string for this provider is:
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). |
Microsoft Ole Db Provider For Odbc Drivers Download Windows Server 2008
Note
If you are connecting to a data source provider that supports Windows authentication, you should specify Trusted_Connection=yes or Integrated Security = SSPI instead of user ID and password information in the connection string.
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, 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, 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 (DLL) 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, 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, 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:
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 earlier:
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
Transact-SQL
SQL Server Features
With SQL Server, ADO can use XML for Command input and retrieve results in XML stream format instead of in Recordset objects. For more information, see Using Streams for Command Input and Retrieving Resultsets Into Streams.
Accessing sql_variant data using MDAC 2.7, MDAC 2.8, or Windows DAC 6.0
Microsoft SQL Server has a data type called sql_variant. Similar to OLE DB's DBTYPE_VARIANT, the sql_variant data type can store data of several different types. However, there are a few key differences between DBTYPE_VARIANT and sql_variant. ADO also handles data stored as a sql_variant value differently than how it handles other data types. The following list describes issues to consider when you access SQL Server data stored in columns of type sql_variant.
In MDAC 2.7, MDAC 2.8, and Windows Data Access Components (Windows DAC) 6.0, the OLE DB Provider for SQL Server supports the sql_variant type. The OLE DB Provider for ODBC does not.
The sql_variant type does not exactly match the DBTYPE_VARIANT data type. The sql_variant type supports a few new subtypes not supported by DBTYPE_VARIANT, including GUID, ANSI (non-UNICODE) strings, and BIGINT. Using subtypes other than those listed earlier will work correctly.
The sql_variant subtype NUMERIC does not match the DBTYPE_DECIMAL in size.
Multiple data type coercions will result in types that do not match. For example, coercing a sql_variant with a subtype of GUID to a DBTYPE_VARIANT will result in a subtype of safearray(bytes). Converting this type back to a sql_variant will result in a new subtype of array(bytes).
Recordset fields that contain sql_variant data can be remoted (marshaled) or persisted only if the sql_variant contains specific subtypes. Attempting to remote or persist data with the following unsupported subtypes will cause a run-time error (unsupported conversion) from the Microsoft Persistence Provider (MSPersist): VT_VARIANT, VT_RECORD, VT_ILLEGAL, VT_UNKNOWN, VT_BSTR, and VT_DISPATCH.
The OLE DB Provider for SQL Server in MDAC 2.7, MDAC 2.8, and Windows DAC 6.0 has a dynamic property called Allow Native Variants which, as the name implies, allows developers to access the sql_variant in its native form as opposed to a DBTYPE_VARIANT. If this property is set, and a Recordset is opened with the Client Cursor Engine (adUseClient), the Recordset.Open call will fail. If this property is set and a Recordset is opened with server cursors (adUseServer), the Recordset.Open call will succeed, but accessing columns of type sql_variant will produce an error.
In client applications that use MDAC 2.5, sql_variant data can be used with queries against Microsoft SQL Server. However, the values of the sql_variant data are treated as strings. Such client applications should be upgraded to MDAC 2.7, MDAC 2.8, or Windows DAC 6.0.
Microsoft Ole Db Provider For Oracle Download
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.
Microsoft Ole Db Provider For Db2 Download
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 Properties collection of the Connection object.
| 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 |
Download Microsoft Ole Db Provider For Odbc Drivers For Windows 7
Download Ole Db Provider For Oracle
Recordset Dynamic Properties
The following properties are added to the Properties collection of the Recordset object.
| 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 Properties collection of the Command object.
| 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 |
Microsoft Ole Db Provider For Odbc Drivers Download Free
For specific implementation details and functional information about the Microsoft SQL Server OLE DB Provider, see the SQL Server Provider.