should the following code work for sql server 2000 or just for sql server 2005?

hi can someone tell me if the following code works for sql server 2000 and if no what would be a similar code.. ? thanks in advance
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
 
@from varchar(500) ,
 
@to varchar(500) ,
 
@subject varchar(500),
@body varchar(4000) ,@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output
 
AS
 
DECLARE @imsg int DECLARE @hr int
 
DECLARE @source varchar(255)
 
DECLARE @description varchar(500)
 
EXEC @hr = sp_oacreate 'cdo.message', @imsg out
 
--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
 
EXEC @hr = sp_oasetproperty @imsg,
 
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
 
--SMTP Server
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
 
'smtp.gmail.com'
 
--UserName
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
 
'your gmail email'
 
--Password
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
 
'your gmail password'
 
--UseSSL True/False
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
 
'True'
 
--PORT
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
 
'587''
 
--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
 
'1'
 
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
 
SET @output_mesg = 'Success'
 
-- sample error handling.
IF @hr <>0 SELECT @hr
 
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
 
IF @hr = 0
BEGIN
 
--set @output_desc = ' source: ' + @source
set @output_desc = @description
 
END
 
ELSE
 
BEGIN
 
SET @output_desc = ' sp_oageterrorinfo failed'
 
END
 
IF not @output_desc is NULL
 
SET @output_mesg = 'Error'
 
END
 
EXEC @hr = sp_oadestroy @imsg
 
END
 
 
 
To Call
 
DECLARE @out_desc varchar(1000),
 
@out_mesg varchar(10)
 
 
 
EXEC sp_send_cdosysmail 'Your Email', 'Recepient email','Subject', 'Body', 'HTMLBody', @output_desc = @out_desc output, @output_mesg = @out_mesg output
 
PRINT @out_mesg

Open in new window

cookingAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
from what I see in that code, there seems to be nothing "special" it would not work in sql 2000...
0
 
xbradyConnect With a Mentor Commented:
It looks like it should work.
0
 
8080_DiverCommented:
My question to you is, if you need to put this on SS2K, do you not have access to an SS2K box?  
If so, try creating the SP on the SS2K box using Enterprise Manager. . . if EM whines about the code, it won't work but it also shouldn't be saved as an SP.
If not, how do you plan to put it on an SS2K box?
0
 
cookingAuthor Commented:
yes this code works on sql server 2000 i had misleading information that it was for sql 2005 and above only... the problem is i am getting permission denied because ole automation procedures are not enabled on the server and i cannot enable them. i only have all the permissions on my database not on master database or msdb ... is there another way to get this job done using a stored procedure ?
0
 
Anthony PerkinsConnect With a Mentor Commented:
For SQL Server 2000 only, I would use this instead of resorting to the sp_OA* routines:
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
0
All Courses

From novice to tech pro — start learning today.