SQL 2005 Database Email

See more:

SQL-Server-2005

SQL-Server

email

I am trying to iterate through database tables and send email to three different managers. I am getting an error when tring to create a stored procedure for the following.

Msg 102, Level 15, State 1, Procedure usp_test_proc, Line 92
Incorrect syntax near ';'.

Really don't know why and would appreciate any help you can provide.

DECLARE 
    @EmployeeID varchar (50),
    @FunctionalMgrID varchar (50),
    @Job varchar (6),
    @WBS varchar (5),
    @LastworkDate datetime,
    @EmployeeName varchar (50),
    @FunctionalMgrName varchar (50),
    @FunctionalMgrEmail varchar (255),
    @TalentMgrName varchar (50),
    @TalentMgrEmail varchar (255),
    @AssetTag varchar (50),
    @AssetModelName varchar (128),
    @AssetMgrName varchar (50),
    @AssetMgrEmail varchar (255),
    @mailsubject varchar (200),
    @mailbody varchar (MAX);
/* Cursor - looping through specified CasualEmployee Database Records */
DECLARE Emails CURSOR FAST_FORWARD READ_ONLY
FOR
/* Statement to fill cursor with data */
SELECT 
    [CEmpDetails].[EmployeeID],
    [FunctionalMgrID],
    [Job],
    [WBS],
    [LastworkDate],
    [EmployeeName],
    [FunctionalMgrName],
    [FunctionalMgrEmail],
    [TalentMgrName],
    [TalentMgrEmail],       
    [AssetTag],
    [AssetModelName],
    [AssetMgrName],
    [AssetMgrEmail]
FROM [CDB].[dbo].[CasualEmployeeDetails] Inner Join [CEmpAssets] 
    on [CEmpDetails].EmployeeID = [CEmpAssets].EmployeeID;
/* Open the cursor and fill it with the first set of values */
OPEN Emails;
FETCH NEXT FROM Emails into 
    @EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName, 
    @FunctionalMgrName, @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail,  
    @AssetTag, @AssetModelName, @AssetMgrName, @AssetMgrEmail
/*Prepare and send email */
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @mailsubject = 'Notification: blah blah ' + @EmployeeName + ':',
           @mailbody = 'Records indicate blah blah blah blah blah:<br />
                -- is something else here<br />
                -- something else here<br />
                -- but has stuff assigned<br />                 
                <br />Employee ID:  ' + @EmployeeID +
                '<br />Employee Name:  ' + @EmployeeName +
                '<br />Last Work Date:  ' + convert(varchar(30), @LastworkDate, 101) +
                '<br />Last Work Job-WBS:  ' + @Job + '-' + @WBS +
                '<br />
                <br />Manager: please coordinate with blah blah blah blah 
                blah blah blah blah blah blah:
                -----------------------------------------------------
                -----------------------------<br /><br />'
                + @AssetTag + '     ' + @AssetModelName ;
     EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'CasualAsset',
       @recpients = @TalentMgrEmail,
       @copy_recipients = @AssetMgrEmail, 
       @subject = @mailsubject,
       @body_format = 'HTML',
       @body = @mailbody ;
    FETCH NEXT FROM Emails INTO 
        @EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName, 
        @FunctionalMgrName, @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail,       
        @AssetTag, @AssetModelName, @AssetMgrName, @AssetMgrEmail;
END;
 CLOSE Emails;
      deallocate Emails;