0

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 ?

Improve this question
3
  • Have you considered changing the query so that NULL values are excluded prior to aggregation? Or not sending query results as attachments through sp_send_dbmail? Or changing the way the queries work (e.g. pulling the data locally and then aggregating on it)?
    – Aaron Bertrand
    Feb 26, 2016 at 15:58
  • @sqld-_-ba Can you post the query and an explanation on which columns have null values? It would help troubleshoot.
    – JohnG
    Feb 26, 2016 at 16:16
  • @AaronBertrand, none of those were the best options given the time constraints for my task. The existing code has been working fine for years, and I just needed to send its results via dbmail, and was able to by turning off warnings at relevant part of code.
    – d-_-b
    Mar 11, 2016 at 18:55

2 Answers 2

Reset to default
1

This should remove your warning.

select name, count(employeeId) from employees group by name
select * from RemoteServer..departments
Improve this answer
-1

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 !

SQL wants Warnings to be On ONLY when executing remote queries. Thankfully in my case, the Group By (which generates the Null value Warnings) is done locally, so I 'fooled' SQL by Turning Warnings ON just before remote query.

If however, your Group By is part of the RemoteServer query, you're out of luck, and need to pull data in locally before grouping.

Improve this answer
5
  • 1
    The problem is that in your * that is part of your count(*) contains values that are null. You can Replace the * by a column that you know won't be null or a column surrounded by an isnull. Example Count(isnull(field,0)) which will make your warning disappear, When you have warnings, they are fixable.
    – JohnG
    Feb 26, 2016 at 18:03
  • 4
    I personally don't agree with the type of fix that you applied, it is a patch around the problem instead of attacking the problem.
    – JohnG
    Feb 26, 2016 at 18:06
  • However the 'problem' is in a part of the code that I didn't write, so I don't know logic enough or have time to fix it. The count(*) i showed above was a simplified part of the big code. I simply needed to make existing code work with dbmail, and was able to with the workaround.
    – d-_-b
    Mar 11, 2016 at 18:50
  • @JohnG: COUNT(*) function never gives this warning as it actually counts NULLs as well. Probably employees is a view with more aggregates.
    – wqw
    May 3, 2019 at 12:25
  • @wqw I agree, it must be another aggregate function that is causing this... he Probably simplified the query too much and listed the wrong part of the query that is causing this. Thank you for correcting me.
    – JohnG
    May 3, 2019 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.