Problem with Database Mail in SQL Server 2005

Question

Post reply

Problem with Database Mail in SQL Server 2005

Lester Vincent

Ten Centuries

Points: 1227
More actions
April 20, 2010 at 10:59 pm

#223722

Hi People. I have at last got Database Mail successfully sending a test email :-):-).
Now, I want to add a row to a log table each time an email is sent.
These emails are all sent with a file attachment and I need not only the mailitem_id value, but an easy reference fo to whom the email was sent.
I am about to use "EXEC msdb.dbo.sp_send_dbmail " to send one as a test.
I want to be able to capture the mailitem_id value into a variable so I can store it in the log table row.
Would some kind soul please tell me what syntax I should use in my SPROC to get this value?
With thanks in advance.
Lester Vincent
Sydney

Viewing 11 posts - 1 through 10 (of 10 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hey buddy, kind souls? 😀

Fine here is how u can do it capture the mail

Declare @MailItemID INT

EXEC @RetCode = msdb.dbo.sp_send_dbmail

@profile_name = 'My Profile',

@recipients = @EMailAddress,

@subject = @MailSubject,

@body = @HTMLBody,

@body_format = 'HTML',

@mailitem_id = @MailItemID OUTPUT

Insert into mail_audit_log (columns go here)

select (columns come here) From msdb.dbo.sysmail_mailitems

where mailitem_id = @MailItemID

Hmmm, hope this helps you..:-) Now, am i a kind soul ? :w00t:

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 2

I think you are, Mr. Cold Coffee!

Thanks for the code suggestion. Looks good.

Since my post, I tried sending an email from my laptop (using msdb.dbo.sp_send_dbmail, with Developer Edition of SQL 2005), with a small text-file as attachment.

It went to the queue, and returned ID=16, but log showed a message, as follows: -

Could not retrieve item from the queue.

I contacted the recipient, thinking it might have got there, without the attachment, but no such luck!:-D Now I'm really confused!!!

Any ideas as to what is wrong with it? Please?

Lester Vincent

Sydney

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Lester Vincent (4/21/2010)
I think you are, Mr. Cold Coffee!

WOW, THANKS ! :hehe:

And for details about the mails, you will have to play around with the msdb.dbo.sysmail_ tables mate..I dont have much experience with database mail, so i can point only directions for you , Lester...If i get some more info on that, i will keep you posted!!

Cheers!!

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 4

Thanks heaps for your help so far. I googled the error msg and found a variety of responses. One was that it will only work with Windows Server installed and not on a single machine (Hope he's wrong!!) Another blasted Microsoft for producing a poor piece of software (doesn't help much!)

Another suggested the following settings (which I'd like to try but still working out where to adjust them)

a) Account retry attempts: 1

b) Account Retry Delay(seconds): 60

c) Maximum File Size (Bytes) 1000000 ----> make sure you set to at least 1 million

d) Prohibited Attachment Fil Extensions: exe, dll, vb, jr (you may want to edit to meet specific needs)

e) Database Mail Executable Minimum Lifetime (seconds): 600

f) Logging Level: Extended

g) Port: 25 (I know that is the default, but he suggests to check firewall that it has not been blocked.

If I can get to adjust these settings, maybe I could make it work.

The test email went through quite OK, but sending via the SPROC produced the error.

Ah well, a bit more trial and error, I guess.

Lester Vincent

Sydney

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 5

Silly question... Did you enable Database Mail stored procedures?

(Surface Area Config -> Features -> select your instance -> Database Engine -> Database Mail -> Check Enable Database Mail stored procs)

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 6

Yes, I had done that, Cycling Rabbit.:-)

Double-checked before responding to your post to.

Thanks for trying. It still doesn't work from my laptop with Dev Ed 2005.

Would appreciate any other suggestions.

Lester Vincent

Sydney

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 7

When you setup your email profile, did you make your profile public AND default?

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 8

Lester Vincent

Ten Centuries

Points: 1227

April 26, 2010 at 4:22 am

#1157523

Yes, I did.

Lester Vincent

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 9

Hmmm.. Odd... Anything useful in the SQL Server Agent Logs? What's the code you are using?

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 10

Here is the code that looks OK to me, but didn't go through: -

DECLARE @Mail_Subject varchar(200), @WorkOrder varchar(15), @PropertyAddress varchar(200)

DECLARE @Attachment_FilePath varchar(200), @FileName varchar(100), @CompanyName varchar(40)

DECLARE @Email_Body varchar(500), @Recipient_Email varchar(100)

DECLARE @MailItemI int

SET @Mail_Subject='Test Database Email from Lester at Asset Trade Services to Lester'

SET @Recipient_Email='info@qbsbarcoding.com'

SET @CompanyName='QuickFire Business Systems'

--Assemble the Attachment file name and path (a simple text file used for the test): -

SET @Attachment_FilePath='C:\Company Shared Folders\SQL_Database_Object_Upgrades_30122009.txt'

--Assemble the Email Body: -

SET @Email_Body='See attached Maintenance Request for your attention please.'

+CHAR(10)+CHAR(13)

+@CompanyName+CHAR(13)+CONVERT(varchar(12),GetDate(),113)

SET @MailItemID=0

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Asset Trade Services - Subcontract Manager',

@recipients=@Recipient_Email,

@subject=@Mail_Subject,

@file_attachments=@Attachment_FilePath,

@body=@Email_Body,

@mailitem_id=@MailItemID OUTPUT

SELECT @MailItemID

It did execute to the extent that it created and returned a value for @mailitem_id.

I hope you can see something wrong here, my friend. 🙂

Regards,

Lester Vincent

Sydney