send_db_mail issues - 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

General SQL Server Forums

New to SQL Server Programming

send_db_mail issues

Author

Topic

jasonwills
Starting Member

3 Posts

Posted - 2011-03-11 : 10:42:10

Hi All,

i have the following code that i was hoping someone could help me on - i'm new to it and i know it's not quite right, so was after some advice on how to improve it - at the moment, this will only send a single email even if there are two lines that meet the query..
i also cannot get the @NumberofDays variable to populate or the @body variable, so any help would be greatly appreciated.

Many Thanks



Use DeviceRegistrations
		DECLARE @CompanyName nvarchar (50) = NULL
		DECLARE @ProductKey nvarchar (50) = NULL
		DECLARE @ProductName nvarchar (50) = NULL
		DECLARE @StartDate nvarchar (10) = NULL
		DECLARE @EndDate nvarchar (10) = NULL
		DECLARE @Result nvarchar (10) = NULL
		DECLARE @Emailed nvarchar (50) = NULL
		DECLARE @Sent nvarchar (10) = NULL
		DECLARE @Body nvarchar (max) = NULL
		DECLARE @Subject nvarchar (max) = NULL
		DECLARE @Email nvarchar (max) = NULL
		DECLARE @NumberOfDays nvarchar (3) = NULL

Select 

@CompanyName = CompanyName,
@ProductKey = [Product Key],
@ProductName = [Product Name],
@StartDate = [Start Date],
@EndDate = [End Date],
@Emailed = [Emailed],
@Email = [Email],
@Subject = 'Product Expiring at ' + @CompanyName
	 
FROM dbo.RegistrationDetails

WHERE

datediff(d,GETDATE(),[End Date]) < 30

IF @Emailed = '0'

BEGIN

	EXEC msdb.dbo.sp_send_dbmail @profile_name='Jason',

		@recipients=@Email,
		@subject=@Subject,
		@body=@Body,
		@body_format = 'HTML';
		
    END
IF @Emailed = '0'
UPDATE

dbo.RegistrationDetails

SET [Emailed] = '1' 
WHERE datediff(d,GETDATE(),[End Date]) < 30 AND [Emailed] = '0'

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources