Using SMTP Mail - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using SMTP Mail

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-08 : 08:55:52

Was hoping someone could help me with this.

We are using Lotus notes here as our mail system. Because of this we had to set up a special stored procedure to work with the SMTP mail protocol. We set it up according to
Q312839 - HOW TO Send E-Mail Without Using SQL Mail in SQL Server.htm.

Everything works fine. But what bothers us is that the code here appears to go out to a microsoft website for a particular object (sendusing). Am I reading this right? If so what would happen if our internet connection (or microsofts) goes down? Is there a way to set this up locally so we dont have to rely on this connection? Here is the code Microsoft tells us to use:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
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)

--************* 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', 'MailServer Name'

-- 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

What concerns us is:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
AND
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServer Name'

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

It appears these are looking to the internet for the objects..

Any help in understanding this is greatly appreciated.

John

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-08 : 11:16:28

These are just Namespaces (URI's) - they are not URL's - no need to worry

HTH
Jasper Smith

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-08 : 13:23:57

What purpose does the name space serve?

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-08 : 17:34:15

Rather than try and put it in my own words

quote:

Microsoft CDO for Windows 2000 enlists the use of Uniform Resource Identifiers (URIs) to qualify or scope field names and their semantic definitions. Each field accessed through a Fields collection is comprised of a namespace prefix and a local name. The namespace prefix is a URI reference used to qualify or scope the semantic definition of the property. For example, the subject field for a message has two representations: one with non US-ASCII characters encoded using the mechanism defined in RFC 1522, and another where these characters are decoded into native, UNICODE characters. By using namespaces, one can identify which semantic definition is intended when the "subject" for the message is requested. In this case, the subject field defined within the urn:schemas:mailheader: namespace is defined to be the US-ASCII string value of the field, and the subject field defined within the urn:schemas:httpmail: namespace is the native, UNICODE version

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_fields.asp[/url]

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources