| Author |
Topic |
|
josh2009
Yak Posting Veteran
81 Posts |
 Posted - 2010-05-10 : 14:57:41
|
| I am trying to send an email with a file attachment wit a SQL DB trigger using DBMail but I am unable to. I can send an email message with no attachment with no problems but whenever I try to include an attachment, it just won't let me. I have seen reports online about a limitation - In the Books Online, in the sp_send_dbmail remarks : Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.If this is true, is there absolutely no way to send attachments other than from the SQL Server machine? Here is my code - exec msdb.dbo.sp_send_dbmail @profile_name = 'DBMail1', @recipients = 'me@yahoo.com', @copy_recipients = 'me@yahoo.com', @body = @Notes, @Subject = @SubjectInfo, @body = @Notes, @Subject = @SubjectInfo, @file_attachments = 'C:\Documents and Settings\vjb001\My Documents\TestEmail.txt'I have also tried sending it to my personal email address thinking maybe our office Outlook account may be blocking it. I was able to send externally to my personal Yahoo account with no attachment successfully but again if I try adding an attachment, I am unable to. Any help will be greatly appreciated. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-10 : 15:06:20
|
| Does the attachment exist on the client machine or the database server?By the way, this is a very bad idea. The email component will participate in the transaction since you are doing it in a trigger. This means that a simple insert statement could take much longer to complete as the email has to complete before the transaction can be closed.Instead of this design, the trigger should write to a table that stores what needs to be emailed. Then you'd have a job run every minute to check what needs to be emailed. By doing it this way, the transaction and email aren't dependent on each other.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-10 : 15:19:46
|
Thanks Tara for the quick reply. So even if I specify in my trigger as After Update and then in my code I have the ff statement - If Update(NoteField) Begin ......exec sp_send_dbmail ....... EndSo you're thinking it's not a good way. Pls let me know. What I'm trying to achieve with this code is for our data analyst to be able to notify somebody if there's a dsicrepancy in the lab data. So I createed a new memo field and built a little text box for them to write a note and with the trigger, as soon as they hit save, trigger then sends email. Also, by the time data analyst gets to this point, record had already been inserted, data analyst would only be in the reocrd for updates to the data if necessary. By the way, attachment potentially could be in a network shared drive.Thanks again.quote:
Originally posted by tkizer
Does the attachment exist on the client machine or the database server?By the way, this is a very bad idea. The email component will participate in the transaction since you are doing it in a trigger. This means that a simple insert statement could take much longer to complete as the email has to complete before the transaction can be closed.Instead of this design, the trigger should write to a table that stores what needs to be emailed. Then you'd have a job run every minute to check what needs to be emailed. By doing it this way, the transaction and email aren't dependent on each other.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-10 : 15:33:52
|
| For how to create a job, please check BOL.Here's some pseudocode:1. In trigger, write to an Email table with what needs to be sent out plus include a column to say it hasn't been sent out yet.2. Create SQL job that runs every minute, SQL job will check the Email table and loop through all rows that need to be emailed, update each row after the email is complete to say the email was sent out. This topic is discussed fairly frequently here, so I'd suggest searching for "trigger email" in the search feature to get more information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-10 : 15:48:39
|
I see. Is there a way for the job to run ONLY when an update is made to the note field? You see, the data analyst would probably use the note field very sparingly, perhaps, not even once or twice daily. There would be days that it wouldn't be used at all. The reason I ask is because, I'm just afraid that if I let the job run every so often that it would put an unnecessary load on the server. You see, there are several applications using the same database including applications that store, view and send images so the database server is very busy. Thanksquote:
Originally posted by tkizer
For how to create a job, please check BOL.Here's some pseudocode:1. In trigger, write to an Email table with what needs to be sent out plus include a column to say it hasn't been sent out yet.2. Create SQL job that runs every minute, SQL job will check the Email table and loop through all rows that need to be emailed, update each row after the email is complete to say the email was sent out. This topic is discussed fairly frequently here, so I'd suggest searching for "trigger email" in the search feature to get more information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-10 : 16:04:28
|
Good stuff! Thanks Tara for the input and recommendations. I will check it out then.Jovy Banaagquote:
Originally posted by tkizer
You could call sp_start_job from the trigger to manually kickoff the job. However a job that runs every minute should not add any additional load since you'll be querying a tiny table. But the load should be measured in a test environment to be sure if this is something you are concerned about. Having a job run every minute for emailing purposes is very common.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Send email attachements using DBMail
Tara Kizer