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

ID: Q149921

The information in this article applies to:

Microsoft SQL Server version 6.5
Microsoft Open Database Connectivity

SUMMARY

This article discusses changes in behavior that are introduced when you upgrade from Microsoft SQL Server version 6.0 and its associated Microsoft ODBC drivers to SQL Server 6.5 and its associated Microsoft driver. If you are using non-Microsoft ODBC drivers, consult the documentation from your driver vendor.

MORE INFORMATION

When a version 2.65.0201 or later ODBC driver connects to a 6.50 or later SQL 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 later drivers are connected with a version 6.50 or later server, they will operate with three ANSI options turned on that were not turned on in earlier environments. The settings that will cause changes in behavior from those observed in the SQL Server 6.0 drivers are listed below. Additional information on their effects can be found in the SQL Server 6.5 "Books Online" 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 based applications such as ISQL/w, Enterprise Manager, isql, and so forth, unless the user has also issued the settings listed above.

SET ANSI_NULLS ON

With this setting active, SQL statements cannot use the = or <> operators to compare for nulls; they must use IS NULL or IS NOT NULL. For example, in the following ISQL/w script:


create table TestNull (cola int NOT NULL, colb char(3) NULL)
go
insert into TestNull values (1,'aaa')
insert into TestNull(cola) values (2)
go
SET ANSI_NULLS OFF
go
select * from TestNull where colb = NULL
go
SET ANSI_NULLS ON
go
select * from TestNull where colb = NULL
go

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

SET ANSI_PADDING ON

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

If a table is created while ANSI_PADDING is enabled, the table column will exhibit the padding behavior. Disabling ANSI_PADDING has no effect on existing columns.

SET ANSI_WARNINGS ON

This setting will cause the Microsoft ODBC driver to generate messages in new conditions. One condition is that if an aggregate function encounters nulls it will generate a warning. For example, the following command referencing 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 value whose length is larger than that allowed by the column will fail, whereas with ANSI_WARNINGS set off, the operation would go in after truncating the data. For example, with the TestNull table defined above and the 6.5 driver, the following:


   SQLExecDirect(hstmt,
                 "insert into TestNull values (3, 'abcd')",
                 SQL_NTS);

will return SQL_ERROR, and call to SQLError until it returns SQL_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 the ability to control these ANSI settings. With this driver, you have several options for controlling these settings:

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.

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.

An application can specify the keywords 'AnsiNPW=YES' or 'AnsiNPW=NO' on a call to SQLDriverConnect when connecting through the 2.65.0240 driver.

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);  
   
```


Keywords          : kbinterop kbsetup ODBC SSrvInst 
Version           : 2.65.0201 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 25, 1999