June 11, 2007 at 9:59 am
Hello,
We are trying to resolve this problem:
1. We have a view on SQL Server 2005 that is based on a query joining a local table and a table on a linked server in SQL Server 2000.
2. A query from MySQL is getting this error when trying to query the view:
Could not execute , Server message number=7405 severity=16 state=1 line=1 server=ANGELDB text=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.
In order to resolve the error, I have seen the suggestion to set ANSI_NULLS and ANSI_WARNINGS to ON for the whole DB server.
I read this article about ANSI_NULLS: Understanding the Implications of ANSI SQL92 SET Options but it didn't explain ANSI_WARNINGS (I looked for the follow-up article promised by the author but did not find it).
My questions are:
A. Is there a more limited way to set these options without having to set them at the server or the database? I have not found a way to set them for a view.
B. If I have to set them at the server, what are the caveats for ANSI_WARNINGS?
C. Is it better to have a procedure that is scheduled to put the view data into a local SQL Server 2005 table and then point the view to that table so the MySQL query won't have to go against the view with the heterogeneous query?
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
June 11, 2007 at 1:36 pm
PS - I ran DBCC USEROPTIONS in a database on our SQL Server 2005 server and got these results for the ANSI_NULLS and ANSI_WARNINGS options:
Set Option Value
ansi_warnings SET
ansi_nulls SET
Does that mean that these options are already turned on at the server level? Or just at the database level? Either way, though, if the view in question (the one listed in number 1 in my original post above) is in this database, then error 7405 should not happen, is that right? (At least not due to these options not being set.)
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
June 11, 2007 at 1:42 pm
I think what it is telling you is that you have to set them for the query explicitly (over rides server settings).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply