Database Mail - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Database Mail

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-01-11 : 06:00:47

Hi There,

We have moved from sql 2000 to sql 2008.
We were using xp_smtp_sendmail but now need to use database mail (sp_send_dbmail)

One of our stored procedures does a select statement and iterates through the results from the query and sends out an email for each record. I cannot get this to work.
I have tested with a simple:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'steve.wilson@mycompany.com',@body='Message Body',
@sensitivity ='Personal',
@importance ='High',
@copy_recipients ='steve.wilson@mycompany.com',@subject ='Message Subject',@profile_name ='Database-mailProfile';

That works fine.
But my code to send an email using a query does not work. This is the code:
USE [BPNAGAS]
GO
/****** Object: StoredProcedure [dbo].[sp_CompDateWarnEmail] Script Date: 01/11/2011 09:50:20 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CompDateWarnEmail]
AS
SET NOCOUNT ON
DECLARE @rc int
DECLARE @bodyText VARCHAR(1024)

DECLARE @ID varchar(10)
DECLARE @Desc varchar(50)
DECLARE @SubjectMessage varchar(50)
DECLARE @PCompDate varchar(50)
DECLARE @FName varchar(50)
DECLARE @LName varchar(50)
DECLARE @ActioneeEmail varchar(100)
DECLARE @Warn varchar(10)

DECLARE DemoCursor CURSOR FOR
SELECT
CAST(tbMatTrack.ActionId As VarChar) As ActionId,
tbMatTrack.Description,
CAST (CONVERT(varchar(10), tbMatTrack.PlannedCompletionDate, 103) AS VarChar)As 'PCompDate',
tbMatOrg.FirstName,
tbMatOrg.LastName,
tbMatOrg.EmailAddress,
LTrim(datediff(day,getdate(),tbMatTrack.PlannedCompletionDate)) As 'Warn'
FROM
tbMatTrack Inner Join
tbMatOrg On tbMatTrack.ActioneeId = tbMatOrg.PersonnelId

OPEN DemoCursor

FETCH DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn
Set @SubjectMessage = 'Action: ' + @ID + ' Description : ' + @Desc
Set @bodyText = '<HTML>'
+ '<BODY>'
+ 'You are logged as the actionee for action : ' + @ID + ' ( ' + @Desc + ' ).'
+ 'This action is nearing the planned completion date of ' + @PCompDate + ''
+ '</BODY>'
+ '</HTML>'

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'steve.wilson@mycompany.com',
@subject = @SubjectMessage,
@profile_name ='SqlAlerts';
SELECT RC = @rc
FETCH DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn
Set @SubjectMessage = 'Action: ' + @ID + ' Description : ' + @Desc
Set @bodyText = '<HTML>'
+ '<BODY>'
+ 'You are logged as the actionee for action : ' + @ID + ' ( ' + @Desc + ' ).'
+ 'This action is nearing the planned completion date of ' + @PCompDate + ''
+ '</BODY>'
+ '</HTML>'
END

CLOSE DemoCursor
DEALLOCATE DemoCursor

Can anyone see why this is not working. The query returns 41 rows so should send 41 emails.

Thanks for any help you can give.

Best Regards,

Steve

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-11 : 08:13:17

What is the "SELECT RC = @rc" inside the while-loop for? The syntax doesn't make any sense...at least not to me.

I'd also change the procedure a little:

...
OPEN DemoCursor
FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn 

WHILE @@FETCH_STATUS = 0
BEGIN
	Set @SubjectMessage = 'Action: ' + @ID + ' Description : ' + @Desc
	Set @bodyText = '<HTML>'
		+ '<BODY>'
		+ '<p>You are logged as the actionee for action : ' + @ID + ' ( ' + @Desc + ' ).</p>' 
		+ '<p>This action is nearing the planned completion date of ' + @PCompDate + '</p>'
		+ '</BODY>'
		+ '</HTML>'

	EXEC msdb.dbo.sp_send_dbmail
		@recipients = N'steve.wilson@mycompany.com',
		@subject = @SubjectMessage,
		@profile_name ='SqlAlerts';

	FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn 
END
...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-01-12 : 06:49:13

Hi Lumbago,
Thanks for the reply.

I have adjusted the code to use your code.
When I run the stored procedure I get a "Query Completed Successfully" message but I do not receive the emails. This is my code now:

USE [MMS-ActionTracker]
GO
/****** Object: StoredProcedure [dbo].[sp_CompDateWarnEmail] Script Date: 01/12/2011 11:07:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CompDateWarnEmail]
AS
SET NOCOUNT ON
DECLARE @rc int
DECLARE @bodytext VARCHAR(1024)

DECLARE @ID varchar(10)
DECLARE @Desc varchar(50)
DECLARE @SubjectMessage varchar(50)
DECLARE @PCompDate varchar(50)
DECLARE @FName varchar(50)
DECLARE @LName varchar(50)
DECLARE @ActioneeEmail varchar(100)
DECLARE @Warn varchar(10)

DECLARE DemoCursor CURSOR FOR
SELECT
CAST(tbMatTrack.ActionId As VarChar) As ActionId,
tbMatTrack.Description,
CAST (CONVERT(varchar(10), tbMatTrack.PlannedCompletionDate, 103) AS VarChar)As 'PCompDate',
tbMatOrg.FirstName,
tbMatOrg.LastName,
tbMatOrg.EmailAddress,
LTrim(datediff(day,getdate(),tbMatTrack.PlannedCompletionDate)) As 'Warn'
FROM
tbMatTrack Inner Join
tbMatOrg On tbMatTrack.ActioneeId = tbMatOrg.PersonnelId
WHERE datediff(day,getdate(),tbMatTrack.PlannedCompletionDate) < 0 AND ActualCompletionDate is Null OR ActualCompletionDate = 0

OPEN DemoCursor
FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn

WHILE @@FETCH_STATUS = 0
BEGIN
Set @SubjectMessage = 'Action: ' + @ID + ' Description : ' + @Desc
Set @bodyText = '<HTML>'
+ '<BODY>'
+ 'You are logged as the actionee for action : ' + @ID + ' ( ' + @Desc + ' ).'
+ 'This action is nearing the planned completion date of ' + @PCompDate + ''
+ '</BODY>'
+ '</HTML>'

EXEC msdb.dbo.sp_send_dbmail
@recipients = N'steve.wilson@mycompany.com',
@subject = @SubjectMessage,
@profile_name ='SqlAlerts';

FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @ActioneeEmail, @Warn
END

CLOSE DemoCursor
DEALLOCATE DemoCursor

Thanks again for your help.

Best Regards,

Steve

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 07:13:10

Well...you need to do some debugging then. Does sp_send_dbmail work by itself with hardcoded values? Is the procedure even run inside the while-loop? Do you find the mail you tried to send using this query? ->

select * from msdb.dbo.sysmail_allitems

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources