Unable to send email from stored procedure populating dataset in SSRS report

• grantbanjo

  Ten Centuries

  Points: 1196

  More actions

  December 30, 2014 at 5:04 pm

  #296266

  Hello,

  I am using SQL Server 2008 R2 and am using a parameterized stored procedure in a report. When a particular parameter is provided in the report and the View Report button is selected a few files are generated, which are then displayed in the body of the report. Different files are generated based on the parameter value entered. This part is working fine.

  The problem is that when a specific parameter value is entered in the report, I now must also send an email. So for example, in the stored procedure applied by the report there is a condition that when parameter value "C" is provided files are created and an email needs to be sent. The problem is that when I run the report that then attempts to apply the EXEC msdb.dbo.sp_send_dbmail command within the stored procedure, the following error appears:

  An error has occurred during report processing. (rsProcessingAborted)

  Cannot read the next data row for the dataset DataSet1. (rsErrorReadingNextDataRow)

  For more information about this error navigate to the report server on the local server machine, or enable remote errors

  I have tried several workarounds:

  Calling the "EXEC msdb.dbo.sp_send_dbmail" command as a separate stored procedure.

  After placing the "EXEC msdb.dbo.sp_send_dbmail" in a stored procedure, adding it in a job and then calling the job at step 1.

  My objective is, for example, to be able to run the report with a parameter value of "C" and then having the stored procedure both generate the files and then send an email. Perhaps there is a better approach with an alert service, but I am not aware of how this can be accomplished.

  Thanks,

  Grant