The ODBC standard is closely matched to the SQL-92 standard, and ODBC applications expect standard behavior from an ODBC driver. To make its behavior conform more closely with that defined in the ODBC standard, the Microsoft® SQL Server™ ODBC driver always uses any SQL-92 options available in the version of SQL Server with which it connects.
When the SQL Server ODBC driver connects to SQL Server version 7.0, the server detects that the client is using the ODBC driver and sets several options on. The options set on by SQL Server 7.0 are the same as those turned on by SET statements when the driver connects to SQL Server 6.5, except that SQL Server 7.0 also sets on the CONCAT_NULL_YIELDS_NULL option.
The options set by the driver when connecting to each prior version of SQL Server are:
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647
SET ANSI_DEFAULTS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET ANSI_NULL_DFLT_ON ON
SET TEXTSIZE 2147483647
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET TEXTSIZE 2147483647
SET ARITHABORT ON
The driver issues these statements itself; the ODBC application does nothing to request them. Setting these options allows ODBC applications using the driver to be more portable because the server behavior then matches the SQL-92 standard.
DB-Library-based applications generally do not turn these options on. Sites observing different behavior between ODBC or DB-Library clients when running the same SQL statement should not assume this points to a problem with the ODBC driver. They should first rerun the statement in the DB-Library environment with the same SET options as would be used by the SQL Server ODBC driver.
Because SET options can be turned on and off at any time by users and applications, developers of stored procedures and triggers should also take care to test their procedures and triggers with the SET options listed above turned both on and off. This ensures that the procedures and triggers work correctly regardless of which options a particular connection may have set on when they invoke the procedure or trigger. Triggers or stored procedures that require a particular setting for one of these options should issue a SET statement at the start of the trigger or stored procedure. This SET statement remains in effect only for the execution of the trigger or stored procedure; when the procedure or trigger ends, the original setting is restored.
The SET options used when connected to SQL Server 7.0 or SQL Server 6.5 has the net effect of setting on three more SQL-92 options than those set in the 6.0 environment: ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS. When connected to SQL Server 7.0, a fourth option, CONCAT_NULL_YIELDS_NULL, is also set on. These options can cause problems in existing stored procedures and triggers migrated from SQL Server 6.0 to either SQL Server 6.5 or 7.0. The SQL Server ODBC driver does not set these options on if AnsiNPW=NO is specified in the data source or on either SQLDriverConnect or SQLBrowseConnect.
The SQL Server ODBC driver also sets on the QUOTED_IDENTIFIER option when connected to SQL Server 6.0 or later. With this option set on, SQL statements should comply with the SQL-92 rule that character data strings be enclosed in single quotes and that only identifiers, such as table or column names, be enclosed in double quotation marks:
SELECT "au_fname"
FROM "authors"
WHERE "au_lname" = 'O''Brien'
Like the SQL-92 options noted earlier, the SQL Server ODBC driver does not turn the QUOTED_IDENTIFIER option on if QuotedID=NO is specified in the data source or on either SQLDriverConnect or SQLBrowseConnect.
To allow the driver to know the current state of SET options, ODBC applications should not use the Transact-SQL SET statement to set these options. They should only set these options using either the data source or the connection options. If the application issues SET statements, the driver can generate incorrect SQL statements.
| SQLBrowseConnect | SQLDriverConnect |