Olr Cannot Be Opened. Error 2

Leave a comment

ORA-19809: limit exceeded for recovery files tips. Oracle Database Tips by Donald BurlesonOctober 22, 2015. How do I fix this ORA-19809 error? SQL alter database open; alter database open. ERROR at line 1: ORA-16038: log 1 sequence# 230 cannot be archived. After upgrading from SQL Safe Backup 8.5.1 to 8.5.2, the Web Console may need to be re-registered. Go to the product registration in the Web Console, delete the SQL Safe Backup 8.5.1 product, and register the SQL Safe Backup 8.5.2 product.

This chapter describes use of the registry for various Oracle Database for Windows components. It also lists recommended values and ranges for configuration parameters.

This chapter contains these topics:

About Configuration Parameters

Oracle Database for Windows uses configuration parameters to locate files and specify run-time parameters common to all Oracle products. When an Oracle program or application requires a translation for a particular configuration variable, Oracle Database for Windows uses the associated parameter. All Oracle parameters are stored in the registry.

Registry Overview

Oracle Database for Windows stores its configuration information in a repository (the registry) that is organized in a tree format. The tree format consists of keys in the registry and parameter values for the keys. Keys and parameter values can be viewed and modified in Registry Editor.

Keys are folders that appear in the left pane of a Registry Editor window. A key contains subkeys or parameters.

Caution:

Although Registry Editor lets you view and modify registry keys and parameter values, you normally are not required to do so. In fact, you can render your system useless if you make incorrect changes. Therefore, only advanced users should edit the registry. Back up your system before making any changes in the registry.

Parameters in Registry Editor appear as a string, consisting of three components:

  • Parameter name

  • Value class or type of entry

  • Value itself

For example, parameter ORACLE_SID can have the following entry in the registry:

Value classes for Oracle Database for Windows parameters are:

  • String value with a REG_SZ, REG_EXPAND_SZ (for an expandable string), or a REG_MULTI_SZ (for multiple strings) prefix to identify a parameter value entry as a data string

  • Binary value with a REG_DWORD prefix to identify a value entry as a dword (hexadecimal data) entry

Most Oracle Database for Windows parameter values are string types. Use Oracle Universal Installer defaults when a type is not given.

Registry Parameters

This section describes Oracle Database for Windows registry parameters for the following keys. Other products, such as Oracle Enterprise Manager, have additional keys and parameters that are not described in this chapter.

To modify these registry values, see 'Managing Registry Parameters with regedit'.

HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_HOME_NAME

Each time you install Oracle products into a new Oracle home on your computer, HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_HOME_NAME is created. This subkey contains parameter values for most Oracle products.

Note:

See Appendix B, 'Optimal Flexible Architecture', in Oracle Database Installation Guide for Microsoft Windows for details on the PATH variable and registry values when you are working with multiple Oracle homes.

HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_HOME_NAME includes the following parameters for an Oracle home directory. Depending on products you install, additional parameters can also be created. See your Windows development manuals.

MSHELP_TOOLS

Specifies the location of Windows help files. The default value is

NLS_LANG and Other Globalization Parameters

Specifies supported language, territory, and character set. This parameter specifies the language in which messages appear, the territory and its conventions for calculating week and day numbers, and the character set displayed. Oracle Universal Installer sets this value during installation based on the language setting of the operating system.

The default value for NLS_LANG, if not set, is AMERICAN_AMERICA.US7ASCII.

There are other globalization parameters that can be set along NLS_LANG to override some values implicitly determined by NLS_LANG. These parameters are:

The following parameters can also be set along NLS_LANG to determine globalization behavior that is independent from the value of NLS_LANG:

Note:

All globalization parameters set in the environment and Registry for a database client are ignored if NLS_LANG is not set.

See Oracle Database Globalization Support Guide for more information about NLS_LANG and other globalization parameters

ORA_CWD

Specifies current working directory. For example, if you set this parameter and then use ORADIM, a log file called oradim.log is created in this directory. This parameter must be manually set.

ORA_SID_AUTOSTART

Starts Oracle Database when OracleServiceSID service is started. The default value is true.

ORA_SID_PFILE

Specifies full path to initialization parameter file. The default value is ORACLE_BASEadminDB_NAMEpfileinit.ora

ORA_SID_SHUTDOWN

When set to true, the default value, this parameter shuts down the instance of Oracle Database identified by SID when OracleServiceSID is stopped manually—using either the Control Panel or Net stop command.

ORA_SID_SHUTDOWN_TIMEOUT

Sets maximum time (in seconds) to wait for shutdown to complete before the service for a particular SID stops. The default value is 30.

ORA_SID_SHUTDOWNTYPE

Specifies mode in which Oracle Database is shut down when you stop OracleServiceSID. Valid values are a (abort), i (immediate), and n (normal). The default value is i.

ORA_TZFILE

Specifies location of time zone file. Each file contains:

  • Valid time zone names

  • Offset from UTC

  • Abbreviation for standard time

  • Abbreviation for daylight savings time

In previous releases, the default value for ORA _TZFILE was

Beginning with Oracle Database 11g Release 2 (11.2), the default value is

The timezone_version_number.dat datafiles contain most commonly used time zones and are smaller for better database performance. The new default, timezlrg_version_number.dat, includes time zones not defined in the smaller file.

See Also:

Oracle Database Globalization Support Guide for additional details about time zone files

ORACLE_AFFINITY

Specifies Windows processor affinity of each thread within the Oracle Database process. This parameter must be manually added. Oracle recommends consulting Oracle Support Services before changing this parameter. The format is:

Each name setting must be the name of a background thread, user for non-background (shadow) threads, or def for any thread type not handled specifically.

The name mask sets the affinity mask of the Oracle Database process. Valid background thread names include DBW0, LGWR, PMON, SMON, ARCH, RECO, CKPT, TRWR, J000 through J999, P000 through P481, and any other name found in the NAME column of the v$bgprocessdata dictionaryview.

Each affinity setting must be a valid affinity mask (or its numeric equivalent) for the corresponding name. Process affinity masks are used only when Oracle services are first started. Each thread's affinity is set only when the individual thread is started (for example, at database startup time for the background threads).

ORACLE_BASE

Specifies the top-level Oracle directory (for example, C:apporacleproduct11.2.0) that contains ORACLE_HOME, admin, and oradata. The default is ORACLE_BASE.

ORACLE_GROUP_NAME

Specifies the name of the group containing icons of the Oracle products installed. The parameter is added to your registry when you first install Oracle products, even if Oracle Universal Installer does not create a program group for Oracle products you have installed (for example, if you have installed only Oracle Net software). The default value is Oracle-HOME_NAME.

ORACLE_HOME

Specifies Oracle home directory in which Oracle products are installed. This directory is immediately beneath the Oracle base directory in the Oracle directory hierarchy. The default value is the drive letter and name that you specify during installation.

ORACLE_HOME_KEY

The HKEY_LOCAL_MACHINE location of Oracle parameters. The default value is softwareoracleHOMEID.

ORACLE_HOME_NAME

Specifies home name of Oracle home directory in which Oracle products are installed. The default value is the name that you specify during installation.

ORACLE_PRIORITY

Determines Windows scheduling priorities of threads within the Oracle Database management system process. The format is:

The name class sets the priority class of the Oracle Database process. Threads can be assigned priority either collectively or individually. The collective name user designates non-background (shadow) threads; the collective name def designates any thread type not handled specifically. Valid individual background thread names include DBW0, LGWR, PMON, SMON, ARCH0, RECO, CKPT, TRWR, J000 through J999SNP0through SNP9, and any other name found in the NAME column of the v$bgprocess data dictionary view.

The default value is class:normal; def:normal.

Note:

ORACLE_PRIORITY is not automatically created for you in the registry. When it is not defined in the registry, Windows default values are used for thread priorities.

ORACLE_SID

Specifies the name of the Oracle Database instance on the host computer. The value of this parameter is the SID for the instance. The default value is specified by the entry in the Database Identification window of Oracle Universal Installer.

OSAUTH_PREFIX_DOMAIN

Enables user authentication. When it is set to true, it enables the server to differentiate between one username and another, whether they are local users, domain users, or domain users on another domain in your network. When it is set to false, the domain is ignored, and the local user becomes the default value of the operating system user returned to the server. The default value is true.

RDBMS_ARCHIVE

Specifies the location of backup database files. The default value is ORACLE_HOMEdatabasearchive.

RDBMS_CONTROL

Specifies the location of backup database control files. The default value is ORACLE_HOMEdatabase.

SQLPATH

Specifies the location of SQL scripts. The default value is ORACLE_HOMEdbs.

HKEY_LOCAL_MACHINESOFTWAREORACLE

This subkey contains the following parameters:

INST_LOC

Specifies the location of Oracle Universal Installer files. The default value is System Drive:program filesoracleinventory.

OO4O

Specifies the location of Oracle Objects for OLE message files. The default value is ORACLE_HOMEoo4omesg.

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices

HKEY_LOCAL_MACHINESYSTEMCurrentControlSet contains four keys:

  • Control

  • Enum

  • HardwareProfiles

  • Services

The first three are used by the operating system. You can edit only the Services subkey, which contains:

Parameters for Oracle Counters for Windows Performance Monitor

Oracle Counters for Windows Performance Monitor parameters appear in:

where ORACLEver refers to whatever version of Oracle Database is installed.

For multiple Oracle homes compliance starting with Oracle Database 10g Release 1 (10.1), parameters Hostname, Username, Password, LOGFILE, and PERF_FILE_NAME have been moved to

Note:

Modify only the Hostname, Password, and Username values to point to any database. Oracle recommends that you use operfcfg. See 'Using operfcfg'.

For Oracle Counters for Windows Performance Monitor to display information for Oracle Database performance objects, it must log in to the database. Modify the following parameters if the default information is not applicable or if you want to access another database:

  • Hostname displays the SID of the database that Oracle Counters for Windows Performance Monitor will connect to. The default value is blank, causing Oracle Counters for Windows Performance Monitor to connect to the default database on the computer.

    If you specify a different SID, you must also add a connect descriptor for that SID to your tnsnames.ora file. Oracle recommends that you use Oracle Net Configuration Assistant, but you can also copy, paste, and modify the following example:

  • Password displays the encrypted password for the user name to access the database.

  • Username displays the user name to access the database. The default value is SYSTEM.

Oracle Counters for Windows Performance Monitor requires the following parameters as entry points:

  • Close specifies the close entry point for the DLL. The default value is CloseOracleverPerformanceData.

  • Collect specifies the collect entry point for the DLL. The default value is CollectOracleverPerformanceData.

  • Library specifies the name of the Oracle Counters for Windows Performance Monitor DLL. The default value is ORAPERFver.dll

  • Open specifies the open entry point for the DLL. The default value is OpenOracleverPerformanceData.

The following parameters specify Oracle Counters for Windows Performance Monitor log file and object configuration files:

  • LOGFILE specifies the name of the Oracle Counters for Windows Performance Monitor log file. This log file reports any errors, such as Oracle Database objects not appearing or database access problems. The default value is ORACLE_HOMEdbsoperfver.log.

  • PERF_FILE_NAME specifies the location of the PERFver.ora file, which contains all performance objects displayed by Oracle Counters for Windows Performance Monitor. The default value is ORACLE_HOMEdbsperfver.ora

Parameters for Oracle Database Services

HKEY_LOCAL_MACHINESYSTEMCURRENTCONTROLSETSERVICES subkey contains additional subkeys that correspond to each Oracle Database service.

Each service subkey contains the following parameters:

  • DisplayName specifies the service name of the instance whose SID is SID. The default value is the name of the service. For example, OracleServiceORCL1, where ORCL1 is the SID.

  • ImagePath specifies the fully qualified path name of the executable started by the service and any command-line arguments passed into the executable at run time. The default value is the path to the executable file of the product.

  • ObjectName specifies the logon user account and computer to which the service should log on. The default value is LocalSystem.

Oracle RAC Registry Parameters

Oracle RAC registry values are based on the clusterware. If you are not using the clusterware, then some of this information may not be applicable to your particular cluster environment.

Note:

Oracle RAC is not supported on Windows XP, Windows Vista, Windows 7, and any 32-bit Windows operating system.

HKEY_LOCAL_MACHINESOFTWAREORACLEOCR

This subkey contains the following values:

  • OCRROOT points to the location of the Oracle Cluster Registry file

  • LOCAL_ONLY which is set to False for a cluster installation and True for a single-instance database installation

Managing Registry Parameters with regedit

Caution:

Do not edit your registry unless absolutely necessary. If an error occurs in your registry, then Oracle Database for Windows can stop functioning, and the registry itself can become unusable.

Modifying a Parameter Value with regedit

To edit Oracle-related settings:

  1. Start Registry Editor in one of two ways:

    • From the command prompt, enter:

    • From the Start menu, select Run, enter regedit in the Open field, and click OK.

    The Registry Editor window appears.

  2. Navigate to the values you want to view or modify by double-clicking appropriate keys.

    The left-hand side of the window shows the hierarchy of registry keys, and the right-hand side of the window shows various values associated with a key.

  3. Double-click the parameter to edit.

    The String Editor dialog appears:


    Description of the illustration stringre.gif

  4. Make any necessary edits.

  5. Click OK.

  6. Choose Exit from the Registry menu.

Adding a Registry Parameter with regedit

To add a parameter to the registry:

  1. Start Registry Editor in one of two ways:

    • From the command prompt, enter:

    • From the Start menu, select Run, enter regedit in the Open field, and click OK.

    The Registry Editor window appears.

  2. Navigate to the key to which you want to add the new value.

  3. Choose Add Value from the Edit menu.

    The Add Value dialog appears:


    Description of the illustration addvalue.gif

  4. In the Value Name field, type the name of the value that you want to add to the currently selected key.

  5. In the Data Type list, select the data type that you want to assign to the added value:

    • REG_SZ, REG_EXPAND_SZ (for an expandable string), or REG_MULTI_SZ (for multiple strings) for a data string

    • Binary value with a REG_DWORD prefix to identify a value entry as a DWORD (hexadecimal data) entry

  6. Click OK.

    The String Editor dialog appears:

    Motor Era offers service repair manuals for your Toyota Vitz - DOWNLOAD your manual now! Toyota Vitz service repair manuals. Complete list of Toyota Vitz auto service repair manuals: TOYOTA. Parts list catalogue manual → View webpages ( download→pdf→url ) TOYOTA. Title: Toyota Vitz 2004 Service And Repair Manual, Author: GraigForeman, Name: Toyota Vitz 2004 Service And Repair Manual, Length: 4 pages, Page: 1, Published: 2013-10-05 Issuu company logo Issuu. Toyota vitz 2004 maintenance manual. Toyota Owner manuals and warranty information are the keys to quality maintenance for your vehicle. No need to hunt down a separate Toyota repair manual or Toyota service manual. From warranties on Toyota replacement parts to details on features, Toyota Owners manuals help you find everything you need to know about your vehicle, all in one place.


    Description of the illustration stringre.gif

  7. Type the value for the parameter.

  8. Click OK.

    Registry Editor adds the parameter.

  9. Choose Exit from the Registry menu.

Managing Registry Parameters with Oracle Administration Assistant for Windows

Instead of using regedit to add, edit, and delete parameters for an Oracle home, you can use the Oracle Home Configuration snap-in, one of several snap-ins included as part of Oracle Administration Assistant for Windows. You must have Microsoft Management Console on your computer to use this product.

See 'HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_HOME_NAME' for more information about Oracle home parameters.

Starting Oracle Administration Assistant for Windows

To start Oracle Administration Assistant for Windows:

  1. What mod adds blood moon. From the Start menu, select Programs, then select Oracle -HOME_NAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

    Oracle Administration Assistant for Windows starts.

  2. Expand Oracle Homes.

  3. Right-click the Oracle home that you want to modify.


    Description of the illustration admin1.gif

  4. Click Properties. The Properties dialog appears.


    Description of the illustration admin2.gif

Adding Oracle Home Parameters

To add an Oracle home parameter:

  1. Click Add in the Properties dialog.

    The Add Value dialog appears.


    Description of the illustration admin3.gif

  2. Enter the name in the Parameter Name field.

  3. Enter the value in the Parameter Value field.

  4. Click OK.

  5. Click Apply.

    Note:

    With Oracle Administration Assistant for Windows, you can add parameters only with a data type of REG_SZ. Use regedit to add parameters with a data type of REG_EXPAND_SZ, REG_MULTI_SZ, or REG_DWORD.

Editing Oracle Home Parameters

To change the default SID, select the SID from the Default SID list in the Properties dialog. To edit one of the other parameters:

  1. Select the parameter in the Other Settings list in the Properties dialog.

  2. Click Edit.


    Description of the illustration admin4.gif

    The Edit Value dialog appears.


    Description of the illustration admin5.gif

  3. Modify the value.

  4. Click OK.

  5. Click Apply.

Deleting Oracle Home Parameters

To delete an Oracle home parameter:

  1. Select the parameter in the Other Settings list in the Properties dialog.

  2. Click Delete.

Modifying Oracle Counters for Windows Performance Monitor Parameters

Instead of using regedit to modify Oracle Counters for Windows Performance Monitor's Hostname, Password, and Username parameters, you can use operfcfg or Oracle Administration Assistant for Windows.

See Also:

'Parameters for Oracle Counters for Windows Performance Monitor' for more information about Hostname, Password, and Username parameters

Using operfcfg

operfcfg is an Oracle Database tool that you run from the command prompt. You must specify the system password by operfcfg.exe, located in the ORACLE_HOMEbin directory. operfcfg.exe prompts for a user name, password, and TNS alias.

For example:

If you run the following command, then operfcfg.exe does not prompt for a password:

The -U and -D commands are optional.

The following examples illustrate use of operfcfg. First, to change user name to dba_admin and leave database name at its current value, you would enter:

To change user name to dba_admin and database name to prod, you would enter:

In the final example, you change host name to a blank value, causing Oracle Counters for Windows Performance Monitor to connect to the default database on the computer. The current user name and password must be valid user accounts on this database. Enter:

Using Oracle Administration Assistant for Windows

Oracle Counters for Windows Performance Monitor snap-in is part of Oracle Administration Assistant for Windows. You must have Microsoft Management Console on your computer to use this product.

To use Oracle Counters for Windows Performance Monitor snap-in:

  1. From the Start menu, select Programs, then select Oracle -HOME_NAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

    Oracle Administration Assistant for Windows starts.

  2. Right-click Performance Monitor.

  3. Click Properties.

    The Performance Monitor Properties dialog appears.

  4. Modify the text in the Username, Password, or Database fields.

  5. Click Apply.

-->

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:

KeywordDescription
ProviderSpecifies the OLE DB Provider for SQL Server.
Data Source or ServerSpecifies the name of a server.
Initial Catalog or DatabaseSpecifies the name of a database on the server.
User ID or uidSpecifies the user name (for SQL Server Authentication).
Password or pwdSpecifies 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.

ParameterDescription
Trusted_ConnectionIndicates 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 LanguageIndicates 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 AddressIndicates the network address of the SQL Server specified by the Location property.
Network LibraryIndicates 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 PrepareDetermines whether SQL Server creates temporary stored procedures when Commands are prepared (by the Prepared property).
Auto TranslateIndicates 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 SizeIndicates 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 NameIndicates the client application name.
Workstation IDA 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.

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 Properties collection of the Connection object.

ADO Property NameOLE DB Property Name
Active SessionsDBPROP_ACTIVESESSIONS
Asynchable AbortDBPROP_ASYNCTXNABORT
Asynchable CommitDBPROP_ASYNCTNXCOMMIT
Autocommit Isolation LevelsDBPROP_SESS_AUTOCOMMITISOLEVELS
Catalog LocationDBPROP_CATALOGLOCATION
Catalog TermDBPROP_CATALOGTERM
Column DefinitionDBPROP_COLUMNDEFINITION
Connect TimeoutDBPROP_INIT_TIMEOUT
Current CatalogDBPROP_CURRENTCATALOG
Data SourceDBPROP_INIT_DATASOURCE
Data Source NameDBPROP_DATASOURCENAME
Data Source Object Threading ModelDBPROP_DSOTHREADMODEL
DBMS NameDBPROP_DBMSNAME
DBMS VersionDBPROP_DBMSVER
Extended PropertiesDBPROP_INIT_PROVIDERSTRING
GROUP BY SupportDBPROP_GROUPBY
Heterogeneous Table SupportDBPROP_HETEROGENEOUSTABLES
Identifier Case SensitivityDBPROP_IDENTIFIERCASE
Initial CatalogDBPROP_INIT_CATALOG
Isolation LevelsDBPROP_SUPPORTEDTXNISOLEVELS
Isolation RetentionDBPROP_SUPPORTEDTXNISORETAIN
Locale IdentifierDBPROP_INIT_LCID
Maximum Index SizeDBPROP_MAXINDEXSIZE
Maximum Row SizeDBPROP_MAXROWSIZE
Maximum Row Size Includes BLOBDBPROP_MAXROWSIZEINCLUDESBLOB
Maximum Tables in SELECTDBPROP_MAXTABLESINSELECT
Multiple Parameter SetsDBPROP_MULTIPLEPARAMSETS
Multiple ResultsDBPROP_MULTIPLERESULTS
Multiple Storage ObjectsDBPROP_MULTIPLESTORAGEOBJECTS
Multi-Table UpdateDBPROP_MULTITABLEUPDATE
NULL Collation OrderDBPROP_NULLCOLLATION
NULL Concatenation BehaviorDBPROP_CONCATNULLBEHAVIOR
OLE DB VersionDBPROP_PROVIDEROLEDBVER
OLE Object SupportDBPROP_OLEOBJECTS
Open Rowset SupportDBPROP_OPENROWSETSUPPORT
ORDER BY Columns in Select ListDBPROP_ORDERBYCOLUMNSINSELECT
Output Parameter AvailabilityDBPROP_OUTPUTPARAMETERAVAILABILITY
Pass By Ref AccessorsDBPROP_BYREFACCESSORS
PasswordDBPROP_AUTH_PASSWORD
Persist Security InfoDBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO
Persistent ID TypeDBPROP_PERSISTENTIDTYPE
Prepare Abort BehaviorDBPROP_PREPAREABORTBEHAVIOR
Prepare Commit BehaviorDBPROP_PREPARECOMMITBEHAVIOR
Procedure TermDBPROP_PROCEDURETERM
PromptDBPROP_INIT_PROMPT
Provider Friendly NameDBPROP_PROVIDERFRIENDLYNAME
Provider NameDBPROP_PROVIDERFILENAME
Provider VersionDBPROP_PROVIDERVER
Read-Only Data SourceDBPROP_DATASOURCEREADONLY
Rowset Conversions on CommandDBPROP_ROWSETCONVERSIONSONCOMMAND
Schema TermDBPROP_SCHEMATERM
Schema UsageDBPROP_SCHEMAUSAGE
SQL SupportDBPROP_SQLSUPPORT
Structured StorageDBPROP_STRUCTUREDSTORAGE
Subquery SupportDBPROP_SUBQUERIES
Table TermDBPROP_TABLETERM
Transaction DDLDBPROP_SUPPORTEDTXNDDL
User IDDBPROP_AUTH_USERID
User NameDBPROP_USERNAME
Window HandleDBPROP_INIT_HWND

Recordset Dynamic Properties

The following properties are added to the Properties collection of the Recordset object.

ADO Property NameOLE DB Property Name
Access OrderDBPROP_ACCESSORDER
Blocking Storage ObjectsDBPROP_BLOCKINGSTORAGEOBJECTS
Bookmark TypeDBPROP_BOOKMARKTYPE
BookmarkableDBPROP_IROWSETLOCATE
Change Inserted RowsDBPROP_CHANGEINSERTEDROWS
Column PrivilegesDBPROP_COLUMNRESTRICT
Column Set NotificationDBPROP_NOTIFYCOLUMNSET
Command Time OutDBPROP_COMMANDTIMEOUT
Defer ColumnDBPROP_DEFERRED
Delay Storage Object UpdatesDBPROP_DELAYSTORAGEOBJECTS
Fetch BackwardsDBPROP_CANFETCHBACKWARDS
Hold RowsDBPROP_CANHOLDROWS
IAccessorDBPROP_IAccessor
IColumnsInfoDBPROP_IColumnsInfo
IColumnsRowsetDBPROP_IColumnsRowset
IConnectionPointContainerDBPROP_IConnectionPointContainer
IConvertTypeDBPROP_IConvertType
Immobile RowsDBPROP_IMMOBILEROWS
IRowsetDBPROP_IRowset
IRowsetChangeDBPROP_IRowsetChange
IRowsetIdentityDBPROP_IRowsetIdentity
IRowsetInfoDBPROP_IRowsetInfo
IRowsetLocateDBPROP_IRowsestLocate
IRowsetResynch
IRowsetScrollDBPROP_IRowsetScroll
IRowsetUpdateDBPROP_IRowsetUpdate
ISequentialStreamDBPROP_ISequentialStream
ISupportErrorInfoDBPROP_ISupportErrorInfo
Literal BookmarksDBPROP_LITERALBOOKMARKS
Literal Row IdentityDBPROP_LITERALIDENTITY
Maximum Open RowsDBPROP_MAXOPENROWS
Maximum Pending RowsDBPROP_MAXPENDINGROWS
Maximum RowsDBPROP_MAXROWS
Notification GranularityDBPROP_NOTIFICATIONGRANULARITY
Notification PhasesDBPROP_NOTIFICATIONPHASES
Objects TransactedDBPROP_TRANSACTEDOBJECT
Others' Changes VisibleDBPROP_OTHERUPDATEDELETE
Others' Inserts VisibleDBPROP_OTHERINSERT
Own Changes VisibleDBPROP_OWNUPDATEDELETE
Own Inserts VisibleDBPROP_OWNINSERT
Preserve on AbortDBPROP_ABORTPRESERVE
Preserve on CommitDBPROP_COMMITPRESERVE
Quick RestartDBPROP_QUICKRESTART
Reentrant EventsDBPROP_REENTRANTEVENTS
Remove Deleted RowsDBPROP_REMOVEDELETED
Report Multiple ChangesDBPROP_REPORTMULTIPLECHANGES
Return Pending InsertsDBPROP_RETURNPENDINGINSERTS
Row Delete NotificationDBPROP_NOTIFYROWDELETE
Row First Change NotificationDBPROP_NOTIFYROWFIRSTCHANGE
Row Insert NotificationDBPROP_NOTIFYROWINSERT
Row PrivilegesDBPROP_ROWRESTRICT
Row Resynchronization NotificationDBPROP_NOTIFYROWRESYNCH
Row Threading ModelDBPROP_ROWTHREADMODEL
Row Undo Change NotificationDBPROP_NOTIFYROWUNDOCHANGE
Row Undo Delete NotificationDBPROP_NOTIFYROWUNDODELETE
Row Undo Insert NotificationDBPROP_NOTIFYROWUNDOINSERT
Row Update NotificationDBPROP_NOTIFYROWUPDATE
Rowset Fetch Position Change NotificationDBPROP_NOTIFYROWSETFETCHPOSISIONCHANGE
Rowset Release NotificationDBPROP_NOTIFYROWSETRELEASE
Scroll BackwardsDBPROP_CANSCROLLBACKWARDS
Server CursorDBPROP_SERVERCURSOR
Skip Deleted BookmarksDBPROP_BOOKMARKSKIPPED
Strong Row IdentityDBPROP_STRONGITDENTITY
Unique RowsDBPROP_UNIQUEROWS
UpdatabilityDBPROP_UPDATABILITY
Use BookmarksDBPROP_BOOKMARKS

Command Dynamic Properties

The following properties are added to the Properties collection of the Command object.

ADO Property NameOLE DB Property Name
Access OrderDBPROP_ACCESSORDER
Base PathSSPROP_STREAM_BASEPATH
Blocking Storage ObjectsDBPROP_BLOCKINGSTORAGEOBJECTS
Bookmark TypeDBPROP_BOOKMARKTYPE
BookmarkableDBPROP_IROWSETLOCATE
Change Inserted RowsDBPROP_CHANGEINSERTEDROWS
Column PrivilegesDBPROP_COLUMNRESTRICT
Column Set NotificationDBPROP_NOTIFYCOLUMNSET
Content TypeSSPROP_STREAM_CONTENTTYPE
Cursor Auto FetchSSPROP_CURSORAUTOFETCH
Defer ColumnDBPROP_DEFERRED
Defer PrepareSSPROP_DEFERPREPARE
Delay Storage Object UpdatesDBPROP_DELAYSTORAGEOBJECTS
Fetch BackwardsDBPROP_CANFETCHBACKWARDS
Hold RowsDBPROP_CANHOLDROWS
IAccessorDBPROP_IAccessor
IColumnsInfoDBPROP_IColumnsInfo
IColumnsRowsetDBPROP_IColumnsRowset
IConnectionPointContainerDBPROP_IConnectionPointContainer
IConvertTypeDBPROP_IConvertType
Immobile RowsDBPROP_IMMOBILEROWS
IRowsetDBPROP_IRowset
IRowsetChangeDBPROP_IRowsetChange
IRowsetIdentityDBPROP_IRowsetIdentity
IRowsetInfoDBPROP_IRowsetInfo
IRowsetLocateDBPROP_IRowsetLocate
IRowsetResynchDBPROP_IRowsetResynch
IRowsetScrollDBPROP_IRowsetScroll
IRowsetUpdateDBPROP_IRowsetUpdate
ISequentialStreamDBPROP_ISequentialStream
ISupportErrorInfoDBPROP_ISupportErrorInfo
Literal BookmarksDBPROP_LITERALBOOKMARKS
Literal Row IdentityDBPROP_LITERALIDENTITY
Lock ModeDBPROP_LOCKMODE
Maximum Open RowsDBPROP_MAXOPENROWS
Maximum Pending RowsDBPROP_MAXPENDINGROWS
Maximum RowsDBPROP_MAXROWS
Notification GranularityDBPROP_NOTIFICATIONGRANULARITY
Notification PhasesDBPROP_NOTIFICATIONPHASES
Objects TransactedDBPROP_TRANSACTEDOBJECT
Others' Changes VisibleDBPROP_OTHERUPDATEDELETE
Others' Inserts VisibleDBPROP_OTHERINSERT
Output Encoding PropertyDBPROP_OUTPUTENCODING
Output Stream PropertyDBPROP_OUTPUTSTREAM
Own Changes VisibleDBPROP_OWNUPDATEDELETE
Own Inserts VisibleDBPROP_OWNINSERT
Preserve on AbortDBPROP_ABORTPRESERVE
Preserve on CommitDBPROP_COMMITPRESERVE
Quick RestartDBPROP_QUICKRESTART
Reentrant EventsDBPROP_REENTRANTEVENTS
Remove Deleted RowsDBPROP_REMOVEDELETED
Report Multiple ChangesDBPROP_REPORTMULTIPLECHANGES
Return Pending InsertsDBPROP_RETURNPENDINGINSERTS
Row Delete NotificationDBPROP_NOTIFYROWDELETE
Row First Change NotificationDBPROP_NOTIFYROWFIRSTCHANGE
Row Insert NotificationDBPROP_NOTIFYROWINSERT
Row PrivilegesDBPROP_ROWRESTRICT
Row Resynchronization NotificationDBPROP_NOTIFYROWRESYNCH
Row Threading ModelDBPROP_ROWTHREADMODEL
Row Undo Change NotificationDBPROP_NOTIFYROWUNDOCHANGE
Row Undo Delete NotificationDBPROP_NOTIFYROWUNDODELETE
Row Undo Insert NotificationDBPROP_NOTIFYROWUNDOINSERT
Row Update NotificationDBPROP_NOTIFYROWUPDATE
Rowset Fetch Position Change NotificationDBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE
Rowset Release NotificationDBPROP_NOTIFYROWSETRELEASE
Scroll BackwardsDBPROP_CANSCROLLBACKWARDS
Server CursorDBPROP_SERVERCURSOR
Server Data on InsertDBPROP_SERVERDATAONINSERT
Skip Deleted BookmarksDBPROP_BOOKMARKSKIP
Strong Row IdentityDBPROP_STRONGIDENTITY
UpdatabilityDBPROP_UPDATABILITY
Use BookmarksDBPROP_BOOKMARKS
XML RootSSPROP_STREAM_XMLROOT
XSLSSPROP_STREAM_XSL

For specific implementation details and functional information about the Microsoft SQL Server OLE DB Provider, see the SQL Server Provider.

See Also