PRB: Use IS NULL in SQL Queries when Calling from Visual InterDev

• Microsoft Visual InterDev, versions 1.0, 6.0

SYMPTOMS

When you run a SQL Query from Visual InterDev that has "= NULL" the data returned may be different than what you see in ISQL/W tool (which comes with SQL Server).

CAUSE

If the Use ANSI nulls, paddings and warnings check box is selected in your ODBC data source name (DSN) settings, this behavior will appear. According to ANSI standards "= NULL" is FALSE and the following query against the PUBS database in SQL Server from Visual InterDev returns NO RECORDS.

RESOLUTION

There are two ways to overcome this problem

• Use ANSI recommended "IS NULL" instead of "= NULL" to avoid ambiguity.
  
  -or-



• From your ODBC Data Source Administrator, clear the Use ANSI nulls, paddings and warnings check box for your ODBC DSN settings.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

• From your ODBC Data Source Administrator, select the Use ANSI nulls, paddings and warnings check box for your ODBC DSN settings. Use the PUBS database in SQL Server as an example for this test.



• After you add a connection to your Visual InterDev project, double-click the Discounts table.



• Make sure the Query toolbar is visible. If it's not visible, right-click the toolbar and select Query.



• Click the SQL icon and change the query from SELECT * FROM discounts to SELECT * FROM discounts WHERE stor_id = NULL.



• Run the query by clicking the ! icon from the query toolbar.

Additional query words:

Keywords : kbVisID600 kbDSupport
Version : WINDOWS:1.0,6.0
Platform : WINDOWS
Issue type : kbprb