eMAIL

Question

Post reply

eMAIL

Sanjay Masawan

Ten Centuries

Points: 1031
More actions
March 31, 2005 at 7:19 am

#60713

Can anyone tell me how to send Email on error in DTS package
And also how to configure a profile

Viewing 4 posts - 1 through 3 (of 3 total)

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

Daljit-208782 SSChasing Mays Points: 623 More actions · Answer 1

For configuring SQL Mail, refer to the following link:

http://support.microsoft.com/kb/263556/

for Common SQL Mail Problems refer to

http://support.microsoft.com/kb/315886/

For DTS mails refer to:

http://www.sqldts.com/default.aspx?287

Thanks

Rainer Plischke Mr or Mrs. 500 Points: 506 More actions · Answer 2

If you don't have or want MAPI installed on the server and you also don't want to use an additional tool you might use cdo. The following - which I think was taken originally from somewhere else on this forum and later modified a little - may do the trick. As the original stored procedure uses a table I did some more modifications but didn't test it so don't wonder if there are any unused variables or flaws, but you for sure can figure out how it is supposed to work.

As sa rights are required to execute this we finally did this with a slightly different approach using a table and a job regularly checking that table for new emails to be sent.

declare@vSMTPServer varchar(255)

set @vSMTPServer = 'smtp.demo.com'

DECLARE @hr varchar(2000),

@hr2 varchar(2000)

declare @iMsg int

declare @source varchar(1000)

declare @description varchar(1000)

declare @output varchar(2000)

declare@iM_ID int,

@vReceipient varchar(8000),

@vSubject varchar(256),

@vBody varchar(8000),

@vCC varchar(8000),

@vBcc varchar(8000),

@vSender varchar(256),

@vAttachment varchar(8000),

@iStatus int,

@bHTML bit

set @vReceipient = 'receipient@demo.com'

set @vSubject = 'subject'

set @vBody = 'body'

set @vSender = 'sender@demo.com'

set @vAttachment = 'e:\demo\demofile'

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

-- This is to configure a remote SMTP server. --

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.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @vSMTPServer

-- 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', @vReceipient

if @vCc is not null begin

EXEC @hr= sp_OASetProperty @iMsg, 'Cc', @vCc

end

if @vBcc is not null begin

EXEC @hr= sp_OASetProperty @iMsg, 'Bcc', @vBcc

end

EXEC @hr= sp_OASetProperty @iMsg, 'From', @vSender

EXEC @hr = sp_OASetProperty @iMsg,'Subject', @vSubject

-- no object return code allowed, therefore "AddAttachment" method call w/o @hr = ...

if @vAttachment is not null

EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @vAttachment

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

if @bHTML = 0

EXEC @hr =sp_OASetProperty @iMsg, 'TextBody', @vBody

else

EXEC @hr =sp_OASetProperty @iMsg, 'HTMLBody', @vBody

EXEC @hr =sp_OAMethod @iMsg, 'Send', NULL

-- Do some error handling after each step if you need to. -- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

Robert Hammett SSC-Addicted Points: 468 More actions · Answer 3

You can also use VBScript to send email using CDO. I don't have the code in front of me but it's similar to...

dim m as CreateObject("CDO")

m.sender = "me@here.com"

m.recipient = "you@there.com"

m.subject = "hi"

m.body = "Howdy pardner"

m.send

Or, something along those lines anyway. Run this in an ActiveX script. I cannot guarantee how secure or scalable this is but it worked well for sending email in response to errors at the small shop where I used to work.