SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Sending SQL notifications with CDOSYS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/04/2004 :  13:03:04  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 08/21/2004 :  00:56:42  Show Profile  Reply with Quote
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

Go to Top of Page

Lonestar
Starting Member

3 Posts

Posted - 05/13/2005 :  03:50:52  Show Profile  Reply with Quote
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) ;)
Go to Top of Page

Lonestar
Starting Member

3 Posts

Posted - 05/13/2005 :  11:10:10  Show Profile  Reply with Quote
ok, stupid question....I should use a method as method, not as property ;)
Go to Top of Page

jparks
Starting Member

1 Posts

Posted - 06/07/2005 :  17:25:12  Show Profile  Reply with Quote
Could you post the code that allowed you to do an attachment. I am still having trouble making it work. Thanks!
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 06/07/2005 :  17:32:38  Show Profile  Reply with Quote
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
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 06/07/2005 :  18:04:04  Show Profile  Visit jhermiz's Homepage  Reply with Quote
jparks who ?
=)


Keeping the web experience alive -- http://www.web-impulse.com
Imperfection living for perfection --
http://jhermiz.blogspot.com/
Go to Top of Page

Lonestar
Starting Member

3 Posts

Posted - 06/08/2005 :  01:48:12  Show Profile  Reply with Quote
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 ;)
Go to Top of Page

dlongmo
Starting Member

1 Posts

Posted - 09/22/2005 :  14:46:00  Show Profile  Reply with Quote
Where do I add this line of code?

sp_OAMethod @iMsg, 'AddAttachment',null, @Attachment
Go to Top of Page

gzindino
Starting Member

1 Posts

Posted - 11/02/2005 :  19:27:03  Show Profile  Send gzindino a Yahoo! Message  Reply with Quote
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 ;)

Go to Top of Page

CrisO
Starting Member

6 Posts

Posted - 12/03/2005 :  11:30:18  Show Profile  Reply with Quote
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

Go to Top of Page

erde
Starting Member

6 Posts

Posted - 02/02/2006 :  10:43:56  Show Profile  Reply with Quote
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.
Go to Top of Page

stoneroses
Starting Member

2 Posts

Posted - 05/23/2006 :  02:30:41  Show Profile  Reply with Quote
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.
Go to Top of Page

karbon
Starting Member

9 Posts

Posted - 05/29/2006 :  08:11:29  Show Profile  Reply with Quote

did u try SP1?

http://support.microsoft.com/kb/910416/en-us
Go to Top of Page

nikie
Starting Member

1 Posts

Posted - 04/02/2007 :  02:49:29  Show Profile  Reply with Quote
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.
Go to Top of Page

StephenCT
Starting Member

New Zealand
1 Posts

Posted - 07/09/2007 :  17:31:14  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 07/09/2007 :  17:34:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you are using SQL Server 2005, then why don't you just use Database Mail instead?

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mpthole
Starting Member

1 Posts

Posted - 09/21/2007 :  15:24:09  Show Profile  Reply with Quote
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.
Go to Top of Page

mccork
Starting Member

1 Posts

Posted - 10/23/2007 :  21:36:53  Show Profile  Reply with Quote
mpthole.... it certainly helped me. Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000