Error: Heterogeneous queries require the ANSI_Nulls and ANSI_Warnings

20 January 2003

Issue Description

When using the External Table wizard to add a SQL View based on a Linked Server table or an OPENROWSET SQL Query, the following error occurs: "[Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_Nulls and ANSI_Warnings options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

Solution

In order to add an External table of this type, the settings for the DSN used when adding the view must be modified.

To set the DSN options close the External Tables Wizard, and follow the steps below:

  1. Go to Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
  2. From the System DSN tab, select the appropriate DSN, and click 'Configure'.
  3. From the first window, leave the initial settings for Name, Description and server, then click 'Next>'.
  4. Enter A valid Login ID and Password, then click 'Next>'.
  5. From this window, check the checkboxes for both "Use ANSI quoted identifiers" and the "Use ANSI nulls, paddings, and warnings", then click 'Next>'.
  6. From this window, click the 'Finish' button.
  7. From this window, click 'Test Data Source'. The message "Tests completed successfully" should be displayed.
  8. Click 'OK' to close the DSN Setup window.
  9. Click 'OK' to close the ODBC Data Source Administrator. Now, reopen the External Table Wizard, and Re-Add the view.
Site Map