3

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 
4

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.