CDOSYS Mail Using SQL Server

The procedure works without problems for the basic 'textbody' and 'htmlbody' type messages. The problem is I would like to get the CreateMHTMLBody method working. This works beautifully as a VBScript which I'm attempting to convert to SQL.

CREATE PROCEDURE usp_send_cdosysmail
@.From_Addr VARCHAR(500) ,
@.To_Addr VARCHAR(500) ,
@.Subject VARCHAR(500),
@.Body VARCHAR(4000) ,
@.SMTPserver VARCHAR(25) = 'localhost',
@.BodyType VARCHAR(10) = 'textbody'
AS

DECLARE @.imsg INT
DECLARE @.hr INT
DECLARE @.source VARCHAR(255)
DECLARE @.description VARCHAR(500)
DECLARE @.output VARCHAR(1000)

EXEC @.hr = sp_oacreate 'cdo.message', @.imsg out
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @.SMTPserver
EXEC @.hr = sp_oamethod @.imsg, 'configuration.fields.UPDATE', NULL

EXEC @.hr = sp_oasetproperty @.imsg, 'to', @.To_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'from', @.From_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'subject', @.Subject

IF @.BodyType <> 'URL'
BEGIN
EXEC @.hr = sp_oasetproperty @.imsg, @.BodyType, @.Body
END
ELSE
BEGIN
-- Generate The Body From The Input URL
EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''
END

EXEC @.hr = sp_oamethod @.imsg, 'send', NULL

When I test the "EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''" step it gives me a blank email.
The return code (@.hr) for that step is -2147211483 (x80042725).
There is no error message. Search as I might, I haven't been able to find a reference to that error.I found the answer:
EXEC @.hr = sp_OAMethod @.imsg, 'CreateMHTMLBody', NULL, @.body, 0, '',''
works!!!!

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
ThanksHi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
Thanks
Hi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
ThanksHi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>