October 19, 2009 at 10:56 pm
hi,
just wondering if anyone could help me find a sample program that sends e-mail using a STMP Relay. cdosysmail is using STMP Server but we are changing to use STMP Relay instead.
Any help would be appreciated.
thanks,
October 19, 2009 at 11:16 pm
I'm a little fuzzy what you are asking.. An SMTP relay is just a server that passes SMTP traffic for you. It is really no different than any other SMTP host.
There is replacement (mostly) for xp_sendmail at http://www.sqldev.net/xp/xpsmtp.htm
There is also a replacement, that I don't have any experience with at http://www.sqlanswers.com/Software/SAM/SAMxp_sendmail.aspx but it is a commercial and cost 386.53 for a license.. And as an aside, I think this part of their licensing sucks:
"If you are licensing a SQL Server cluster, a separate license must be purchased for each node in the cluster. When licensing cluster nodes, it's necessary to repeat all node names separated by commas for each license."
CEWII
October 19, 2009 at 11:45 pm
thanks for the reply elliot.
i will have a look on the link that you gave and will see what i can get from there.
nevertheless, i would like to make the code below to work on a STMP Relay. I've changed this line
FROM:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'S1CN01.CNGROUP.COM'
TO:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'stmprelay.remc'
but did not work. have been informed that the server had been added to the relay. i was told that i need to rewrite my script. does this make sense?
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(8000) =" "
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
IF @hr <>0
BEGIN
SELECT @hr
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
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--***************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'
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- 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', 'S1CN01.CNGROUP.COM'
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
BEGIN
SELECT @hr
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
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
-- handle the failure of the destroy if needed
IF @hr <>0
BEGIN
select @hr
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
RETURN
END
GO
thanks,
October 20, 2009 at 12:04 am
Can you ping 'stmprelay.remc' from that machine? If not then this will never succeed. That is an odd name though.. It isn't a fully qualified name. Are you sure it is right?
Second using sp_OA methods is ripe for memory leaks.. If you can I would definitely move away from using this method.
CEWII
October 20, 2009 at 2:59 am
hi,
thanks for the response...
no this is not the correct name.. i just changed it for security purpose of posting in the internet.. however, will check if i can ping the machine...
thanks,
October 20, 2009 at 3:02 am
Thats ok, I understand..
CEWII
October 20, 2009 at 4:28 pm
hi,
i tried pinging the machine and works fine. however, when i tried using my script, i got this error. do you have idea why?
The message could not be sent to the SMTP server. The transport error code was 0x800ccc67. The server response was 421 4.3.2 Service not available, closing transmission channel
Thanks in advance.
October 20, 2009 at 4:50 pm
Do they require sending the mail using SSL? That is all I can think of is that they don't allow you to use port 25.. Does this component support SSL?
CEWII
October 20, 2009 at 5:48 pm
Thanks for the reply once again. You've been so helpful.
I have spoken to our IT team and found out that my server was not authenticated to the SMTP Relay. Watta!!!... I was told that they had. Well, it was done in Italy.
Anyways, if I have more questions with regard to this SMTP e-mailing, I hope you don't me coming back to this thread. Will let you know once it is working or not. Don't know yet what will be the outcome of my testing until they have authenticated my server.
Thanks again..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply