Post reply

sp_send_dbmail to execute a query and output HTML format

  • May 17, 2010 at 11:01 am

    #156959

    Hi. I am using sp_send_dbmail to run a query and format the output as HTML. The output is fine except that in the email between the body and the query results are a bunch of hyphens (below), why? It's as if the hyphens are column headers for the output, how do I get rid of them? Thanks for your help.

    The following client(s) have a policy end date next month.

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- Client 6031 ABC Corp. has an end date of Jun 25, 2010.

    My query output is the string above with client number, name and date changing for each record retrieved.

    '''Client '' + rtrim(ci.clientnumber) + '' '' + rtrim(ci.dba) + '' has an end date of '' + convert(varchar(20),endDate,107) + ''. ''' +

    The SQL to send the email is:

    set @Body = 'The following client(s) have a policy end date next month.

    '

    EXEC msdb.dbo.sp_send_dbmail

    @Recipients=@Recipients,

    @Subject=@Subject,

    @Profile_name=@Profile_name,

    @attach_query_result_as_file=0,

    @execute_query_database=@dbname,

    @body=@Body,

    @query=@Query,

    @body_format='HTML'

  • May 17, 2010 at 11:05 am

    #1168365

    Sorry to bother, I went to BOL and found a parameter @query_result_header. That took care of the problem.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply