SQLCMD ERRORLEVEL -m

sainiak
sainiak used Ask the Experts™
on
I am running an SQLCMD command from DOS against a SQL Server 2008 database that occasionally outputs the following message at the end of the data:
"WARNING: Null value is eliminated by an aggregate or other SET operation."

I do not control the DB and do not want to fix the error, I just want to suppress the warning message.
I know the default ERRORLEVEL is 0.
If I add the output parameter -m1 this warning disappears.  This is what I want because the output is read by a different program and does not expect messages at the end.

But it raises a few other questions for me:
1.  What ERRORLEVEL values exist in SQL Server?
2.  Do they mean anything?
3.  Is there a maximum (if I want to suppress any/all errors)?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Did you really use -m1 parameter? I would guess this warning would need -m11.

We should distinguish between DOS ERRORLEVEL and SQL error severity level. Even when -m SQLCMD parameter is described as "-m error_level" in documentation it handles error severity level.

Severity level values represent the error importance (or severity). The higher value means more serious error and the values are listed and described e.g. here: https://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/

You may also issue the following query to see all existing errors in SQL engine:
SELECT * FROM master.dbo.sysmessages ORDER BY severity DESC

Open in new window

If you would like to play with error generation then you may use RAISEERROR() function call:
    RAISERROR ('RAISEERROR example.', -- Message text.  
               10, -- Severity.  
               1 -- State.  
               );  

Open in new window

I would not recommend to suppress errors just warnings. Your should know about all possible errors.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> "WARNING: Null value is eliminated by an aggregate or other SET operation."

If you want to just suppress the above WARNING message, then you can add SET ANSI_WARNINGS to OFF at the beginning of your script..
SET ANSI_WARNINGS OFF
GO

Open in new window


>> 1.  What ERRORLEVEL values exist in SQL Server?
Clarified above by pcelba

>>  2.  Do they mean anything?
As clarified by pcelba above, description column should show the meaning of the individual errors.
SELECT * FROM master.dbo.sysmessages ORDER BY severity DESC

Open in new window


3.  Is there a maximum (if I want to suppress any/all errors)?

Personally I wouldn't recommend suppressing any errors as it might lead to several issues in terms of code maintenance or issue troubleshooting later on..
If you have any specific set of errors, then we can handle it better and then think about suppressing any messages if arise..

Author

Commented:
Thanks for the feedback.  I am going to verify the -m1 suppressed the error, or if it needed -m11.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I confirmed the -m1 suppressed the warning described above.
So I will use -m1 to suppress this warning but will return more severe warnings/errors.

And it looks like -m25 is the max, since anything greater than 25 returns no data with this message (I used -m26 here):
"Sqlcmd: '26': Unexpected argument. Argument has to be a number greater than or equal to -1."

Author

Commented:
I appreciate the detailed explanations!  I have to troubleshoot DB queries once every few years and I am always a bit rusty.
Glad to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial