Heterogeneous Queries

• DanG

  SSC Eights!

  Points: 951

  More actions

  November 7, 2003 at 7:21 am

  #83190

  Hello,

  I have 2 separate SQL Server instances on one server box. In a stored procedure in SQL Server 2 I am trying to perform a select from a table in SQL Server 1. I get the following error calling the SP from QA:

  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 both server instances, I have gone into properties/Connections tab and set ANSI warning and ANSI nulls to checked.

  Same error.

  I am able to take that query and run it from QA with no problem. It just won't run in the SP.

  Anything I am missing?

  Thanks,

  Dan

• philcart

  SSC-Forever

  Points: 47797

  More actions

  November 9, 2003 at 4:12 pm

  #481368

  From BOL,

  quote:

  ---

  For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

  ---

  Just use,

  SET ANSI_NULLS ON

  SET ANSI_WARNINGS ON

  at the start of your stored procedure. This way they'll always be set when you create the SP.

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface

• dreckner

  Newbie

  Points: 6

  More actions

  November 10, 2003 at 5:34 am

  #481397

  I had a similiar problem on a SQL running on two different boxes. The problem turned out to be that DTC Service was running under the Local System account instead of Domain User account. Changing the accounts to the same Domain User account resolved the problem.

  Hope that helps.

• jordanac

  Hall of Fame

  Points: 3713

  More actions

  November 10, 2003 at 6:01 am

  #481400

  Hi,

  I had this problem as well and the solution was simple. Set the ANSI settings and create the proc from Query Analyser not from EM.

  If it aint broke don't fix it!

  
  Andy.