Solved

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

Posted on 2009-05-11
5
219 Views
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
 

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

0
Comment
Question by:cooking
5 Comments
 
LVL 142

Accepted Solution

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

Assisted Solution

by:xbrady
xbrady earned 200 total points
Comment Utility
It looks like it should work.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
 

Author Comment

by:cooking
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now