April 20, 2010 at 10:59 pm
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
April 21, 2010 at 12:37 am
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:
April 21, 2010 at 1:32 am
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
April 21, 2010 at 1:51 am
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!!
April 21, 2010 at 2:14 am
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
April 25, 2010 at 11:22 pm
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)
April 26, 2010 at 1:01 am
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
April 26, 2010 at 1:22 am
When you setup your email profile, did you make your profile public AND default?
April 26, 2010 at 4:22 am
Yes, I did.
Lester Vincent
April 26, 2010 at 7:29 am
Hmmm.. Odd... Anything useful in the SQL Server Agent Logs? What's the code you are using?
April 27, 2010 at 2:04 am
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)
+CHAR(10)+CHAR(13)
+CHAR(10)+CHAR(13)
+CHAR(10)+CHAR(13)
+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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply