Turn off ANSI_WARNINGS for heterogenous queries

• sqld-_-ba

  SSCarpal Tunnel

  Points: 4365

  More actions

  February 26, 2016 at 8:52 am

  #308423

  We have a Stored Procedure with many linked-server queries. When executing it displays a Warning:

  Warning: Null value is eliminated by an aggregate or other SET operation.

  To suppress this, I tried :

  SET ANSI_WARNINGS OFF

  But that threw this error :

  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.

  All articles I looked at mention setting ANSI_WARNINGS to ON to get it to work.

  But I Absolutely do not want to see those Warnings, because I'm sending query results as attachment in sp_send_dbmail which has that Warning message right at the top of attachment.

  Is it possible to suppress the warnings for heterogenous queries ?

• Jeff Moden

  SSC Guru

  Points: 1004561

  More actions

  February 26, 2016 at 4:55 pm

  #1861594

  sqld-_-ba (2/26/2016)

  ---

  We have a Stored Procedure with many linked-server queries. When executing it displays a Warning:

  Warning: Null value is eliminated by an aggregate or other SET operation.

  To suppress this, I tried :

  SET ANSI_WARNINGS OFF

  But that threw this error :

  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.

  All articles I looked at mention setting ANSI_WARNINGS to ON to get it to work.

  But I Absolutely do not want to see those Warnings, because I'm sending query results as attachment in sp_send_dbmail which has that Warning message right at the top of attachment.

  Is it possible to suppress the warnings for heterogenous queries ?

  You're chasing the wrong problem. Post the code that is generating this error and lets have a go at it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sqld-_-ba

  SSCarpal Tunnel

  Points: 4365

  More actions

  March 11, 2016 at 11:46 am

  #1864653

  Nevermind, found a workaround. The code was as such:

  select name, count(*) from employees group by name

  select * from RemoteServer..departments

  I modified it to :

  SET ANSI_WARNINGS OFF

  select name, count(*) from employees group by name

  SET ANSI_WARNINGS ON

  select * from RemoteServer..departments

  and it worked !

  It was only on Remote queries that ANSI_WARNINGS needed to be on, not on Local queries.