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 |
|
vdpramesh
Starting Member
12 Posts |
 Posted - 2004-04-17 : 10:43:39
|
| Hi, I would like to know how the following stored proc works for sending mail thru SMTP server. Here one of line specified like this, "EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'".I understand that this is URI and not URL with refrerring microsoft site.The specified URI should be available on my local system or how that stuff works.? Pls explain me on this ?.Thanks in Advance!Ramesh. CREATE PROCEDURE [dbo].[sp_send_cdosysmail] @From varchar(100) , @To varchar(100) , @Subject varchar(100)=" ", @Body varchar(4000) =" "/*********************************************************************This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary.References 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)--************* 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.-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp 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. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'myservername' -- 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-- Sample error handling. IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END-- Do some error handling after each step if you have to.-- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsgGO |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-04-17 : 11:35:31
|
| What this procedure does is to instantiate and run an OLE object, so what you would normally reference in i.e. active server pages like this:Set Mail = Server.CreateObject("CDO.Message")...can be used using a few system stored procedures in your sql aswell. I just copied/modified a procedure like this to send emails from the SQL Server Agent using the object ASPemail from Persists Software. Works excellent, and it means that you don't need a MAPI-client on your sql-server...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-17 : 15:56:20
|
| Lumbago, I was about to look for some examples of writing sprocs to send email using ASPemail. I currently have a function that generates/retreives information like what the body will be, what the subject is, who is sending it to who, and other details which send it to a general function where it setups up the component and then sends the email. i wanted to turn this into sproc and i'm not sure how to work it with t-sql, or even as an active-x in a job. Can you post a simple example, or point me to a place where i can get more info on this? thanks.- RoLY roLLs |
|
|
|
|
|
|
|
|
Want to know how this works...