Null value SQL Warning becoming error with PHP ODBC Prepare

2
votes

I currently have a SQL query that checks whether 80% of the data for a certain asset (asset I'm checking is traffic counters) is available, and depending on the results, returns the result or null. (Please note that the number of data points varies between different traffic counters, thus a variable COUNT(1) is required).

(case when COUNT(a.hour_00)>0.8*COUNT(1) then Sum(a.hour_00) else null end) AS hour_00

This results in the following warning in SQL Server:

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

This doesn't prevent the query running within SQL Server, nor does it prevent it running when I 'normally' call it in PHP using odbc_exec. However, when I try turn the query into a parametrized one using odbc_prepare and odbc_execute, I get the following error.

PHP Warning: odbc_execute() [/phpmanual/function.odbc-execute.html]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation., SQL state 01003 in SQLExecute in ...

I am unsure why this warning is now causing an error. I believe there should be a way to prevent the warning from causing an error. I have tried SET ANSI_WARNINGS OFF, but that results in the following error

PHP Warning: odbc_execute() [/phpmanual/function.odbc-execute.html]: SQL error: [Microsoft][ODBC SQL Server Driver]Cursor type changed, SQL state 01S02 in SQLExecute in

1
phpsql-serverodbc
Did you ever find a solution to this problem? I am running into a similar situation on my php page. - Purple Lady
Sort of. I called SET ANSI_WARNINGS OFF; in a separate sql statement before calling the query. that allowed me to then call the prepared query. - Pretty Cool
I see. I basically did the PHP equivalent - I put error_reporting(E_COMPILE_ERROR | E_RECOVERABLE_ERROR | E_ERROR | E_CORE_ERROR); into my script to filter out the ODBC warnings and it ran just fine. Thank you for responding! - Purple Lady

1 Answers

0
votes

In my experience this is a misreported error.

You have another error in your script, which is causing the script to fail - however the error message returned is the text of the aggregate-elminated warning, instead of the real error.

I had this problem in a VBA/EXcel report; but I bet its the same thing.