Article: Sending SQL notifications with CDOSYS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Sending SQL notifications with CDOSYS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-08-04 : 13:03:04

While SQL Server has a very mature set of tools, one thing that is lacking is the ability to send a simple email alert based on a pre-defined set of rules. At first glace, it appears that email notifications exist but what becomes a problem to many people is the requirement for a MAPI subsystem to be present on the server. This means that Outlook or an equivalent program has to be installed on the server. Not only that, to properly configure it, the administrator will need to log into the server as the user that the SQL Server service is running under. I have always found this inconvenient so I set out to create a stable, free, easy to configure means of accomplishing the same thing without installing anything special on the server. I've included steps below to complete this task successfully.

Article Link.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-08-21 : 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 - 2005-05-13 : 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 - 2005-05-13 : 11:10:10

ok, stupid question....I should use a method as method, not as property ;)

jparks
Starting Member

1 Post

Posted - 2005-06-07 : 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

583 Posts

Posted - 2005-06-07 : 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
[url]http://www.sqlteam.com/item.asp?ItemID=5908[/url]

Beauty is in the eyes of the beerholder

jhermiz

3564 Posts

Posted - 2005-06-07 : 18:04:04

jparks who ?
=)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Lonestar
Starting Member

3 Posts

Posted - 2005-06-08 : 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 Post

Posted - 2005-09-22 : 14:46:00

Where do I add this line of code?

sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment

gzindino
Starting Member

1 Post

Posted - 2005-11-02 : 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 - 2005-12-03 : 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 - 2006-02-02 : 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 - 2006-05-23 : 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

Posted - 2006-05-29 : 08:11:29

did u try SP1?

http://support.microsoft.com/kb/910416/en-us

nikie
Starting Member

1 Post

Posted - 2007-04-02 : 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

1 Post

Posted - 2007-07-09 : 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

38200 Posts

Posted - 2007-07-09 : 17:34:24

If you are using SQL Server 2005, then why don't you just use Database Mail instead?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mpthole
Starting Member

1 Post

Posted - 2007-09-21 : 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 Post

Posted - 2007-10-23 : 21:36:53

mpthole.... it certainly helped me. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources