Automatic Emails 1

[spwiz]

This is driving me mad

I've been trying to write an stored procedure that loops through a table and emails them some information relavant to them.

(Its a recruitment site that checks candidates skills agains't current job requirments)

2 problems

1. I have an sp that sends an email using CDONTS. How do I call the sp when i'm using a select statement. I could cheat and use a cursor (YUK!!!)

2. How would i automate this to run once a day..

Cheers
robp

 

[rac2]

Hello robp,

Use SQL Server Agent to schedule a stored procedre to run.
You will find it in Enterprise Manager by expanding Management.

 

[tlbroadbent]

We have numerous processes that send email to a list of people. The T-SQL code is in stored procedures. The procedure is run via a scheduled job.

The following is an example of such a stored procedure.

---------------------------------

CREATE PROCEDURE [dbo].[sp_SendEmail] AS

Declare
@Address as varchar(120),
@Subject as varchar(500),
@Message varchar(5000),
@id int,
@statuscode int

Set nocount on

Declare curList Cursor for
Select
id,
isnull(Address,'') As Address,
Subject,
MessageText
From NotifyQueue
Where isnull(SendDate,'') = ''

Open curList
Fetch Next From curList
into @id, @Address, @Subject, @Message
While (@@FETCH_STATUS <> -1)
begin
print @address
if @Address <> ''
begin
exec @StatusCode =
master.dbo.xp_sendmail
@recipients=@Address,
@subject=@Subject,
@message=@Message,
@attachments='instructions.txt'
end
Else
Begin
Set @statuscode=1
End

Update notifyQueue Set
SendDate = GetDate(),
SendStatus= @statuscode
Where notifyqueue.id = @id

FETCH NEXT From curList
Into @id, @Address, @Subject, @Message

End

Close curList
Deallocate curlist
Set nocount off
GO
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.