Heterogeneous Queries

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