INF: Differences in SQL Behavior Between ODBC and ISQL

ID: Q135533

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5
Microsoft Open Database Connectivity, version 2.5

SUMMARY

When you use the ODBC drivers that ship with SQL Server version 6.0 or later, SQL commands sometimes may appear to work differently in ODBC clients from how they work in either DBLIB clients or the SQL Server client utilities, such as isql, ISQL/w, and the Enterprise Manager query analyzer window. In most cases, these differences are simply due to options set by the Microsoft ODBC SQL Server using the Transact-SQL SET command, and if the same options are set in the DBLIB clients they exhibit the same behavior as the ODBC client.

MORE INFORMATION

The ODBC standard is closely matched to the ANSI SQL standard, and ODBC applications expect the behavior of an ODBC driver to match the standard behavior. In order to make its behavior conform more closely with that defined in the ODBC standard, the Microsoft ODBC SQL Server driver version 2.50.0121 always issues the following Transact-SQL SET commands:

When you connect to SQL Server version 4.21a:


   SET TEXTSIZE 2147483647
   SET ARITHABORT ON

When you connect to SQL Server version 6.0:


   SET ANSI_NULL_DFLT_ON ON
   SET TEXTSIZE 2147483647
   SET QUOTED_IDENTIFIER ON
   SET ARITHABORT ON

The 2.65.0201 driver which ships with SQL Server 6.5 will set the same options when connecting to version 4.21a and 6.0 servers. When the 2.65.0201 driver connects to a SQL Server 6.5 server, it will set the following options:


   SET QUOTED_IDENTIFIER ON
   SET TEXTSIZE 2147483647
   SET ANSI_DEFAULTS ON
   SET CURSOR_CLOSE_ON_COMMIT OFF
   SET IMPLICIT_TRANSACTIONS OFF

The Microsoft ODBC SQL Server driver issues these commands itself; the ODBC application does nothing to request them. Setting these options allows ODBC applications using the driver to be more portable, because the driver's behavior then matches that defined by the standard. This is mentioned in the "What's New" section for the Microsoft ODBC SQL Server driver in the SQL Server version 6.0 Books Online,and also in the Microsoft SQL Server driver help file for SQL Server 6.0, DRVSSRVR.HLP.

ODBC applications should not change these SET options. Not only does the driver depend on these to comply with the ODBC standard behavior, it also assumes that these options are set when it generates SQL commands it sends to the server. For example, the driver may put double quotation marks around object names in its generated SQL statements to handle objects created using quoted identifiers, and whose names are only legal if enclosed in quotation marks. If the application has turned off QUOTED_IDENTIFIERS, then these generated SQL commands will get syntax errors from the server.

Before assuming that there is a true difference in behavior between the Microsoft ODBC SQL Server driver and DBLIB applications, you should run the DBLIB test with the same SET options as you use with the ODBC driver.

The 2.65.0240 driver that ships with SQL Server 6.5 Service Pack 2 gives users greater control over what specific ANSI options are set by the driver. When a user defines a data source using the 2.65.0240 driver, there is a check box for turning on or off the setting of QUOTED_IDENTIFIERS, and another for turning on or off ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS. Users can also set these options on or off when creating a data source through SQLConfigDataSource. In addition, the 2.65.0240 driver supports driver specific connection options to control these at connect time. See the SQL Server 6.5 Service Pack 2 Readme.txt for more details.

Additional query words: sql6


Keywords          : kbinterop kbprg SSrvISQL SSrvPrg 
Version           : 2.50.0121 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 14, 1999