SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Mail or not?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 11/30/2012 :  03:52:51  Show Profile  Reply with Quote
Hi,
If I were to set up a website with on-line purchasing, when someone makes a purchase, is a reply e-mail best to be sent from sql server (trigger and db mail) or is it best to come from the interface. For example when buying off Amazon / ebay, you get replies as the document chain goes on - would these be sql mail based or written through the interface? Thanks.

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 11/30/2012 :  03:58:09  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> trigger and db mail
Definitely not a send mail in a trigger.

Add a row to a table from the trigger - it shoiuld be a stored procedure and that should add the send mail row rather than a trigger.
Another process looks at that table and sends any outstanding mails. This could be a sql server ob which polls the table or an external process. The good thing about this is that you can change the mail process easily and it shouldn't affect anything so the decision isn't so important - could just depend on the expertise available or what is in place already. Also gives a record of the emails sent and can resend if needed. Also dev and testing becomes easier because the mail just needs that table not the rest of the system.

I used to send mails from a dedicated server but the implementation is a lot better now so that is not so important - but if you find it an issue that is quite easy to implement as long as you have the mail table as above..

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 11/30/2012 03:59:31
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 11/30/2012 :  04:31:18  Show Profile  Reply with Quote
Thanks for your thoughts on that.
The reason at this moment I am thinking trigger is that software has been written by an external company who drop the records into our database through Sage web services and so we cannot write the stored procedure. In future developments, we are going to bring the process in-house and then yes, your idea is definitely viable - I had not thought of including a send mail within a stored procedure but with all the details ready in the stored proc, that would definitely be the efficient way.
I was more fishing to get the idea if the likes of Amazon/ebay use sql mail for their replies.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 11/30/2012 :  04:40:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
In that case the trigger just inserts the details into a table and the asynchronous process sends the mail.
If the trigger sends the mail it would block the inserts until it was sent - and the insert would fail if the send mail statement failed. You definitely don't want to be doing that if a third party is doing the inserts as you would be relying heavily on their error handling.

If they are doing inserts then I would consider a timestamp or identity on the table so that it can be polled rather than a trigger as it's less risky.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 11/30/2012 04:41:39
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 12/04/2012 :  13:47:12  Show Profile  Reply with Quote
Interesting info that I didn't realise - Many thanks for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000