INF: ODBC ANSI Upgrade Changes From SQL Server 6.0 to 6.5

This article was previously published under Q149921
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
This article discusses changes in behavior that are introduced when youupgrade from Microsoft SQL Server version 6.0 and its associated MicrosoftODBC drivers to SQL Server 6.5 and its associated Microsoft driver. If youare using non-Microsoft ODBC drivers, consult the documentation from yourdriver vendor.
More information
When a version 2.65.0201 or later ODBC driver connects to a 6.50 or laterSQL Server, it sets the following options with SQL Server:
   SET TEXTSIZE 2147483647   SET ANSI_DEFAULTS ON   SET CURSOR_CLOSE_ON_COMMIT OFF   SET IMPLICIT_TRANSACTIONS OFF				

The net effect of setting these options is that when the 2.65.0201 or laterdrivers are connected with a version 6.50 or later server, they willoperate with three ANSI options turned on that were not turned on inearlier environments. The settings that will cause changes in behavior fromthose observed in the SQL Server 6.0 drivers are listed below. Additionalinformation on their effects can be found in the SQL Server 6.5 "BooksOnline" section "What's New In 6.5," Part 4, What's New for Transact-SQL,Statements and Functions, Set Statement.

Also note that these behaviors will not show up in DB-library basedapplications such as ISQL/w, Enterprise Manager, isql, and so forth, unlessthe user has also issued the settings listed above.

SET ANSI_NULLS ON

With this setting active, SQL statements cannot use the = or <> operatorsto compare for nulls; they must use IS NULL or IS NOT NULL. For example, inthe following ISQL/w script:
create table TestNull (cola int NOT NULL, colb char(3) NULL)goinsert into TestNull values (1,'aaa')insert into TestNull(cola) values (2)goSET ANSI_NULLS OFFgoselect * from TestNull where colb = NULLgoSET ANSI_NULLS ONgoselect * from TestNull where colb = NULLgo				

the first select returns one row with colb = NULL, the second selectreturns no rows. This can also affect commands in stored procedures iftheir WHERE clauses attempt = or <> comparisons using parameters passed inwith null values. See the What's New section in the SQL Server 6.5 "BooksOnline" for further information.

SET ANSI_PADDING ON

When enabled, the ANSI_PADDING causes varchar and varbinary values to bepadded with spaces or nulls. It also affects fixed-length datatypes such aschar or int. If the user specifies that columns of fixed-length datatypes(char, int, tinyint, smallint) can be NULL, ANSI_PADDING causes thesecolumns to be padded in the event of a null value.

If a table is created while ANSI_PADDING is enabled, the table column willexhibit the padding behavior. Disabling ANSI_PADDING has no effect onexisting columns.

SET ANSI_WARNINGS ON

This setting will cause the Microsoft ODBC driver to generate messages innew conditions. One condition is that if an aggregate function encountersnulls it will generate a warning. For example, the following commandreferencing the table created above:
   SQLExecDirect(hstmt,                 "select max(colb) from TestNull)",                 SQL_NTS);				

will return SQL_SUCCESS_WITH_INFO and a call to SQLError will return:
szSqlState = "01003", *pfNativeError = 8153,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Warning, null value eliminated from aggregate."

Another example is that an insert or update that attempts to put in a valuewhose length is larger than that allowed by the column will fail, whereaswith ANSI_WARNINGS set off, the operation would go in after truncating thedata. For example, with the TestNull table defined above and the 6.5driver, the following:
   SQLExecDirect(hstmt,                 "insert into TestNull values (3, 'abcd')",                 SQL_NTS);				

will return SQL_ERROR, and call to SQLError until it returnsSQL_NO_DATA_FOUND will return:
szSqlState = "22001", *pfNativeError = 8152,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Column 'colb' of table 'pubs.dbo.TestNull' cannot
accept 4 bytes (3 max)."
szSqlState = "01000", *pfNativeError = 3621,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Command has been aborted."

The 2.65.0240 driver that comes with SQL Server 6.5 Service Pack 2 adds theability to control these ANSI settings. With this driver, you have severaloptions for controlling these settings:
  1. The data source dialog box has a new check box that is displayed after you click the options button. The name of the new check box is 'Use ANSI Nulls, Padding, and Warnings'. If the check box is on the driver will use these three options, if it is off the driver will not use them.
  2. You can specify the keywords 'AnsiNPW=YES' or 'AnsiNPW=NO' on a call to SQLConfigDataSource when configuring a data source for the 2.65.0240 driver.
  3. An application can specify the keywords 'AnsiNPW=YES' or 'AnsiNPW=NO' on a call to SQLDriverConnect when connecting through the 2.65.0240 driver.
  4. An application can call SQLSetConnectOption prior to connecting as follows (assuming the programmer has copied over the file odbcss.h from the Service Pack 2 directory):
          SQLSetConnectOption(hstmt,SQL_COPT_SS_ANSI_NPW,SQL_AD_ON);  						

    -or-
          SQLSetConnectOption(hstmt,SQL_COPT_SS_ANSI_NPW,SQL_AD_OFF);    						
Properties

Article ID: 149921 - Last Review: 10/26/2013 02:59:00 - Revision: 4.0

  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft Open Database Connectivity 4.21
  • kbnosurvey kbarchive kbinterop kbsetup KB149921
Feedback