Link to home
Create AccountLog in
Avatar of Xencor
XencorFlag for United States of America

asked on

How to modify SQL Server 2005 Database Mail to include database data in the Message Body

I am creating a SQL Server Agent scheduled task that should send an email alert based on a datediff criteria. The email address is pulling out of a field in an "Employees" table. The email alerts seem to be going out properly to the correct email addresses. But, I cannot figure out how to modify my "Message Body" portion to contain database data that pertains to the record that is selected, such as a Certification Title and Cert. Description.
DECLARE @Email varchar(255)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Email_Address
FROM Certifications C, Employees E 
where C.Employee_ID=E.Employee_ID 
	and DATEDIFF(day,Expiration_Date,getdate()) <= 30 
 
OPEN c1
 
FETCH NEXT FROM c1
 
INTO @Email
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@Email,
@body='Message Body ',
 
@subject='Message Subject',
@profile_name='Certification Database E-mailer'
 
PRINT @Email
 
FETCH NEXT FROM c1
INTO @Email
 
END
 
CLOSE c1
DEALLOCATE c1

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

DECLARE @Email varchar(255), @Title varchar(100), @description varchar(100)
declare @body varchar(max)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Email_Address, CertTitle, CertDescription
FROM Certifications C, Employees E
where C.Employee_ID=E.Employee_ID
        and DATEDIFF(day,Expiration_Date,getdate()) <= 30
 
OPEN c1
 
FETCH NEXT FROM c1
 
INTO @Email, @Title, @description
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
set @body = 'Your title is: ' + @Title + ' and desc is: ' + @description

EXEC msdb.dbo.sp_send_dbmail
@recipients=@Email,
@body= @body,
 
@subject='Message Subject',
@profile_name='Certification Database E-mailer'
 
PRINT @Email
 
FETCH NEXT FROM c1
INTO @Email, @Title, @description
 
END
 
CLOSE c1
DEALLOCATE c1
Avatar of Xencor

ASKER

Hi , added your suggested changes and I recieved this error in the history:

Incorrect syntax near 'set@body' (SQL STATE 42000)(Error102)
 Incorrect syntax near the keyword 'CLOSE (SQL STATE 42000)(Error 156) The step failed
DECLARE @Email varchar(255), @Title varchar(100),@description varchar(100)
DECLARE @body varchar(max)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Email_Address, Certfication_Prefix, Certification_Description
FROM Certifications C, Employees E 
where C.Employee_ID=E.Employee_ID 
	and DATEDIFF(day,Expiration_Date,getdate()) <= 30 
 
OPEN c1
 
FETCH NEXT FROM c1
 
INTO @Email, @Title, @description
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
set@body = 'Your title is: '+@Title  + ' and description is: '+ @description
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@Email,
@body=@body,
 
@subject='Message Subject',
@profile_name='Certification Database E-mailer'
 
PRINT @Email
 
FETCH NEXT FROM c1
INTO @Email, @Title,@description
 
END
 
CLOSE c1
DEALLOCATE c1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Xencor

ASKER

The error went away, but I think there might be something with the logic of my SELECT statement. If an expiration date falls within that criteria of 30 days, an email is blasted for every record that pertains to that employee.
Sounds like a business issue you need to handle.  Glad to help you with the database mail issue though.  If you have more problems with your select, post a new question.

Thanks,
Tim