Can't used SQL Send Mail in Scheduler 2

[dk2006]

Hi All,
I created DTS Package that will export data from a table to MS-Excel and email to dedicated recipient. I used Send Mail Task and it works fine when I test it while I'm in DTS design mode. When I schedule it in Scheduler, it doesn't work. Anyone know why?

thanks,
dk

 

[AlexCuse]

Probably something to do with what login the job is being run under.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[dk2006]

thank you for responding. the login is the same as the owner of dts. Still having problem.

 

[AlexCuse]

are you the owner of the DTS? It may be that there is not an email account associated with that login, but there is one associated with your login.

Because of problems like this, and the better flexibility it offers, I usually send emails using VBScript. Is that an option for you (requires SMTP to be enabled on your mail server)?

Ignorance of certain subjects is a great part of wisdom

 

[dk2006]

AlexCuse,
I already did something similar to what you said.
I wrote and external program (VB.NET) to send email with attached file using SMTP server. It works great. Now I just want to revise it so that everything is in one place (same DTS job). I also want the SQL Send Mail to work for other purpose.

I am the owner of DTS and have email account with outlook install on the server that DTS is running on. At this point, I don't know what else could have prevent it from working. Error message from MS...ha! not helping at all. No wonder it's name is Microsoft. Error number 0x80070057. Whatever that is. I tried to search on Google... no help.

thanks,
dk

 

[AlexCuse]

dk2006 -

I wouldn't mess with outlook at all for this. Here is a script that (should) send email for you after some modifications. I tried to remove all my references to global variables and what not, I apologize if I missed anything. You should just need to change email fields and IP address, and thsi will run for you (from an ActiveX script taxk).

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
        Dim bMail


        'call the SendMail function
        bMail = SendMail()
	
        'if the message was sent successfully then return success else return failure
        If bMail = True Then
          Main = DTSTaskExecResult_Success
        Else
          Main = DTSTaskExecResult_Failure
        End If

End Function


Function SendMail()

        On Error Resume Next

        Dim myMail

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="New AgeIn Load"
    myMail.From="server@company.com"
    myMail.To= "Someone@company.com"
    myMail.TextBody="Hi John, " & Chr(13) & Chr(13) _
	& "This Is Where you type Body" 
	& Chr(13) & Chr(13) _
	& "Thanks," & Chr(13) & "<auto generated message>"
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
    'Name or IP of remote SMTP server
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
        ="111.11.1.11"
    'Server port
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
        =25 
    myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing
 
        If ErrorCount <> 0 Then
          SendMail = False
        Else
          SendMail = True
        End If

End Function

Let me know if this works for you.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

In order to get the DTS sendmail function working you have to log into the SQL Server with the account which the SQL Agent is running and configure outlook under this account.

Then setup SQL Mail and SQL Agent Mail to use this profile. A Service restart may be required to get SQL to see the profile.

Then DTS should be able to send email.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dk2006]

Thank you guys. That make a lot of sense.

dk