August 8, 2018 at 6:29 am
Hi experts,
The stored procedure executes only with the Warning. But when it executes from the user's side, it raises this error. Error: Invalid Custom SQL query. Warning: Null value is eliminated by an aggregate or other SET operation. Can anyone tell me if it is possible (and how) to avoid the error at the query level?
Thanks,
Yoha
August 8, 2018 at 6:32 am
keneangbu - Wednesday, August 8, 2018 6:29 AMHi experts,
The stored procedure executes only with the Warning. But when it executes from the user's side, it raises this error. Error: Invalid Custom SQL query. Warning: Null value is eliminated by an aggregate or other SET operation. Can anyone tell me if it is possible (and how) to avoid the error at the query level?
Thanks,
Yoha
Add this to the start of the stored procedure:SET ANSI_WARNINGS OFF
August 8, 2018 at 6:46 am
I tried to add SET ANSI_WARNINGS OFF, but executing the stored procedure raises this. "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics. Enablethese options and then reissue your query." It expects it be set at the connection level.
August 8, 2018 at 8:34 am
What are you using to run the query? That doesn't seems to be a SQL Server error, it's more like something to do with the client.
August 8, 2018 at 8:41 am
keneangbu - Wednesday, August 8, 2018 6:46 AMI tried to add SET A, but executing the stored procedure raises this. "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics. Enablethese options and then reissue your query." It expects it be set at the connection level.
There is a link to this same issue here:
https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua
https://www.sqlservercentral.com/Forums/Topic1517643-3077-1.aspx
August 8, 2018 at 8:45 am
keneangbu - Wednesday, August 8, 2018 6:46 AMI tried to add SET A, but executing the stored procedure raises this. "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics. Enablethese options and then reissue your query." It expects it be set at the connection level.
You sure it has to be set in the connection? You might want to try creating the stored procedure with the setting - in the script, set the option prior to the create procedure line.
Sue
August 8, 2018 at 8:57 am
Luis Cazares - Wednesday, August 8, 2018 8:34 AMWhat are you using to run the query? That doesn't seems to be a SQL Server error, it's more like something to do with the client.
I am using SSMS to run the query. Initially, only the warning was raising; and after considering the suggestion, it raises the error. This error ~ Error: Invalid Custom SQL query. Warning: Null value is eliminated by an aggregate or other SET operation. is thrown when I execute it from the user page.
Thanks,
August 8, 2018 at 8:58 am
Jonathan AC Roberts - Wednesday, August 8, 2018 8:41 AMkeneangbu - Wednesday, August 8, 2018 6:46 AMI tried to add SET A, but executing the stored procedure raises this. "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics. Enablethese options and then reissue your query." It expects it be set at the connection level.There is a link to this same issue here:
https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua
https://www.sqlservercentral.com/Forums/Topic1517643-3077-1.aspx
Much appreciated. Let me go through the link.
August 8, 2018 at 9:08 am
Jonathan AC Roberts - Wednesday, August 8, 2018 8:41 AMkeneangbu - Wednesday, August 8, 2018 6:46 AMI tried to add SET A, but executing the stored procedure raises this. "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics. Enablethese options and then reissue your query." It expects it be set at the connection level.There is a link to this same issue here:
https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua
https://www.sqlservercentral.com/Forums/Topic1517643-3077-1.aspx
The suggestion on the link are regarding avoiding the warnings by making the NULL values to 0. But as my query calculates MINIMUM, I am afraid that it may erroneously take the 0 as the minimum value in the case that it would be the least. I was wondering if there is other way (other than setting the ANSI_WARNINGS OFF) of letting the application ignore the warnings... so that it won't throw the 'Invalid Custom SQL query' error.
August 8, 2018 at 9:15 am
You shouldn't be focusing on the warning. That's not causing the error.
The error might be caused by some statement with a functionality not supported by a client. It could be as simple as using a CTE or APPLY or something more specific to SQL Server that's not supported. We can't help you without seeing the query and knowing what's the client.
August 8, 2018 at 10:05 am
Luis Cazares - Wednesday, August 8, 2018 9:15 AMYou shouldn't be focusing on the warning. That's not causing the error.
The error might be caused by some statement with a functionality not supported by a client. It could be as simple as using a CTE or APPLY or something more specific to SQL Server that's not supported. We can't help you without seeing the query and knowing what's the client.
Okay Luis. I will try to refine and share the code. Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply