cooking
asked on
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?