Resource icon

SQL Solve sp_OAMethod AddAttachment Error

Kevin

Code Monkey
Staff member
Well, this one was driving me nuts today at work so in case anybody else out there encounters it, here's the solution.

I was working on a sproc to add attachments to an email using CDO in SQL Server 2008. The command for this was easy enough.

Code:
EXEC sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment

Running that code, though resulted in an error message.

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Oddly, the attachment still worked, it just threw the error at the sproc runtime. Our dear friend Google turns up a good number of people running into the same situation.

Before saying what did work, here are some things that did NOT work...

Code:
EXEC sp_OAMethod @iMsg, 'AddAttachment', '', @Attachment
Code:
declare @OutPutParam int;
EXEC sp_OAMethod @iMsg, 'AddAttachment', @OutPutParam, @Attachment

Both of the above will result in suppressing the error but your file won't get attached. Sure, it would be easy enough to just use the original code and ignore the error (and hope your DBA doesn't notice it one day) but that isn't the right thing to do.

So what did work? @OutPutParam needs specified, not NULL or empty string, and it needs to be specified as an output parameter.
Code:
declare @OutPutParam int;
EXEC sp_OAMethod @iMsg, 'AddAttachment', @OutPutParam out, @Attachment;
And there you go, attachment with no more error message. Yay! (y)

To save yourself some grief, also make sure your @Attachment file name is fully qualified and is reachable by your SQL server. If the SQL server can't attach the file, either because of a bad file name or it can't reach the file location, your email will still go out but with no attachment.
 
From someone that still thinks there is a magic crystal in that box with wires this post reads like a wizard's recipe. I can do some amazing things with that energy ball but I have no clue how to make one or how it is what it is?
 
Back
Top