Heterogeneous query error in query using linked server

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  November 27, 2007 at 12:19 pm

  #200925

  Hello,

  What are the options for resolving this error? I am getting it with a query on a ColdFusion 5 page where we switched one of the tables to point to a table on a linked server. The query is just a SELECT statement joining tables on two different tables.

  The query runs fine in Query Analyzer, but on the web page is throws this error:

  ODBC Error Code = 37000 (Syntax error or access violation)

  [MERANT][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.

  I've tried various things, such as enabling the ANSI_NULLS and ANSI_WARNINGS settings on the linked server and the local server, and reversing the query to originate from the remote server. But no success.

  Is there a setting I am missing? Failing that, is there a way to do this via holding tables or temp tables?

  Thanks for any help!

  webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 27, 2007 at 12:40 pm

  #754706

  What version of sql are you using?

  Ansi_Nulls, and ANSI_WARNINGS are connection settings, so when you connect you tell sql you must tell sql what values you want these to be.

  so prior to executing your query

  Set ansi_Nulls on

  Set ansi_Warnings ON

  GO

  Select * from mylinkedserver.mydatabase.owner.mytable

  Perhaps cold fusion has default connection properties you can have this set apon connecting

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  November 27, 2007 at 12:59 pm

  #754714

  Thanks,

  The local server is SQL 2000. The linked server is SQL 2005.

  Because this is a SELECT statement, I tried making a view, but I don't know if ANSI_NULLS and ANSI_WARNINGS can be set for a view.

  Thanks again,

  webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  November 27, 2007 at 1:04 pm

  #754718

  PS - I don't see anywhere obvious where I can specify these connection options in ColdFusion - if anyone knows ColdFusion and can point it out if there is such a place to set it, please let me know. (I looked in the ODBC data source on the CF administrator.)

  Thanks again,

  webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html