Hello,
I am attempting to send emails using T-SQL (in a SQLServerAgent Job)
using the stored procedure sp_SendSMTPMail. I created the stored proc
using the following script that I got off a post here on google.
CREATE PROCEDURE sp_SendSMTPMail (@From varchar(255),
@To varchar(8000),
@Subject varchar(255),
@Body varchar(8000),
@cc varchar(8000) = NULL,
@Bcc varchar(8000) = NULL)
AS
DECLARE @newmail int
DECLARE @result int
-- Create the CDONTS.Newmail object
EXEC @result = sp_OACreate 'CDONTS.NewMail', @newmail OUT
IF @result <> 0
BEGIN
PRINT 'Error creating object'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Check the Optional Properties
IF not @cc = NULL
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'cc', @cc
IF @result <> 0
BEGIN
PRINT 'Error setting [cc] property'
-- EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
IF not @Bcc = NULL
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'Bcc', @Bcc
IF @result <> 0
BEGIN
PRINT 'Error setting [Bcc] property'
-- EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
--set the non-optional properties
EXEC @result = sp_OASetProperty @newmail, 'From', @From
EXEC @result = sp_OASetProperty @newmail, 'To', @To
EXEC @result = sp_OASetProperty @newmail, 'Subject', @Subject
EXEC @result = sp_OASetProperty @newmail, 'Body', @Body
-- Send the message...
EXEC @result = sp_OAMethod @newmail, 'Send'
IF @result <> 0
BEGIN
PRINT 'Error sending message'
-- EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Destroy the object.
EXEC @result = sp_OADestroy @newmail
IF @result <> 0
BEGIN
PRINT'Error destroying object'
-- EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
GO
______________________________________________
This worked perfectly in testing at our office but when we shipped to
the client it produced the error 'Error creating object'. We sent the
client the script to create the sp_displayoaerrorinfo proc and got the
following error message.
Error creating object-2147221005
OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string
Now this suggested to me that the for some reason we were unable to
reference the CDO library, or it wasn't installed. However they have
Outlook 2000 installed with CDO installed with it. I've had had them
remove and reinstall the CDO library but that hasn't helped.
Other thing I though of was a permission type problem. They are using
NT authentication for there logins into SQL server and are logged on
the server as Administrator who I would assume has all required
permissions.
Any suggestions for either determining the cause of them problems or a
solution?
Thanks in advance.
Bob.
