November 2, 2005 at 2:11 pm
I have a stored procedure that I can not get to save. When I try and save it, I get the following error :
7405:Heterogenous queries require the ANSI_NULLS & ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options & then reissue your query.
If I comment out a portion of the query that does a select to a temp table between a linked table and another temp table, it will let me save it.
Any ideas?
Thanks in advance.
Dorothy
November 2, 2005 at 2:46 pm
Set the options inside your proc:
create proc abc
as
SET ANSI_NULLS OFF ---or on
SET ANSI_WARNINGS OFF --or on
...
or
figure out what about that internal query is requiring those options and be more explicit in your select statement.
November 3, 2005 at 1:52 am
If you are using ALTER PROCEDURE, change to DROP PROCEDURE | CREATE PROCEDURE, and see if this cures the problem.
Andy
November 3, 2005 at 4:48 am
Certain features in SQL, such as linked servers and indexed views, as well as indices on computed columns, require certain SET options to be set a particular wasy. This is to ensure a consistent application of rules such as what to do with nulls when updating and reading from indices. The heterogeneous message is referring to your linked server table reference - ensure you have the two options set on your connection and all will be well..
You can set certain options to be defaulted at a server level and at a database level to avoid setting them on each connection.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply