Microsoft OLE DB Provider for SQL Server Overview

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=MyUserID;Password=MyPassword;"

The string consists of these keywords:

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.

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 earlier:

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'

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.

Recordset Dynamic Properties

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

Command Dynamic Properties

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

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

See Also

ConnectionString Property (ADO) Provider Property (ADO) Recordset Object (ADO)