Sending Attachment with Cdosys

Question

Post reply

Sending Attachment with Cdosys

Viewing 8 posts - 16 through 22 (of 22 total)

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

RG_CGX Grasshopper Points: 11 More actions · Answer 1

you HAVE to use the ",@rv out," parameter. if not it wont work

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT

RG.

rasoulov Right there with Babe Points: 765 More actions · Answer 2

rasoulov

Right there with Babe

Points: 765

February 4, 2003 at 1:57 pm

#448032

I have resolved this issue. Thanks.

cwightman SSC Veteran Points: 248 More actions · Answer 3

Have tried the examples but the attachemnt did not turn up.

Please could someone help

David Burrows SSC Guru Points: 65111 More actions · Answer 4

Clive,

I answered your query on another thread but there seems to be a common problem with cdosys and attachments. Many people have posted their procs on this site and I used one of them and altered it for my own use. If you email me I will send you my proc to try, it works for me.

Far away is close at hand in the images of elsewhere.
Anon.

sqlservercentral@onskee.com SSC Veteran Points: 232 More actions · Answer 5

I hope this turns out to be useful to someone. I found the core of this code from another SQL site (before I found this one )

If you're in a setup like us and cannot use CDONTS, this is a reasonable alternative, I think, using ASPMail by ServerObjects.com. You also have the ability to specify your own SMPT server, and with a few lines, make the SMTP server a parameter. It defaults to HTML formatting for the body. It also accepts attachments. If you send an attachment, it makes a feeble but reasonable attempt to remove the HTML tags so it can send as regular text (which is a function of the mail component).

Regards -

B

 

CREATE Procedure WA_SENDMAIL



@RecipientName varchar(50),

@RecipientAddress varchar(50),

@SenderName varchar(50),

@SenderAddress varchar(50),

@CCName VarChar(50),

@CCAddress VarChar(50),

@BCCName VarChar(50),

@BCCAddress VarChar(50),

@Subject varchar(100),

@Body varchar(8000),

@Attachment VarChar(100)



AS



Declare @MailBody VarChar(8000)

Declare @MailServer VarChar(30)

Set @MailServer = 'mail.db.pvt'



Declare @Response VarChar(100)



SET nocount on



declare @oMail int --Object reference

declare @resultcode int



EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT



if @resultcode = 0

BEGIN

Set @Body = Convert(Char(8000),@Body)

EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost',@MailServer

EXEC @resultcode = sp_OASetProperty @oMail, 'FromName',@SenderName

EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress',@SenderAddress

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL,@RecipientName, @RecipientAddress

If @CCName <> '' AND @CCAddress <> ''

Begin

EXEC @resultcode = sp_OAMethod @oMail, 'AddCC',NULL, @CCName, @CCAddress

End

If @BCCName <> '' AND @BCCAddress <> ''

Begin

EXEC @resultcode = sp_OAMethod @oMail, 'AddBCC',NULL, @BCCName, @BCCAddress

End

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject



If @Attachment <> ''

Begin

Create Table #MailBody(BODY VarChar(8000))

INSERT INTO #MailBody(BODY) VALUES(@Body)



UPDATE #MailBody Set BODY = Replace(BODY, '<br>',CHAR(10))

CREATE TABLE #html ( tag varchar(30) )

INSERT #html VALUES ( '<html>' )

INSERT #html VALUES ( '<head%>' )

INSERT #html VALUES ( '<title%>' )

INSERT #html VALUES ( '<link%>' )

INSERT #html VALUES ( '</title>' )

INSERT #html VALUES ( '</head>' )

INSERT #html VALUES ( '<body%>' )

INSERT #html VALUES ( '<td%>' )

INSERT #html VALUES ( '</td>' )

INSERT #html VALUES ( '<tr%>' )

INSERT #html VALUES ( '</tr>' )

INSERT #html VALUES ( '<table%>' )

INSERT #html VALUES ( '</table>' )

--INSERT #html VALUES ( '<style%</style>' )

INSERT #html VALUES ( '<link%>' )

INSERT #html VALUES ( '<style%>' )

INSERT #html VALUES ( '</style>' )

INSERT #html VALUES ( '</body>' )

INSERT #html VALUES ( '</html>' )



WHILE exists(select * FROM #MailBody JOIN #html on patindex('%' + tag + '%' , BODY ) > 0 )

UPDATE #MailBody

SET BODY = stuff(BODY, patindex('%'+tag+'%', BODY),charindex('>', BODY, patindex('%'+tag+'%',BODY )) - patindex('%'+tag+'%', BODY) + 1, '' )

FROM #MailBody JOIN #html

ON patindex('%'+tag+'%', BODY) > 0





Set @Body = (SELECT BODY FROM #MailBody)

DROP TABLE #MailBody

DROP TABLE #html

EXEC @resultcode = sp_OASetProperty @oMail,'BodyText', @Body

Exec @resultcode = sp_OAMethod @oMail,'AddAttachment', NULL, @Attachment

End

Else

Begin

EXEC @resultcode = sp_OASetProperty @oMail,'BodyText', @Body

EXEC @resultcode = sp_OASetProperty @oMail,'ContentType', 'text/html'

End



EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL



EXEC sp_OADestroy @oMail



END





SET nocount off



GO

B Thomas SSC Journeyman Points: 91 More actions · Answer 6

I like the suggestions, and the ability to attach a SQL query output to an email.

I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @sqlquery + '" /E''')

But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

I DO NOT want to use XP_SENDMAIL as it uses MAPI . Is there any other way to attach SQL queries.

DLong SSC Rookie Points: 48 More actions · Answer 7

How do I send this attachment as a parameter?

I would like to pass the parameter in the job, like

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT

and then in the job have @attachement = 'c:\test.txt'

tasnim Grasshopper Points: 11 More actions · Answer 8

The following is the cdosys mailer .I can send mails but attachment is not working.

I dont have any clue why the attachement dont works.

Also if i write

EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, 'E:/test/test.txt'

i.e by passing values directly it doesnt send the attachment.

actually when i use

IF @hr 0

BEGIN

SELECT @hr

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Attachment')

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

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Attachment')

GOTO send_cdosysmail_cleanup

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

GOTO send_cdosysmail_cleanup

END

That is to trap the value.@hr returns -2.17 so the particular syntax

EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, 'E:/test/test.txt'

yields error @hr returns -ve value.

Could u trace this problem.

Alter PROCEDURE sp_my_mailobject

@From varchar(50) ,

@To varchar(50) ,

@Subject varchar(50),

@Body text,

@Smtp nvarchar(50),

@User varchar(50),

@Pass varchar(50),

@BodyType int,

@file NVARCHAR(200)

As

Declare @iMsg int

Declare @hr int

Declare @source varchar(255)

Declare @description varchar(500)

Declare @output varchar(1000)

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

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

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

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', '1'

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', @User

/*-- This is to configure the Server Name or IP address.

-- Replace password of your SMTP Server.*/

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', @Pass

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

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

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

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

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

IF @file IS NOT NULL

BEGIN

EXEC @hr = sp_OAMethod @imsg, 'AddAttachment', NULL, @file

IF @hr 0

BEGIN

SELECT @hr

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Attachment')

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

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Attachment')

GOTO send_cdosysmail_cleanup

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

GOTO send_cdosysmail_cleanup

END

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

IF @hr 0

BEGIN

SELECT @hr

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')

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

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')

GOTO send_cdosysmail_cleanup

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

GOTO send_cdosysmail_cleanup

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

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')

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

INSERT INTO [dbo].[g_send_fail_error] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')

END

/*-- else sp_OAGetErrorInfo failed*/

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

GO