Need Help with sp_SMTPMail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help with sp_SMTPMail

Author

Topic

bigknoxy
Starting Member

3 Posts

Posted - 2006-03-24 : 16:26:34

When I test this with Query Analyzer I get this:
The command(s) completed successfully then

0x800401F3 ODSOLE Extended Procedure Invalid class string

this is my code. Help would be great.

CREATE Procedure sp_SMTPMail2
@ToAddress varchar(100),
@FromAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
AS
SET NOCOUNT ON
DECLARE
@object int,
@hr int,
@property varchar(255),
@return varchar(255),
@src varchar(255),
@desc varchar(255),
@Mailserver varchar(100)

SET @Mailserver = 'mailserver.com'

-- First, create the object.
EXEC @hr = master..sp_OACreate 'Persits.MailSender', @object OUT
IF @hr <> 0
BEGIN
-- Report any errors
EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
GOTO END_ROUTINE
END
ELSE
-- An object is successfully created.
BEGIN
-- Set properties
EXEC @hr = master..sp_OASetProperty @object, 'Host', @Mailserver
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = master..sp_OASetProperty @object, 'From', @FromAddress
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = master..sp_OASetProperty @object, 'Subject', @Subject
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = master..sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO CLEANUP

EXEC @hr = master..sp_OAMethod @object, 'AddAddress', NULL, @ToAddress
IF @hr <> 0 GOTO CLEANUP

EXEC @hr = master..sp_OAMethod @object, 'Send', NULL
GOTO CLEANUP

END

CLEANUP:
-- Check whether an error occurred.
IF @hr <> 0
BEGIN
-- Report the error.
EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
END

-- Destroy the object.
BEGIN
EXEC @hr = master..sp_OADestroy @object
-- Check if an error occurred.
IF @hr <> 0
BEGIN
-- Report the error.
EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
END
END
END_ROUTINE:
RETURN
GO

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-03-27 : 07:35:36

Its seems like SMTP service is not installed on the server where you executing this stored procedure.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

bigknoxy
Starting Member

3 Posts

Posted - 2006-03-27 : 09:41:22

I'm sorry but I am really new to this...
How would I check to see if it is installed?
I believe the server has outlook installed would that not be it?
Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-03-27 : 17:47:26

quote:
Originally posted by sachinsamuel

Its seems like SMTP service is not installed on the server where you executing this stored procedure.

What about the error message gives you this idea ??????

I'd ignore that little bit of advice.

Invalid class string implies that the COM object isn't registered. Did you run an installer for the Persists component ? Or did you use regsvr32 to register it ?

Can you write some VBS to test the component works on the server ?

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

svicky9
Posting Yak Master

232 Posts

Posted - 2006-03-27 : 23:24:59

This article from microsoft may help you

http://support.microsoft.com/default.aspx/kb/243899

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-03-28 : 00:44:42

Invalid class string error occurs when the you are trying create an instance or object of a component which is not registered.

To register SMTP, go to add remove programe. Then add remove components. This will list down all the windows component. The checked ones are those which are installed and the unckecked ones are those which are not in installed. Click Internet Information services(IIS) and then click Details button. Check if SMTP service is checked or not. If not then install and the try running your script.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-03-28 : 01:14:42

quote:
Originally posted by sachinsamuel

Invalid class string error occurs when the you are trying create an instance or object of a component which is not registered.

Is there an echo in here ?

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

bigknoxy
Starting Member

3 Posts

Posted - 2006-03-29 : 09:57:04

Thanks for the help guys... I was trying to use the dll above and also 'SMTPsvg.Mailer' and I registered them but just wouldn't work for me for some reason, so I ended up using CDO when I found it was already registered on the server and I wouldn't have to put it on myself and CDO worked for me the very first time...
Here is the link if anyone is interested:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=670&lngWId=5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources