Great, thanks for this Carlosdl, that combined with the little info i already had, i have it working a treat now – thankyou very much.

Jason

Actually, one more quick question – if i wanted to combine two columns [column1] + [column2] – where would i put it in this query – ideally i want to be able to combine two columns and use that in the @body section of the email – (where you have @body = @name) would it be after the first WHERE statement?

Cheers again

jason

You could do it in the query definition, or when calling the sp_send_dbmail procedure (I would probably opt for the latter)

Example 1:

SELECT id, col_x + col_y AS email_body
...
FETCH NEXT FROM yourCursor INTO @id,@email_body
...
    EXEC sp_send_dbmail @profile_name='YourProfile',
    @recipients='your@recipients.com',
    @subject='The subject',
    @body=@email_body
...

Example 2:

SELECT id, col_x , col_y
...
FETCH NEXT FROM yourCursor INTO @id, @col_x, @col_y
    EXEC sp_send_dbmail @profile_name='YourProfile',
    @recipients='your@recipients.com',
    @subject='The subject',
    @body=@col_x + @col_y
...

mm, thanks again Carlosdl, i did try that, and i get an incorrect syntax on the + symbol…..

True. Try using an additional variable.

Something like this:

...
SELECT @mail_body = @col_x + @col_y
...

and then use the new variable when executing sp_send_dbmail.

Hi Carlosdl, soo frustrating as i’m nearly there( with a lot of your help) – i now get “variable assignment is not allowed in a cursor declaration” if i try to do it in the existing SELECT statement. – is cursor the best way to do it – i have heard people say to use the while statement rather than cursor – but i’m stuck either way.

thanks again for all your help Carolsdl.

Jason

Can we take a look at your current code ? Can you post it ?

Since you need to send an e-mail for each record, I can’t think of a better way to do it, than using a cursor.

Hi Carlosdl;

here is the code that i am using:

USE [DeviceRegistrations]
GO
/****** Object:  StoredProcedure [dbo].[expiring]    Script Date: 10/04/2010 16:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[expiring] AS
		DECLARE @CompanyName nvarchar (50) = NULL
		DECLARE @ProductKey nvarchar (50) = NULL
		DECLARE @MaintenanceStartDate nvarchar (10) = NULL
		DECLARE @MaintenanceEndDate nvarchar (10) = NULL
		DECLARE @Emailed int = NULL
		DECLARE @Subject nvarchar (max) = NULL
		DECLARE @FullSubject nvarchar (max) = NULL
		DECLARE @SalesManEmail nvarchar (max) = NULL
		DECLARE @NumberOfDays int = NULL
		DECLARE @SalesManFirstName nvarchar (50) = NULL
		DECLARE @SalesManLastName nvarchar (50) = NULL
		DECLARE @SalesMan nvarchar (max) = NULL
		DECLARE @Body nvarchar (max) = NULL
		DECLARE @RegID int = NULL 
		DECLARE yourCursor CURSOR
     FOR 
     
		SELECT	[RegistrationIndex],
				[CompanyName],
				[Product Key],
				[Maintenance End Date],
				[SalesManEmail],
				[SalesManFirstName],
				[SalesManLastName]	
     
     FROM RegistrationDetails 
     WHERE (datediff(d,GETDATE(),[eCopy Maintenance End Date]) < 30) AND Emailed = 0;
     
 OPEN yourCursor
 FETCH NEXT FROM yourCursor INTO	@RegID,
									@CompanyName,
									@ProductKey,
									@MaintenanceEndDate,
									@SalesManEmail,
									@SalesManFirstName,
									@SalesManLastName
 WHILE @@FETCH_STATUS = 0
 BEGIN
     EXEC msdb.dbo.sp_send_dbmail @profile_name='Jason',

		@recipients = @SalesManEmail,
		@subject = @CompanyName,
		@body = @CompanyName,
		@body_format = 'HTML';
		
     UPDATE RegistrationDetails SET Emailed = 1 WHERE RegistrationIndex = @RegID
     FETCH NEXT FROM yourCursor INTO	@RegID,
										@CompanyName,
										@ProductKey,
										@MaintenanceEndDate,
										@SalesManEmail,
										@SalesManFirstName,
										@SalesManLastName
 END
 CLOSE yourCursor
 DEALLOCATE yourCursor

i’d like to be able to put a string in the subject line + @CompanyName
and to also use HTML in the body (not crucial)

hope it all makes sense.

Jason

Ahh, thank you Caroldl,

worked a treat, i wasn’t sure where i could put it in the whole stored procedure.
Thankyou for all your help on this.

Much appreciated.

jason