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

Posted on 2009-05-11
Last Modified: 2012-05-06
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
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,
--SMTP Server
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
'your gmail email'
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
'your gmail password'
--UseSSL True/False
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("").value',
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
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
--set @output_desc = ' source: ' + @source
set @output_desc = @description
SET @output_desc = ' sp_oageterrorinfo failed'
IF not @output_desc is NULL
SET @output_mesg = 'Error'
EXEC @hr = sp_oadestroy @imsg
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

Question by:cooking
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 24359241
from what I see in that code, there seems to be nothing "special" it would not work in sql 2000...

Assisted Solution

xbrady earned 200 total points
ID: 24359263
It looks like it should work.
LVL 22

Expert Comment

ID: 24359564
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?

Author Comment

ID: 24359836
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 ?
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24360359
For SQL Server 2000 only, I would use this instead of resorting to the sp_OA* routines:

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Creating SQL script using SQL data and SQL script 8 35
Loop to go backward 90 days 2 18
string fuctions 4 25
How to construct an if else statement from existing code 6 19
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question