using cursor in send mail procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using cursor in send mail procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-11 : 08:49:49

Maciek writes "Hi,

I would like to have procedure which will be sending emails to addresses returned by query.
Below procedure returns by function PRINT 2 values but it sends email just to one address.
Do I use CURSOR correctly?

Final query I will return table with row with details for variables,( each row have detail for 1 email) :
-@From varchar(100) ,
-@To varchar(100) ,
-@Subject varchar(100)=" ",
-@Body varchar(4000) =" ",

CREATE PROCEDURE [dbo].[sp_send_cdosysmail2]
@From varchar(100) ,
--@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@server varchar(100)=" "
--@username varchar (20)=" ",
--@password varchar (20)=" "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
AS

Declare @To varchar(100)

DECLARE c1 CURSOR FOR

/***sample select which returns 2 values*****/

SELECT TestUsr.adres
FROM TPIncident
INNER JOIN TestUsr
ON TestUsr.id = TPIncident.Tester
WHERE TPIncident.OwnedName = 'CR5073' OR TPIncident.OwnedName = 'CR5074'

OPEN c1

FETCH NEXT FROM c1
INTO @To

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @To

FETCH NEXT FROM c1
INTO @To

END

CLOSE c1
DEALLOCATE c1

Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
--http://schemas.microsoft.com/cdo/configuration/sendusing

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
--1 for localhost
--2 for remote SMTP
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value', 600 --cdoBasic

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', 1 --cdoBasic
--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @username
--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @password

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxyserver").Value', '161.139.66.103:6588' --ourserver:port
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxybypass").Value', '<local>' --if local IP, no need proxy
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlgetlatestversion").Value', true

-- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fiel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-08-11 : 08:50:39

You might want to take a look at this article:

http://www.sqlteam.com/item.asp?ItemID=5908

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources