Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RobVG
Starting Member
42 Posts |
 Posted - 2003-06-26 : 03:04:50
|
| I'm an SQL/computer newbie and I was wondering if someone could explain how an email trigger works. If the email address is at an ISP, how does SQL send to it? What if its a dial up connection? Or is this function intended for intra-office/intranet? Any info would be much appreciated.Thanks,Rob |
|
|
RobVG
Starting Member
42 Posts |
Posted - 2003-06-26 : 22:37:16
|
| OK, so it's "xp_sendmail"I'll keep digging but even a short, general description of how it works would be helpful. |
 |
|
|
MikeArnold
Starting Member
11 Posts |
Posted - 2003-07-01 : 04:28:54
|
| Rob - Try using the CDO (Collaborative Data Objects) ActiveX object. You should have it installed as part of Windows 2000. It works really well if you have SMTP mail - usong xp_sendmail is a major pain as you have to set up an email client and account for the SQL Server. You don't need any of that crap for this ...This is a stored procedure I wrote that uses CDO and an SMTP server (and yes, those properties really are URL's)CREATE PROCEDURE [dbo].[lasAG_sp_SMTPMail] @From nvarchar(100) , @To nvarchar(100) , @Subject nvarchar(100)=" ", @Body nvarchar(4000) =" "/*********************************************************************Reference to the CDOSYS objects are at the following MSDN Web site:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp***********************************************************************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) DECLARE @smtp_server nvarchar(100)SELECT @smtp_server = <name_of_your_smtp_server>--************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT--***************Configuring the Message Object ******************-- This is to configure a remote SMTP server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'-- This is to configure the Server Name or IP address. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp_server-- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null-- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To EXEC @hr = sp_OASetProperty @iMsg, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL-- Do some error handling after each step if you need to.-- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsgGOFatDaddy |
|
|
|
RobVG
Starting Member
42 Posts |
Posted - 2003-07-01 : 11:23:26
|
| Mike:I read about setting up an email profile at MSDN but frankly it was a little above my head. So is your example but with more time I think I can figure it out. Thanks for your response and if I get it working I'll post it here.Rob |
|
|
|
|
|
|
|
|
xp_email