| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
 Posted - 08/21/2004 : 00:56:42
|
I have implemented the same and it works great, I've also included the udf csv to string code and the ability to pass a csv so that a list of files for attachment can be passed. The attachment method on msdn requires an extra parameter not mentioned in the docs.
.AddAttachment @mailobj, NULL, @attachment
in MSDN you'll find it as .AddAttachment, @mailobj, @attachment
|
 |
|
|
Lonestar
Starting Member
3 Posts |
Posted - 05/13/2005 : 03:50:52
|
Hi!
Sending mails works perfectly with cdosys or cdonts for me, but somehow I can't seem to send attachments. I don't get any errors, but theres just no attachment in the mail.
EXEC @hr = sp_OASetProperty @iMsg, 'AddAttachment', 'D:\huhu.txt'
thats the line I use to try to add the attachment....is there anything wrong with that syntax?it works fine for all other settings.
(and yes, the file does exist) ;) |
|
|
|
Lonestar
Starting Member
3 Posts |
Posted - 05/13/2005 : 11:10:10
|
| ok, stupid question....I should use a method as method, not as property ;) |
|
|
|
jparks
Starting Member
1 Posts |
Posted - 06/07/2005 : 17:25:12
|
| Could you post the code that allowed you to do an attachment. I am still having trouble making it work. Thanks! |
|
|
|
AndyB13
Aged Yak Warrior
United Kingdom
583 Posts |
Posted - 06/07/2005 : 17:32:38
|
Congratulations jparks you are SQLTeams 10000 member to post on here
Oh and welcome to the forum
Have a look at this article regarding email, this may help
http://www.sqlteam.com/item.asp?ItemID=5908
Beauty is in the eyes of the beerholder  |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
|
|
Lonestar
Starting Member
3 Posts |
Posted - 06/08/2005 : 01:48:12
|
For adding an attachment with CDOSYS you need the following line:
sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment
ofc you'll have to pass the path (absolute) for the file to your stored procedure. If you have any further questions, feel free to mail me ;) |
|
|
|
dlongmo
Starting Member
1 Posts |
Posted - 09/22/2005 : 14:46:00
|
Where do I add this line of code?
sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment
|
|
|
|
gzindino
Starting Member
1 Posts |
Posted - 11/02/2005 : 19:27:03
|
hello... i need help... i am trying to send an email with attachment... i can get the email but there is no attachment... please help... below is the code. thanks.
CREATE Procedure util_SMTPMail
@SenderAddress varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@attachment nvarchar(200) = null
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'cdonts.newMail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 1
IF @attachment IS NOT NULL
print @attachment
BEGIN
EXEC @resultcode = sp_OAMethod @oMail, 'AddAttachment', null, @attachment
END
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
Else
BEGIN
PRINT 'The object could not be instantiated!'
END
SET nocount off
GO
quote:
Originally posted by Lonestar
For adding an attachment with CDOSYS you need the following line:
sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment
ofc you'll have to pass the path (absolute) for the file to your stored procedure. If you have any further questions, feel free to mail me ;)
|
|
|
|
CrisO
Starting Member
6 Posts |
Posted - 12/03/2005 : 11:30:18
|
gzindino,
I was having the same problem. Took me a while to notice what I was doing wrong.
The AddAttachment method is trying to return an object not a result code. The @resultcode variable cannot receive this object.
To get around this do not call the sp_OAMethod like a function.
EXEC @resultcode = sp_OAMethod @oMail, 'AddAttachment', null, @attachment
Instead call it like a subroutine by removing @resultcode like this:
EXEC sp_OAMethod @oMail, 'AddAttachment', null, @attachment
|
|
|
|
erde
Starting Member
6 Posts |
Posted - 02/02/2006 : 10:43:56
|
I have such a procedure working fine on SQL 2000.
But now I have installed SQL 2005, and the same procedure, fails on sending the attachment. The same file is used for the attachment. The failed statement is:
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @szFileName
It returns the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
and it quits the procedure.
Mail is send.
|
|
|
|
stoneroses
Starting Member
2 Posts |
Posted - 05/23/2006 : 02:30:41
|
I encountered the same thing
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded. |
|
|
|
karbon
Starting Member
9 Posts |
|
|
nikie
Starting Member
1 Posts |
Posted - 04/02/2007 : 02:49:29
|
Hello...
I am also trying out to send attachment through CDOSYS mail.The mailer is working fine otherwise.But the attachment is not.
EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @file
where @file = 'c:\test\test.xls' a parameter passed from the code to the sp.
When i send mail i get mails with no attachment.Can anybody help me with this. |
|
|
|
StephenCT
Starting Member
New Zealand
1 Posts |
Posted - 07/09/2007 : 17:31:14
|
I too get the error: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Only when trying to send attachments. The email actually goes with the first attachment.
I am running this on MSSQL 2005 SP2, actually fully up to date with all updates on both the SQL and windows (2003).
Any idea guys? |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
|
|
mpthole
Starting Member
1 Posts |
Posted - 09/21/2007 : 15:24:09
|
First time poster, reviving an old thread. We just came across this problem today - being unable to send attachments - using virtually the exact code posted by gzindino above.
quote:
CREATE Procedure util_SMTPMail
@SenderAddress varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@attachment nvarchar(200) = null
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'cdonts.newMail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 1
IF @attachment IS NOT NULL
print @attachment
BEGIN
EXEC @resultcode = sp_OAMethod @oMail, 'AddAttachment', null, @attachment
END
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
Else
BEGIN
PRINT 'The object could not be instantiated!'
END
SET nocount off
GO
The part that changes is where the attachment gets added. Previously:
quote:
EXEC @resultcode = sp_OAMethod @oMail, 'AddAttachment', null, @attachment
You need to replace the null parameter with an actual variable and then specify OUT. Now it should read:
quote:
DECLARE @outVar INT
SET @outVar NULL
EXEC @resultcode = sp_OAMethod @oMail, 'AddAttachment', @outVar OUT, @attachment
Hope that helps someone - I just spent a day on it. |
|
|
|
mccork
Starting Member
1 Posts |
Posted - 10/23/2007 : 21:36:53
|
| mpthole.... it certainly helped me. Thanks! |
|
|
| |
Topic |
|
Article: Sending SQL notifications with CDOSYS
