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

• 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

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

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

   szSqlState = "01003", *pfNativeError = 8153,
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
               Warning, null value eliminated from aggregate."

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

   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."

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

      SQLSetConnectOption(hstmt,SQL_COPT_SS_ANSI_NPW,SQL_AD_OFF);