Error 7405 when trying to save a stored proc

  • 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

     

     

     

  • 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.

     

  • If you are using ALTER PROCEDURE, change to DROP PROCEDURE | CREATE PROCEDURE, and see if this cures the problem.

    Andy

  • 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