Error when using a data reader called via ODBC SQL Server Driver in a board procedure

Question

Hi everyone, I don't understand why I'm getting various error messages in the log files of a procedure when I access our SQL Server using a DataReader. Here is a detailed description of the problem: Errors are being written to the log files even though the data reader is reading the data correctly. The error does not occur when the data reader is started on its own, but only when it is part of a procedure (within an API-call). Example 1 The exception says that “Column 'board.v_fact_bpcs_data.month_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” In fact, the column 'board.v_fact_bpcs_data.month_id' is in the group by clause: Data > {"Command":"SELECT Count(*) AS demo123987645 FROM (SELECT * FROM board.v_fact_bpcs_data WHERE board.v_fact_bpcs_data.termination_cancelation_amount <>0 and board.v_fact_bpcs_data.hw_surcharge_amount <> 0 GROUP BY Left(board.v_fact_bpcs_data.month_id, 6),Left(Cast(board.v_fact_bpcs_data.termination_retraction_type_id as VARCHAR), 2),Cast(board.v_fact_bpcs_data.provider_id as VARCHAR),Cast(board.v_fact_bpcs_data.contract_term_type_id as VARCHAR),Left(Cast(board.v_fact_bpcs_data.none_discount_benefit_id as VARCHAR), 3),case when Cast(board.v_fact_bpcs_data.offset_month_id__net_activation_cohort as VARCHAR) >50 then 50 when Cast(board.v_fact_bpcs_data.offset_month_id__net_activation_cohort as VARCHAR) <0 then NEG1 when Cast(board.v_fact_bpcs_data.offset_month_id__net_activation_cohort as VARCHAR) IS NULL then NEG1 else Cast(board.v_fact_bpcs_data.offset_month_id__net_activation_cohort as VARCHAR) end,case when cast(board.v_fact_bpcs_data.offset_end_cohort__month_id as VARCHAR) >50 then 50 when cast(board.v_fact_bpcs_data.offset_end_cohort__month_id as VARCHAR) <0 then NEG1 when cast(board.v_fact_bpcs_data.offset_end_cohort__month_id as VARCHAR) IS NULL then NEG1 else cast(board.v_fact_bpcs_data.offset_end_cohort__month_id as VARCHAR) end,board.v_fact_bpcs_data.tec_cohort_id_in,Cast(board.v_fact_bpcs_data.tec_tariffcluster_id as VARCHAR),Cast(board.v_fact_bpcs_data.tec_hardwarecluster_id as VARCHAR),Cast(board.v_fact_bpcs_data.customer_contract_state_id as VARCHAR),Left(Cast(board.v_fact_bpcs_data.termination_channel_id as VARCHAR), 2)) t", "ConnectionString":"DSN=FINBI DWH;UID=SANEGpboarddev;"} Exception > {"ControlFlow":false, "StackTrace":"System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'board.v_fact_bpcs_data.month_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteScalar() at BoardDBConnection.BoardDbCommandWrapper.ExecuteScalar()"} Example 2 The exception says that “Column 'board.v_tec_cohort.tec_cohort_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” In fact, the Column 'board.v_tec_cohort.tec_cohort_id' is not in the select list: Data > {"Command":"SELECT Count(*) AS demo123987645 FROM (SELECT * FROM board.v_tec_cohort WHERE board.v_tec_cohort.net_activation_cohort >=202001 group by Cast(board.v_tec_cohort.net_activation_cohort as VARCHAR), Cast(board.v_tec_cohort.end_cohort as VARCHAR), Cast(board.v_tec_cohort.offset_net_end_cohort as VARCHAR)) t", "ConnectionString":"DSN=FINBI DWH;UID=SANEGpboarddev;"} Exception > {"ControlFlow":false, "StackTrace":"System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'board.v_tec_cohort.tec_cohort_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteScalar() at BoardDBConnection.BoardDbCommandWrapper.ExecuteScalar()"}

Robert Barkaway · Answer

Hmm, not sure why that would still be the case, in theory ticking that should stop it running the count. If your data reader is running correctly the error isn't anything to worry about, albeit I understand that it's annoying to have it appearing in the logs. You could possibly try adding column aliases to all the items in your select list, I think that's generally what causes an invalid select when it tries to wrap the select count(*) from () around it?

Benedikt · Answer

Hi Robert, we had enabled the "disable record counting" option, but the

SELECT Count(*) is still being executed

Robert Barkaway · Answer

Hi Benedikt, it's an option on the ODBC connection itself, I don't have a screenshot to hand unfortunately but if you look at your connection config you should find the option there to disable it, should be a check box you can tick.