Suppress tempdb message when outputting result set

Question

Using SQLCMD, I am running a script to output to STDOUT then gziping the output. When I look at the output file, I see this warning message:

Database name 'tempdb' ignored, referencing object in tempdb.

In my script, I have a check at the start of the script to drop the temp table if it exists:

IF OBJECT_ID('tempdb..#TheTable') IS NOT NULL
BEGIN
    DROP TABLE tempdb..#TheTable
END

However - I have also SET NOCOUNT ON, but file still captures the warning message.

SQLCMD Script:
sqlcmd -i TheScript.sql -h-1 -k1 -s"," -W -u | gzip > "C:\TheOutput.gz"

Is there a way to suppress a message like that?

Answer 1

Change your if condition to the following pattern:

IF 0 < OBJECT_ID('tempdb..#TheTable')
 DROP TABLE #TheTable

This should not result in any error messages.

Answer 2

Simple clean version that works on SQL Server 2016 and above without any messages:

drop table if exists #TheTable