Post reply

Send email through sp_send_dbmail - dynamic sql - different results everytime

  • December 21, 2015 at 3:20 pm

    #323218

    Hi,

    I have a stored procedure which runs dynamic sql and sends results out in an email format. I have the results of dynamic sql returning different results every time. I want to capture this results and send it in an email from SQL server. I have seen implementations where the results is predefined or returns a fixed output.

    There are implementations using OPENROWSET, but this feature is not allowed in the production database.

    Are there any other options where the results can be captured and send in the body of an email?

    Thanks in advance.

  • December 22, 2015 at 1:28 am

    #1847353

    Here is a way to send data via an html table in the body of an email

    declare@Body nvarchar(max)

    ,@Query nvarchar(max);

    declare@tabtable (

    Column1 intnot null

    ,Column2 nvarchar(20)not null);

    insert into @tab

    select '1', 'Text1' union all

    select '2', 'Text2' union all

    select '3', 'Text3' union all

    select '4', 'Text4';

    select @Query = (select Column1 as td, '', Column2 as td

    from @tab

    for xml path ('tr'));

    select @Body = '<html><body><table><tr><th>Column1</th><th>Column2</th></tr>' + @Query;

    select @Body = @Body + '</table></body></html>';

    exec msdb.dbo.sp_send_dbmail

    @profile_name = 'ProfileName'

    ,@body_format ='HTML'

    ,@body = @body

    ,@recipients = 'email@email.com'

    ,@subject = 'Html Email';

  • December 22, 2015 at 7:06 am

    #1847433

    The results will be different every time a query is run. Sometimes there might be two columns, and sometimes around 6 to 7.

    The query that will be executing will not yield same result set always.

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

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