Mailing Out from SQL Server?

[SeaninSeattle]

We want to implement outbound email from SQL - that is, I want to write a sproc that executes daily, and looks for certain table conditions, and then sends out email (basically shipment notifications). I understand that CDONTS is supposed to be the way to do this ...

Does anyone have any information with respect to what I should be looking at, or what's the easiest path to follow to get this done?

Thanks,
//sse

Sean Engle
Admin/DirIS

 

[markharr]

First you need to configure SQL Mail on the server. The documentation is in the "Books Online".

A stored procedure can then be used to send e-mail using the extended stored procedure xp_sendmail (again, see the "Books Online&quot.

Perhaps a better option would be to set an update or insert trigger on the appropriate table and look for the condition you are interested in. Then use xp_sendmail as before.

If you are using SQL Server 2000, make sure you get the latest service pack. The shipped version of SQL Server 2000 had big problems in SQL Mail.

 

[SeaninSeattle]

Ok - I'll follow that path. We're still using SQL7.0, so...

thanks very much!

//sse Sean Engle
Admin/DirIS
ssengle@bswusa.com

 

[apollo11]

If you really do need to use CDONTS using SMTP, you can write a lightweight COM object with sendmail interfaces and register it with SQL Server. BOL has various examples on how to trigger and call COM interfaces from within T-SQL, The advantage with this is you will not need to install a MAPI compliant application such as Outlook, Windows Messaging etc. on your SQL Server and create and configure Mail profiles which is kind of convoluted. All you need to do is register the COM object with SQL server and you are done, plus you have the openness of SMTP. Unfortunately SQL mail uses MAPI exclusively to send mails.

 

[SeaninSeattle]

Yeah, we've been looking at that. I've pretty much kept our SQL server clean of other apps - and am not excited at the prospect of putting Outlook on it (for a variety of reasons). I'll have to talk to our developer and see about creating a COM object instead of going the MAPI route...

Thanks for your input...

//sse Sean Engle
Admin/DirIS
ssengle@bswusa.com