MS SQL trigger - sending an email upon updating a table

Hello,</o>
I am trying to execute an email when a MS SQL table has been updated, inserted, or deleted. I am trying to do this with a trigger using SQL mail. Here's more info.. </o>
</o>
My trigger.. </o>
CREATE TRIGGER [CUSTOMER-TRIGGER] ON [xxx].[xxxxx]
FOR INSERT, UPDATE, DELETE
AS
EXEC xp_sendmail 'robertk', 'The master database is full.'</o>
</o>
Error Message:</o>
It doesn't work... I get the following error message</o>
Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'xp_sendmail'. The statement has been terminated.</o>
</o>
Here's the Microsoft link I am using</o>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_3_sqlmal_6gq4.asp </o>
</o>
More info on Microsoft website:</o>
How to use SQL Mail (Transact-SQL)</o>
<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 13.5pt; HEIGHT: 16.5pt" alt="" type="#_x0000_t75"><v:imagedata o:href="http://msdn.microsoft.com/library/en-us/howtosql/basics/update_topic.gif" src="file:///C:\DOCUME~1\COMPAQ~1\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape>New Information - SQL Server 2000 SP3.</o>
SQL Mail uses several extended stored procedures that are necessary for mail enabling. These extended stored procedures are included in a dynamic-link library, SQLMAP70.DLL, which is installed with Microsoft® SQL Server? 2000.</o>
For security reasons, you should limit permissions for all SQL Mail stored procedures and extended stored procedures to members of the sysadmin fixed server role.</o>
</o>
</o>
<A name=_MailAutoSig>Later,
</A><st1ersonName w:st="on"><u1ersonName u2:st="on">Gene Ness
</u1ersonName>[email protected]</o>

 

Hello,</o>
</o>
No support of SQL mail means no trigger emails. It may cause us to reconsider.</o>
</o>
Do you support ... "SmtpMail.SmtpServer" ... If so do you have examples?</o>
</o>
Do you have any examplesofautomatically sending an email after a SQL table has be modified?</o>
</o>
Later,</o>
<st1ersonName w:st="on">Gene Ness</o>
[email protected]</o>

 

Hello,</o>
Yes, I mean System.Web.Mail... Do you support this mail service? What mail service do you recommend?</o>
The System.Web.Mail namespace contains classes that enable you to construct and send messages using the CDOSYS (Collaboration Data Objects for Windows 2000) message component. The mail message is delivered either through the SMTP mail service built into Microsoft Windows 2000 or through an arbitrary SMTP server. The classes in this namespace can be used from ASP.NET or from any managed application.</o>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebmail.asp </o>
</o>
All I want to do is send an email notification if a SQL table is modified. Do you have any sample code for this?
Gene Ness

</o>

 

Hello Bruce,</o>
</o>
Thanks to your support team, I was able tosend an automatic email upon a visitor completing a form. I used "system.web.mail". I have the code if anyone wants it. One of the problems I had was I was trying to execute my Web Matrix email code on the Web Matrix server. Once I moved it to the DASP servers (and help for your staff) the code seems to be working fine.
</o>
However, it would be cleaner if I could do the same on SQL side. I understand your concern about a mass email system if you allowed SQL Mail. Is there anything I could do with stored procedures; ie, have the SQL Trigger call an aspx.page on the web server? Is there a way for SQL to call the web server? Any help/ideas would be of great assistance to us.

Gene Ness

 

Hey Bruce, can the transaction logs be accessed programatically? If so, he may want to try a scheduled task that calls an ASP page that reads the log and emails updates/changes to him. Not sure if this as doable.






Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
[email protected]
www.KiwanisPQ.org
(760) 419-7429