Search code examples
sql-servert-sqlsqlcmd

Suppress tempdb message when outputting result set

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?

Solution

  • 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.