Warnings and SP_recompile messages from SP execution - How to not log those messages

Question

Warnings and SP_recompile messages from SP execution - How to not log those messages

river1

SSC-Insane

Points: 24304
More actions
January 19, 2017 at 8:17 am

#401427

Dear all,
I have a stored procedure that is returning messages other then the only one I want ( the value 1)
As per below, this messages are result from SP_recompile that I have Inside the SP and from some warnings.
How can I request the SP to not send those messages (specially the ones related with the sp_recompile option)
Below the messages:
Object 'bmb_ms.usp_BMBBI_audit_procedure_result_update' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_poc_update' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_machine_counter_estim_insert' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_machine_counter_estim_control_insert' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_last_ingredient_sale_insertupdate' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_last_ingredient_sale_oper_insertupdate' was successfully marked for recompilation.
Object 'bmb_ms.usp_BMBBI_prl_estimated_monthly_cup_sales' was successfully marked for recompilation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
1
Thank you,
Pedro

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

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

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 1

I do not recommend this, but you can use the SET ANSI_WARNINGS command to turn them off. By default they are on. Please carefully read the documentation to ensure you're not going to hit issues if you decide to turn these off. However, that's how you would do it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

I agree with Grant. Suppressing these messages isn't the way to go. Fixing the code that causes them is. There's almost never a need to mark stored procs for recompile and fixing your aggregations or related criteria will fix the Null Aggregation errors. In fact, you might want to dig into the Null Aggregation errors because they might actually be telling you that something is wrong.

--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)