CDO mail did not work

Question

CDO mail did not work

Frances L

SSCarpal Tunnel

Points: 4727
More actions
November 30, 2006 at 6:55 am

#67891

My sql server set up as system adminstration and not domain account set up in the server. I set up DTS service in my sql server. all parts working fine except the mail part did not launch. I checked there is cdo.dll installed in my computer. btw, I have smtp server set up. I can email in agent without any problem. so...

Function Main()
        Dim oFSO, oConn, sFileName, oFile
' Get the filename from my Text File connection called "Text File (Source)"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
sFilename = oConn.DataSource
Set oConn = Nothing
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Check File Exists first
If Not oFSO.FileExists(sFilename) Then
  ' SQL Server 2000 Only, log error
  ' Return Error
                EmailNotify("FileNotExist")
  Main = DTSTaskExecResult_Failure
Else
  ' Get file object
  Set oFile = oFSO.GetFile(sFilename)

  ' Check age of file is less than 24 hours
  If DateDiff("h", oFile.DateLastModified, Now) >= 160 Then
   ' SQL Server 2000 Only, log error
                 EmailNotify("Rob")
   ' Return Error
   Main = DTSTaskExecResult_Failure
  Else
   ' Return Success
   Main = DTSTaskExecResult_Success
  End If
End If

Set oFile = Nothing
Set oFSO = Nothing
End Function
Function   EmailNotify(s)
      Dim objMail , strEmailBody, strA
      Set objMail = CreateObject("CDONTS.NewMail")

' Set Message Text, including a global variable value
       if s="FileNotExist" then

       strEmailBody = " \\Dbase-svr\shared\Doc_master\Doc.txt is not existed"
       else

       'Create the body of the email
       strEmailBody = " \\Dbase-svr\shared\Doc_master\Doc.txt is upated"
   end if
       'objMail.To = "ABC@ABAC.com"
       objMail.Subject = "Doctor Master Update Report"
       'Set the Importance to High
       objMail.Importance = 2
       objMail.Subject = strEmailBody
       objMail.Send

' Clean Up
Set objMail = Nothing
End Function

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

You're not using CDO, you're using CDONTS. They're not the same thing. CDONTS requires CDONTS.dll and no longer comes standard in Windows. I suggest rewriting your procedure to use CDO; however, installing CDONTS.dll on the server will generally do the trick.

Or you could simply call xp_sendmail from the procedure. This would be my preferred solution unless I needed to do something that SQLMail doesn't support like HTML.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

Will you please give me the code for vbscript ? I modified my stored procedure and it gave me the error for 'Configuration Object. i think it must be syntax error since it is my first active x script. Thx.

Function EmailNotify(s)

EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object

EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object

'Configuration Object

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate

EXEC sp_OAMethod @config, 'Fields.Update'

if s="FileNotExist" then

set @Subject='Doctor Master Status-Doctor Master is not existed'

set @Body='Doctor Master Status-Doctor Master is not existed ' +convert(nvarchar(20),getdate())

else

set @Subject='Doctor Master Status-Doctor Master is not updated'

set @Body='\\Dbase-svr\shared\Doc_master\Doc.txt is not updated ' +convert(nvarchar(20),getdate())

end if

' Message Object

EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config

EXEC @hr = sp_OASetProperty @message, 'To', @To

EXEC @hr = sp_OASetProperty @message, 'From', 'abc@abc.com'

EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body

EXEC sp_OAMethod @message, 'Send()'

'Destroys the objects

EXEC @hr = sp_OADestroy @message

EXEC @hr = sp_OADestroy @config

' Errorhandler

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

end function

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Be sure to change the cdoSMTPServer proeprty to your SMTP mail server. Your code shows it as still using your.server.com

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

Function EmailNotify()

Dim myMail

Set myMail=CreateObject("CDO.Message")

myMail.Subject="DoctorMasterUpdateReport"

myMail.From="abc@Abc.com"

myMail.To= "abc@Abc.com"

myMail.TextBody="Doctor Master is not update, " & Chr(13) & Chr(13) _

& "Make Sure to Delimit Lines" & Chr(13) & Chr(13) _

& "Thanks," & Chr(13) & "<auto generated message>"

myMail.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing")=2

'Name or IP of remote SMTP server

myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="172.16.2.6"

'Server port default = 25

myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =25

myMail.Configuration.Fields.Update

myMail.Send

set myMail=nothing

end function

It gave me error for "the transport failed to connect to the server while I run this active X script. I use nslookup to get that smtp server ip address. Do you know why ? Thx.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

This is a network related error. Your application cannot connect to the mail server specified. Check the following:

Is the target server a valid SMTP Server?
Can the SQL Server server connect to the mail server? Some times firewalls or proxy servers can get in the way.
Make sure the that the mail server is running at port 25.
For testing purposes change the MailMessage.From and MailMessage.To properties to an address that exists on the mail server. Some times this exception is thrown, when it really is a relay issue.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

Thanks. I changed my ip address to what i used in the web application our exchange server ip address. it worked. but I got my smtp ip address from iis property and nslookup. Also i have the above stored procedure to run the stored procedure. it work fine to send email since in the stored procedure, I di d not need to specify the ip address. I do not know why it did not work in this case.