The Microsoft® SQL Server OLE DB Provider, 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.
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 | User authentication mode. This can be set to True or False. The default value is False. If this property is set to True then SQLOLEDB uses Microsoft® Windows NT® Authentication Mode to authorize user access to the SQL Server database specified by the Location and Datasource property values. If this property is set to False then SQLOLEDB uses Mixed Mode to authorize user access to the SQL Server database. The SQL Server login and password are specified in the User Id and Password properties. |
| Current Language | A SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on the SQL Server or opening the connection will fail. |
| Network Address | The network address of the SQL Server specified by the Location property. |
| Network Library | The name of the Net-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 | SQL Server stored procedure use. Defines the use of SQL Server temporary stored procedures to when Commands are prepared. |
| Auto Translate | OEM/ANSI character conversion. This property can be set to True or False. The default value is True. If this property is set to True then SQLOLEDB performs OEM/ANSI character conversion when multibyte character strings are retrieved from, or sent to, the SQL Server. If this property is set to False then SQLOLEDB does not perform OEM/ANSI character conversion on multibyte character string data. |
| Packet Size | 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 | The client application name. |
| Workstation ID | A string identifying the workstation. |
Command Object Usage
SQLOLEDB accepts an amalgam of ODBC, ANSI, and SQL Server-specific Transact-SQL as valid syntax. For example, the following SQL statement uses an ODBC SQL escape sequence to specify the LCASE string function:
SELECT customerid={fn LCASE(CustomerID)} FROM Customers
LCASE returns a character string, converting all uppercase characters to their lowercase equivalents. The ANSI SQL string function LOWER performs the same operation, so the following SQL statement is an ANSI equivalent to the ODBC statement presented above:
SELECT customerid=LOWER(CustomerID) FROM Customers
SQLOLEDB successfully processes either form of the statement when specified as text for a command.
Stored Procedures
When executing an SQL Server stored procedure using an 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 preferred command text over the Transact-SQL form:
ODBC SQL
{call SalesByCategory('Produce', '1995')}
Transact-SQL
EXECUTE SalesByCategory 'Produce', '1995'
Recordset Behavior
SQLOLEDB cannot use SQL Server cursors to support the multiple-rowsets 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 rowset using a command containing an SQL ORDER BY clause can fail.
See Also For specific implementation details and functional information about the Microsoft SQL Server OLE DB Provider, consult the Microsoft SQL Server OLE DB Provider documentation in the Data Access SDK.