question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked NickRyanNZ-4337 commented

DBMail Error Message but email gets sent

I have a package that calls a stored procedure to send emails. Looking at the log after the job ran, there are a number of lines where it appears there was an error but there's no sign the email didn't get sent.

The SP should have sent over 3000 emails. In the SSIS log there are lots of OnError lines with Send Emails SP:Error: Mail (Id: nnnnn) queued as the message.

If I run the following query, it finds only 1 failed email task from the period in which all the SSIS error lines were generated.

select *
from Msdb.dbo.sysmail_faileditems

Are those errors I can ignore or is there somewhere other than that table where I can look for more details about the log error?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,

If I run the following query, it finds only 1 failed email task from the period in which all the SSIS error lines were generated.

It seems that the mail is sent normally and there is no problem. Records in those logs may indicate that the mail is in the queue.

Check the Status of E-Mail Messages Sent With Database Mail
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/check-the-status-of-e-mail-messages-sent-with-database-mail?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered NickRyanNZ-4337 commented

lots of OnError lines with Send Emails SP:Error: Mail (Id: nnnnn) queued as the message.

That's the InfoMessage returned by SP SendDBMail. SSIS misinterpret such messages sometime as error, which it isn't. You can suppress InfoMessages with

 SET NOCOUNT ON;

or you can define in SQL Execution task, that it don't return a ResultSet.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unfortunately that's not the solution. I had an Ah Ha! moment then when I opened the project, I found the Execute SQL Task has ResultSetType_None. My stored procedures always have SET NOCOUNT ON;.

0 Votes 0 ·