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?

Eric Hauenstein · Accepted Answer · 2015-06-16 15:36:20Z

2

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.

answered Jun 16, 2015 at 15:36

Eric Hauenstein

2,5656 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2018-01-12 09:35:56Z

0

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

drop table if exists #TheTable

answered Jan 12, 2018 at 9:28

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges