Microsoft OLE DB Provider for SQL Server

[This is preliminary documentation and subject to change.]

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:

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 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'
 

SQL Server 2000 Features

With SQL Server 2000, ADO can use XML for Command input and retrieve results in XML stream format rather than 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

Microsoft SQL Server 2000 introduces a new 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 accessing SQL Server data stored in columns of type sql_variant.

• The MDAC 2.7 OLE DB Provider for SQL Server is the only provider that 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 above 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 containing 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 version 2.7 OLE DB Provider for SQL Server 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.
• Using MDAC 2.5 components (including ADO) against SQL Server 2000 is a supported configuration, except when accessing sql_variant data. Values stored in sql_variant columns will appear to version 2.5 components as strings. Client applications accessing sql_variant data should be upgraded to MDAC 2.7.

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.

Recordset Dynamic Properties

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

Command Dynamic Properties

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

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.

See Also

ConnectionString Property | Provider Property | Recordset Object

Comments ( )

Link to this page: //www.vb-net.com/Sql/Login/Oledb.htm