July 11, 2001 at 11:01 pm
Hi all
I ripped this nice piece of code some time back from the sql*server magazine forum for sending emails via cdonts. All works fine and dandy, but after much effort I cant set the properties to change its default SMTP server it uses. Can anyone assist me?
CREATE PROCEDURE SendMail_sp (@FROM NVARCHAR(255), @TO NVARCHAR(255), @SUBJECT NVARCHAR(255), @BODY NVARCHAR(4000)) AS
DECLARE @Object int
DECLARE @Object2 int
DECLARE @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)
DECLARE @V_BODY NVARCHAR(4000)
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT
IF @Hresult = 0 begin
--SET SOME PROPERTIES
SET @V_BODY = '' + @BODY
EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY
--CALL SEND METHOD
EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL
--DESTROY THE OBJECT
EXEC @Hresult = sp_OADestroy @Object
end
else begin
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
end
GO
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 12, 2001 at 9:35 am
I don't think you can do it through the NewMail object. Assuming you are using CDONTS with IIS server: you can setup the default smtp server with Internet Information Services. Right click on Default SMTP Virtual Server and fill in the info on the general tab.
July 12, 2001 at 9:54 am
I don't believe CDONTS supports changing of the SMTP Server. I'm looking at the object model right now to refresh my memory and I don't see anything in relation to setting a server. In IIS if you use CDONTS, it's important that you have the SMTP services running on the IIS box as well. You're probably going to have to go 3rd party. I know there are some 3rd party products out there which do support configuring for a particular server. Any particular reason you can't use xp_sendmail except for maybe the From address?
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 12, 2001 at 6:29 pm
CDO does support this from what I can find on MSDN using a configuration object. Have to admit Im not entirely clear on the differences between CDO and CDONTS! Still, might be worth looking in to.
Andy
July 12, 2001 at 8:20 pm
Hi guys
CDONTS was the original SMTP email library used with Windows NT, then 2000 saw CDOSYS.DLL, has a whole range of changes, including the use of a 'configuration' method that allows you to set smtpserver etc by specifiying a ADODB parameter with all the connect properties in it (for the life of me I couldnt work out now to massage the ADODB property under SQLServer via sp_OAxx)
Anyhow, I got a programmer to whip up a small DLL that used CDOSYS. Unfortunatly, our dev and test servers worked perfectly, but our new prod box is in a private nework and enclosed by a super tight firewall. All ports were opened for smtp from the server but the CDO libraries refuse to work. I tried Jmail (again, will post the sight) that used its own DLL and apart from taking over 1min to send a mail from the prod server, it worked a treat!? (Jmail on our dev and test servers runs in under 1sec)
Im still at a total loss in how CDO is actually communicating over the network to the smtp server and it seems to going through the local servers SMTP service as I have "bad" emails in its bad folder. This is strange as im not even specifying this as the relay server.
The only other issue I can think of is DNS. Apparently its not setup properly on the prod network, so this have something to do with it. Even so, we simply use IP's rather than hostnames.
So, fun for all! 🙂
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 12, 2001 at 9:14 pm
Prior to Windows 2000:
CDO Library, CDO Rendering Library - for MS Exchange Servers
CDO for NTS - for an SMTP Server (running locally)
As for Windows 2000, CDO for Windows 2000 is supposed to be able to communicate with a non-local server (I haven't tried it) using SMTP, so I'm as puzzled as you are Chris. Has anyone sniffed for packets to see if the server is even trying to communicate via SMTP? Has anyone run a netstat -a to see if the server opens a port to communicate with the specified SMTP server? I'd try those things as well to try and help troubleshoot.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 12, 2001 at 10:03 pm
Thanks for the info Brian..
Here is the code and web-site for the 3rd party (free!) dll that works. Ive replaced the stored proc example header to suit some of our internal requirements...
CREATE PROCEDURE SendMail_sp (@PFROM NVARCHAR(255), @PTO NVARCHAR(255), @PSUBJECT NVARCHAR(255), @PBODY NVARCHAR(4000)) AS
DECLARE @sender varchar(100)
DECLARE @sendername varchar(100)
DECLARE @serveraddress varchar(255)
DECLARE @recipient varchar(255)
DECLARE @recipientBCC varchar(200)
DECLARE @recipientCC varchar(200)
DECLARE @attachment varchar(100)
DECLARE @subject varchar(255)
DECLARE @mailbody varchar(8000)
set @recipientBCC = ''
set @recipientCC =''
set @attachment =''
set @serveraddress = '163.232.xxx.xxx'
set @sendername = ''
set @sender = @PFROM
set @recipient = @PTO
set @subject = @PSUBJECT
set @mailbody = @PBODY
/*
Stored procedure using Dimac w3 JMail by Mats Cederholm, mats@globalcom.se, Global Communications WWW AB
Sending email by instantiating w3 JMail instead of SQL mail.
*/
/*
Declares variables for input/output from w3 JMail and errormessage
*/
declare @object int,
@hr int,
@rc int,
@output varchar(400),
@description varchar (400),
@source varchar(400)
/*
Set all values to w3 JMail needed to send the email
*/
exec @hr = sp_OACreate 'jmail.smtpmail', @object OUT
exec @hr = sp_OASetProperty @object, 'Sender', @sender
exec @hr = sp_OASetProperty @object, 'ServerAddress', @serveraddress
exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient
exec @hr = sp_OASetProperty @object, 'Subject', @subject
exec @hr = sp_OASetProperty @object, 'Body', @mailbody
/*
Set some more values, depending on the value of the variables
*/
if not(@attachment='')
exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment
print @attachment
if not(@recipientBCC='')
exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC
if not(@recipientCC='')
exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC
if not(@sendername='')
exec @hr = sp_OASetProperty @object, 'SenderName', @sendername
/*
Call execute to send the email
*/
exec @hr = sp_OAMethod @object, 'execute', NULL
/*
Catch possible errors
*/
exec @hr = sp_OAGetErrorInfo @object, @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
/*
Kill the object
*/
exec @hr = sp_OADestroy @object
GO
Also the VB code from our test DLL using CDO that doesnt work on our prod sql*server...
Option Explicit
Private mLastErrorDescription As String
Public Property Get LastErrorDescription() As String
LastErrorDescription = mLastErrorDescription
End Property
Public Property Let LastErrorDescription(ByVal NewValue As String)
mLastErrorDescription = NewValue
End Property
Public Function SendMessage(ByVal ToAddress As String, _
ByVal FromAddress As String, _
ByVal SubjectText As String, _
ByVal BodyText As String, _
ByVal Server As String) As Integer
'Simple function for sending email from an SQL Server stored procedure.
'Returns 0 if OK and 1 if FAILED.
'
Dim Result As Long
Dim Configuration As CDO.Configuration
Dim Fields As ADODB.Fields
Dim Message As CDO.Message
On Error GoTo ERR_HANDLER
'Initialise variables.
Result = 0
'Set the configuration.
Set Configuration = New CDO.Configuration
Set Fields = Configuration.Fields
With Fields
.Item(CDO.CdoConfiguration.cdoSMTPServer) = Server
.Item(CDO.CdoConfiguration.cdoSMTPServerPort) = 25
.Item(CDO.CdoConfiguration.cdoSendUsingMethod) = CdoSendUsing.cdoSendUsingPort
.Item(CDO.CdoConfiguration.cdoSMTPAuthenticate) = CdoProtocolsAuthentication.cdoAnonymous
End With
'Create the message.
Set Message = New CDO.Message
With Message
.To = ToAddress
.From = FromAddress
.Subject = SubjectText
.TextBody = BodyText
Set .Configuration = Configuration
'Send the message.
.Send
End With
EXIT_FUNCTION:
'Clean up objects.
Set Configuration = Nothing
Set Fields = Nothing
Set Message = Nothing
SendMessage = Result
Exit Function
ERR_HANDLER:
Result = Err.Number
LastErrorDescription = "Number [" & Err.Number & "] Source [" & Err.Source & "] Description [" & Err.Description & "]"
GoTo EXIT_FUNCTION
End Function
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
April 13, 2004 at 6:31 pm
I ran this stored proc and got the following does anyone know why?
Error Occurred Calling Object: CDO.Message.1 The "SendUsing" configuration value is invalid.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply