Heterogeneous query error in query using linked server

Question

Heterogeneous query error in query using linked server

webrunner

SSC-Dedicated

Points: 31863
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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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: 31863 More actions · Answer 2

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: 31863 More actions · Answer 3

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