Article #Q312839

[Brooke]

HOW TO: Send E-Mail Without Using SQL Mail in SQL Server
(Q312839)

I need the information in this article, but when I click
on the link I get a message to the effect of "article not
available at this time". What's up ??

My specific question is: What is the stored procedure
syntax for adding an attachment using CDOSYS, something
like:
sp_OAMethod @iMsg, 'AddAttachment', "C:\myfile.txt" ??

[Aaron Bertrand - MVP]

The article is currently offline, either they are modifying it, or removing
it entirely (which wouldn't really surprise me). The example in this
article merely uses sp_OACreate to instantiate a copy of CDO.Message; you
can see very similar code at http://www.aspfaq.com/2403

The reason it wouldn't surprise me if the article were being removed, or
re-written to suggest xp_smtp_sendmail instead, is that sp_OACreate is not
without its issues, and probably the least desirable workaround.

"Brooke" <anon...@discussions.microsoft.com> wrote in message
news:046401c3a239$f54a7900$a101...@phx.gbl...

[Aaron Bertrand - MVP]

> My specific question is: What is the stored procedure
> syntax for adding an attachment using CDOSYS, something
> like:
> sp_OAMethod @iMsg, 'AddAttachment', "C:\myfile.txt" ??

Sorry, I missed this, you should be able to say (looking at the code sample
at www.aspfaq.com/2403):

EXEC @return = sp_OAMethod @handle, 'AddAttachment', 'C:\myfile.txt'

(Notice single quotes, not double quotes, around string values.)

[Brooke]

Thanks for the quick response - I actually figured out
something else since my 1st post different needing to use
single quotes:

sp_OAMethod @iMsg, 'AddAttachment',
@retval, 'C:\myfile.txt'

where @retval is declared as int, and recevied some return
value from sp_OAMethod. I actually used "NULL" in place
of "@retval" since I didn't want anything returned and it
worked fine, but wouldn't worked without something in this
argument position.

I'll chekc out www.aspfaq.com/2403 for more clues. THANKS

>.
>

[Brooke]

When I use> EXEC @return = sp_OAMethod

@handle, 'AddAttachment', 'C:\myfile.txt'

I get the error message> sp_OAMethod usage: ObjPointer
int IN, MethodName varchar IN [, @returnval <any> OUT [,
additional IN, OUT, or BOTH params]]

This suggests that I need an additional variable between
AddAttachment and my file name to handle a possible
returned value, something like> sp_OAMethod
@handle, 'AddAttachment', NULL, 'C:\myfile.txt'

Here the error message is> Output values of type Object
are not allowed in result sets.

ANY IDEAS ?

>.
>

[Aaron Bertrand - MVP]

> ANY IDEAS ?

Start from scratch. This code works for me, just replace with your e-mail
address.

DECLARE
@handle INT,
@return INT,
@s VARCHAR(64),
@sc VARCHAR(64),
@up CHAR(27),
@from VARCHAR(64),
@to VARCHAR(64),
@server VARCHAR(255),
@subject VARCHAR(255),
@body VARCHAR(1024)

SET @s = '"http://schemas.microsoft.com/cdo/configuration/'

SELECT
@s = 'Configuration.Fields(' + @s,
@up = 'Configuration.Fields.Update',
@to = 'youremail',
@from = 'som...@somewhere.com',
@body = CONVERT(VARCHAR, GETDATE()),
@subject = 'test.',
@server = '127.0.0.1' -- or change to relay

EXEC @return = sp_OACreate 'CDO.Message', @handle OUT
SET @sc = @s + 'sendusing").Value'
EXEC @return = sp_OASetProperty @handle, @sc, '2'
SET @sc = @s + 'smtpserver").Value'
EXEC @return = sp_OASetProperty @handle, @sc, @server
EXEC @return = sp_OAMethod @handle, @up, NULL
EXEC @return = sp_OASetProperty @handle, 'To', @to
EXEC @return = sp_OASetProperty @handle, 'From', @from
EXEC @return = sp_OASetProperty @handle, 'Subject', @subject
EXEC @return = sp_OASetProperty @handle, 'TextBody', @body
EXEC @return = sp_OAMethod @handle, 'AddAttachment', NULL, 'C:\boot.ini'
PRINT @return
EXEC @return = sp_OAMethod @handle, 'Send', NULL
IF @return <> 0
BEGIN
PRINT 'Mail failed.'
IF @to IS NULL
PRINT 'To address undefined.'
ELSE
BEGIN
IF @from IS NULL
PRINT 'From address undefined.'
ELSE
PRINT 'Check that server is valid.'
END
END
ELSE
PRINT 'Mail sent.'

EXEC @return = sp_OADestroy @handle

[Brooke]

Finally got it to work - your start from scratch worked
for the following reason:

I was evaluating return codes after each Exec and when <>
0 was aborting the process. You didn't do that until the
end. The AddAttachment method returns '-2147211470' even
when successful, and the messaging for that does not make
any sense relative to what's going on. Thus I was bogging
down before getting to the 'Send' method and couldn't
figure out why

>.
>

[Aaron Bertrand - MVP]

> end. The AddAttachment method returns '-2147211470' even
> when successful,

Well, it returns one -2... number when it fails, and another -2... number
when it's successful (didn't memorize which was which).

Sorry I didn't mention that, but you never mentioned that you were checking
for the result after that statement, so I guess we're even. <G>

A