Alternative to sp_OACreate?

[b00gieman]

Since I cannot use xp_sendmail(in MS SQL Server 2000) , I've created my own stored procedure for sending emails.The problem is I get error messages regarding the usage of sp_OACreate , sp_OAGetErrorInfo , sp_OASetProperty , sp_OAMethod.It seems I cannot use the stored procedures from the master database.Can I create something similar to these stored procedures?

 

[SQLSister]

What error message are you getting and what is the code you are using to call the procs?

"NOTHING is more important in a database than integrity." ESquared

 

[b00gieman]

The code I'm using can be found here:

http://support.microsoft.com/kb/312839

 

[jrbarnett]

I use a slight variation on this that works fine on both SQL 7 and 2000:

CREATE PROCEDURE dbo.usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body TEXT ,
@bodytype varchar(10) 
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)

declare @server varchar (50)

set @server='smtphost.somewhere.com' -- change as necessary

exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'[/URL]

exec @hr = sp_oasetproperty @imsg, 
  'configuration.fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver").value',[/URL] 
  @server 
 
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject


-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null

-- sample error handling.
if @hr <>0 
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
--print @output
select @output = ' description: ' + @description
--print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg

John