February 3, 2003 at 9:59 pm
you HAVE to use the ",@rv out," parameter. if not it wont work
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT
RG.
February 4, 2003 at 1:57 pm
I have resolved this issue. Thanks.
February 7, 2003 at 8:41 am
Have tried the examples but the attachemnt did not turn up.
Please could someone help
February 10, 2003 at 2:46 am
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.
February 12, 2003 at 10:37 pm
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
November 16, 2004 at 12:34 pm
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.
September 22, 2005 at 1:47 pm
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'
April 3, 2007 at 11:57 pm
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
END
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
END
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
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
END
END
GO
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply