May 24, 2012 at 2:58 am
Hello all,
I hope someone can help me.
I have a table that holds peoples jobs and their deadlines. I want to email the people on a daily basis whose jobs deadlines are on that day.
What I have so far:
DECLARE @toemail varchar(150)
DECLARE email_cursor CURSOR FOR SELECT email FROM vw_expiring_jobs
OPEN email_cursor
FETCH NEXT FROM email_cursor
INTO @toemail
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @toemail
DECLARE @register_detail varchar(250)
DECLARE @body varchar(250)
SELECT @job_detail = RTRIM(Job_name) +' '+ RTRIM(short_desc) FROM vw_expiring_jobs WHERE email = @toemail
SET @body = 'You have Jobs that are due to be completed today on the register: ' + @job_detail
EXEC msdb.dbo.sp_send_dbmail
@recipients = @toemail,
@subject = 'Expiring Jobs',
@body = @body
FETCH NEXT FROM email_cursor
INTO @toemail
END
CLOSE email_cursor
DEALLOCATE email_cursor
vw_expiring_jobs is a view I set up that returns all expiring jobs for that day.
The script finds all the emails of the people that have expiring jobs correctly. The problem I am having is setting the variable @job_detail for each person. It's emailing all the correct people but it's sending them all the same job detail (the last returned value if I run the second query manually)??? and that's where I am lost...
I really hope someone can help.
Many Thanks
another thing I should point out is that a person may be in the view more than once with more than one job. I want it to email them separately for each instance.
I hope I made sense.
Many Thanks
May 24, 2012 at 4:06 am
WiRL (5/24/2012)
Hello all,I hope someone can help me.
I have a table that holds peoples jobs and their deadlines. I want to email the people on a daily basis whose jobs deadlines are on that day.
What I have so far:
DECLARE @toemail varchar(150)
DECLARE email_cursor CURSOR FOR SELECT email FROM vw_expiring_jobs
OPEN email_cursor
FETCH NEXT FROM email_cursor
INTO @toemail
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @toemail
DECLARE @register_detail varchar(250)
DECLARE @body varchar(250)
SELECT @job_detail = RTRIM(Job_name) +' '+ RTRIM(short_desc) FROM vw_expiring_jobs WHERE email = @toemail
SET @body = 'You have Jobs that are due to be completed today on the register: ' + @job_detail
EXEC msdb.dbo.sp_send_dbmail
@recipients = @toemail,
@subject = 'Expiring Jobs',
@body = @body
FETCH NEXT FROM email_cursor
INTO @toemail
END
CLOSE email_cursor
DEALLOCATE email_cursor
vw_expiring_jobs is a view I set up that returns all expiring jobs for that day.
The script finds all the emails of the people that have expiring jobs correctly. The problem I am having is setting the variable @job_detail for each person. It's emailing all the correct people but it's sending them all the same job detail (the last returned value if I run the second query manually)??? and that's where I am lost...
I really hope someone can help.
Many Thanks
another thing I should point out is that a person may be in the view more than once with more than one job. I want it to email them separately for each instance.
I hope I made sense.
Many Thanks
why not just change the cursor to retreive all of the columns you need rather than just the email colum
change
DECLARE email_cursor CURSOR FOR SELECT email FROM vw_expiring_jobs
to
DECLARE email_cursor CURSOR FOR SELECT email,RTRIM(short_desc),RTRIM(Job_name) FROM vw_expiring_jobs
then you can ditch that second lookup to the table
MVDBA
May 24, 2012 at 7:19 am
Hello Michael,
I tried DECLARE email_cursor CURSOR FOR SELECT email, @job_detail = RTRIM(Job_name) +' '+ RTRIM(short_desc) FROM vw_expiring_jobs
but recieve an error stating I can't set variables within a CURSOR?
How else can I set the variables using the same query?
Many Thanks
May 24, 2012 at 7:42 am
Something like this
DECLARE @toemail varchar(150), @jobname nvarchar(150), @short_desc nvarchar(150)
DECLARE email_cursor CURSOR FOR SELECT email, RTRIM(Job_name), RTRIM(short_desc) FROM vw_expiring_jobs
OPEN email_cursor
FETCH NEXT FROM email_cursor
INTO @toemail, @jobname, @short_desc
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @toemail
DECLARE @body varchar(250)
SET @body = 'You have Jobs that are due to be completed today on the register: ' + @Job_name +' '+ @short_desc
EXEC msdb.dbo.sp_send_dbmail
@recipients = @toemail,
@subject = 'Expiring Jobs',
@body = @body
FETCH NEXT FROM email_cursor
INTO @toemail, @jobname, @short_desc
END
CLOSE email_cursor
DEALLOCATE email_cursor
May 24, 2012 at 8:35 am
WiRL (5/24/2012)
Hello Michael,I tried DECLARE email_cursor CURSOR FOR SELECT email, @job_detail = RTRIM(Job_name) +' '+ RTRIM(short_desc) FROM vw_expiring_jobs
but recieve an error stating I can't set variables within a CURSOR?
How else can I set the variables using the same query?
Many Thanks
look at my post again.... i didn't do "@job_detail="
i did
DECLARE email_cursor CURSOR FOR SELECT email, RTRIM(Job_name) ,RTRIM(short_desc) FROM vw_expiring_jobs
MVDBA
May 24, 2012 at 1:36 pm
A no-cursor option:
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + 'EXEC msdb.dbo.sp_send_dbmail
@recipients = ''' + REPLACE(email,'''','''''') + ''',
@subject = ''Expiring Jobs'',
@body = ''You have Jobs that are due to be completed today on the register: ''' +
RTRIM(REPLACE(Job_name,'''','''''')) + ' ' + RTRIM(REPLACE(short_desc,'''','''''')) + ';
'
FROM vw_expiring_jobs
EXEC(@sql);
EDIT: just noticed it was SQL 2005, get rid of variable init on declare
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 2:56 am
Thanks Anthony and Mike I now have a working solution.
DECLARE @toemail varchar(150), @job_detail varchar(300), @body varchar(400)
DECLARE email_cursor CURSOR FOR SELECT email, RTRIM(job_name) +' '+ RTRIM(short_desc) as job_detail FROM vw_expiring_jobs
OPEN email_cursor
FETCH NEXT FROM email_cursor
INTO @toemail, @job_detail
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @toemail
SET @body = 'You have Jobs that are due to be completed today on the register: ' + @job_detail
EXEC msdb.dbo.sp_send_dbmail
@recipients = @toemail,
@subject = 'Expiring Jobs',
@body = @body
FETCH NEXT FROM email_cursor
INTO @toemail, @job_detail
END
CLOSE email_cursor
DEALLOCATE email_cursor
Might have a play around with a non-cursor version thanks opc.three
Cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply