Turn off ANSI_WARNINGS for heterogenous queries

Question

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 ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

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 · Answer 2

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.