How can I remove info message and column lines from SQL query result file?

Question

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 <[email protected]>' ,
    @subject = 'SQL Instance Info' ,
    @recipients = '[email protected];[email protected]',
    @copy_recipients= '[email protected];[email protected]',
    @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 

Answer 1

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

Answer 2

Scott's answer seems like the only way to do it in sql. I found a few answers which support him.

https://stackoverflow.com/a/2362304/447156

https://dba.stackexchange.com/a/76623/1611

But outside of sql, when you use sqlcmd, you can delete that line using with findstr command.

|findstr /v /c:"---"

/v : Prints only lines that do not contain a match.
/c: string : Uses specified text as a literal search string.

This will help you to not writing header names as hardcoded as well.

But remember, this will delete all the lines where your first columns starts with ---. I assume your "data" does not contain it.