Sql-server – How to remove info message and column lines from SQL query result file

sql servert-sql

For example, I have a query like below and I want to remove info/warning messages (like 1 rows affected) and header's lines (like -------------) rows from SQL output file. I can disable info/warning messages on SQLCMD with -m 1 -W parameters but I want to do this with T-SQL. How can I do that?

DECLARE @tab char(1) = CHAR(9)
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'sqldba',
    @from_address = 'DISPLAYNAME <hostname@domain.com>' ,
    @subject = 'SQL Instance Info' ,
    @recipients = 'to1@domain.com;to2@domain.com',
    @copy_recipients= 'cc1@domain.com;cc2@domain.com',
    @body = 'Results on attachment',
    @body_format = 'HTML',
    @query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  exc.local_tcp_port as ''Port''
    FROM sys.dm_exec_connections exc
    WHERE  exc.session_id = @@SPID
    ',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'InstanceInfo.csv',
    @query_result_separator=@tab,
    @query_result_width =32767,
    @query_result_no_padding=1,
    @exclude_query_output=1,
    @query_result_header=1

Results seems like below:

Instance_Name   IP_Address  Port
-------------   ----------  ---- 
HOSTNAME    123.456.789.0   1433         
(1 rows affected)

It should be:

Instance_Name   IP_Address  Port
HOSTNAME    123.456.789.0   1433

Best Answer

I played around with your example and was able to hardcode the expected column names and used UNION ALL against the actual result set. I did have to convert the port column to varchar(10) to get the UNION ALL to work. I then added the @query_result_header = 0 parameter to eliminate the query header. I never did get the 'Rows Affected' message in my example because I was using the SET NOCOUNT like you did.

DECLARE @tab CHAR(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
    ,@recipients = '<recipients>'
    ,@subject = 'query'
    ,@body_format = 'TEXT'
    ,@query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT ''Instance_Name'' as Instance_Name, ''IP_Address'' as IP_Address, ''Port'' as Port
    UNION ALL
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  convert(varchar(10),exc.local_tcp_port) as ''Port''
    FROM sys.dm_exec_connections exc
    --WHERE  exc.session_id = @@SPID
    '
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'InstanceInfo.txt'
    ,@query_result_separator = @tab
    ,@query_result_width = 32767
    ,@query_result_no_padding = 1
    ,@exclude_query_output = 1
    ,@query_result_header = 0
Related Question