Link to home
Create AccountLog in
Avatar of Annette Wilson, MSIS
Annette Wilson, MSISFlag for United States of America

asked on

SQL 2005 Database Mail

1. I am having problems with the following code getting an incorrect syntax error when I try to add the employee name on the subject line of the email.

2.  After fixing the syntax error, I would like to create a temporary table and iterate through the table for result sets of assets for each employee then distinct email addresses.
DECLARE @TalentMgrEmail varchar (255),
        @FunctionalMgrEmail varchar (255),
        @AssetMgrEmail varchar (255),
        @EmployeeID varchar (50),
        @EmployeeName varchar (50),
        @LastworkDate datetime,
        @Job varchar (6),
        @WBS varchar (5),
        @AssetTag varchar (50),
        @AssetModelName varchar (128)
        

/* 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].[CEmpDetails] 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 @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

/*Prepare and send email */
WHILE @@FETCH_STATUS = 0
  BEGIN
     EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'MyProfileName',
       @recpients = @TalentMgrEmail,
       @copy_recipients = @AssetMgrEmail, 
       @subject = 'Notification: blah blah ' + @EmployeeName + ':',
       @body = 'Records indicate blah blah blah blah blah:
                -- is something else here
                -- something else here
                -- but has stuff assigned
                Employee ID:  ' + @EmployeeID +
                'Employee Name:  ' + @EmployeeName +
                'Last Work Date:  ' + @LastworkDate +
                'Last Work Job-WBS:  ' + @Job + '-' + @WBS +
                'Manager: please coordinate with blah blah blah blah blah blah blah blah blah blah blah:
                  -------------------------------------------------------------------------------------------'
                + @AssetTag + '     ' + @AssetModelName ;
        
      
      FETCH NEXT FROM Emails INTO @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

      CLOSE Emails
      END

Open in new window

Avatar of Jini Jose
Jini Jose
Flag of India image

change the code like below

inside the cursor add declare variable

declare @sub varchar(200)
set @sub='Notification: blah blah ' + @EmployeeName + ':'

then use the new variable @sub

@subject = @sub,
Avatar of Annette Wilson, MSIS

ASKER

gmailjini,
Thank you for your feedback.  I changed the code accordingly but still get an incorrect syntax near 'Emails' error, pointing to the last line of code.

Do you know how I can convert this to using a temp table instead?   Even when I correct the syntax completely, I still need to find a way to select distinct email addresss from the result set.
DECLARE @TalentMgrEmail varchar (255),
        @FunctionalMgrEmail varchar (255),
        @AssetMgrEmail varchar (255),
        @EmployeeID varchar (50),
        @EmployeeName varchar (50),
        @LastworkDate datetime,
        @Job varchar (6),
        @WBS varchar (5),
        @AssetTag varchar (50),
        @AssetModelName varchar (128),
        @mailsubject varchar (200),
        @mailbody varchar (800)
        
        SET @mailsubject = 'Notification: blah blah ' + @EmployeeName + ':';
        SET @mailbody = 'Records indicate blah blah blah blah blah:
                -- is something else here
                -- something else here
                -- but has stuff assigned
                Employee ID:  ' + @EmployeeID +
                'Employee Name:  ' + @EmployeeName +
                'Last Work Date:  ' + @LastworkDate +
                'Last Work Job-WBS:  ' + @Job + '-' + @WBS +
                'Manager: please coordinate with blah blah blah blah blah blah blah blah blah blah blah:
                  -------------------------------------------------------------------------------------------'
                + @AssetTag + '     ' + @AssetModelName ;
        
        

/* 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].[CEmpDetails] 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 @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

/*Prepare and send email */
WHILE @@FETCH_STATUS = 0
  BEGIN
     EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'MyProfileName',
       @recpients = @TalentMgrEmail,
       @copy_recipients = @AssetMgrEmail, 
       @subject = @mailsubject,
       @body = @mailbody;
        
      
      FETCH NEXT FROM Emails INTO @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

      
End

CLOSE Emails
DEALLOCATE Emails

Open in new window

you are assigning the @mailsubject and @mailbody values outside the cursor.
please move to inside the cursor
just after the

WHILE @@FETCH_STATUS = 0
  BEGIN
Still get the same error on the final line... incorrect syntax near 'Emails'


DECLARE @TalentMgrEmail varchar (255),
        @FunctionalMgrEmail varchar (255),
        @AssetMgrEmail varchar (255),
        @EmployeeID varchar (50),
        @EmployeeName varchar (50),
        @LastworkDate datetime,
        @Job varchar (6),
        @WBS varchar (5),
        @AssetTag varchar (50),
        @AssetModelName varchar (128),
        @mailsubject varchar (200),
        @mailbody varchar (800)

/* 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].[CEmpDetails] 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 @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

/*Prepare and send email */
WHILE @@FETCH_STATUS = 0
  BEGIN

     EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'MyProfileName',
       @recpients = @TalentMgrEmail,
       @copy_recipients = @AssetMgrEmail, 
       @subject = @mailsubject,
       @body = @mailbody;
        SET @mailsubject = 'Notification: blah blah ' + @EmployeeName + ':';
        SET @mailbody = 'Records indicate blah blah blah blah blah:
                -- is something else here
                -- something else here
                -- but has stuff assigned
                Employee ID:  ' + @EmployeeID +
                'Employee Name:  ' + @EmployeeName +
                'Last Work Date:  ' + @LastworkDate +
                'Last Work Job-WBS:  ' + @Job + '-' + @WBS +
                'Manager: please coordinate with blah blah blah blah blah blah blah blah blah blah blah:
                  -------------------------------------------------------------------------------------------'
                + @AssetTag + '     ' + @AssetModelName ;
      
      FETCH NEXT FROM Emails INTO @TalentMgrEmail, @FunctionalMgrEmail, @AssetMgrEmail, @EmployeeID, @EmployeeName,
                            @LastworkDate,  @Job, @WBS, @AssetTag, @AssetModelName

      
End

CLOSE Emails
DEALLOCATE Emails

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jini Jose
Jini Jose
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I changed the code accordingly.  still get a syntax error on the final line near ';'
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',
       @recipients = @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;

Open in new window

i didnt get any errors here.

can you do one thing..
just remove the sending mail portion
and just print the outcome of @mailbody and @mailsubject
When I remove the sending mail portion, here's the message:

Msg 102, Level 15, State 1, Procedure up_CasualEmpEmail, Line 103
Incorrect syntax near ';'.
no errors here.
i think some of your data may have illegal characters.
print each values and check
I'm bringing in clean data that is imported from an SSIS package.  The problem may be with the query results.  
One table has the Functional Manager Email and the Talent Manager Email (CEmpDetails)

The other table has the Asset Manager Email (CEmpAsset)
        Many Assets in the Asset Table

The query currently produces multiple rows duplicating the email addresses.

I need to be able to get The Functional Manger Email, Talent Manager Email and Asset Manager Email(there may be several Asset Managers)

And provide a list of the assets and employee info in the email.
I revised the code as follows.  I get a syntax error near this line:

       @profile_name = 'Casual Asset',

When I comment out the code, everything parses well.
/* Email Variables */
DECLARE @mailsubject nvarchar(200)
DECLARE @themailbody nvarchar (MAX)


/* Cursor Variables */
DECLARE @EmployeeID varchar (50);
DECLARE @FunctionalMgrID varchar (50);
DECLARE @Job varchar (6);
DECLARE @WBS varchar (5);
DECLARE @LastworkDate datetime;
DECLARE @EmployeeName varchar (50);
DECLARE @FunctionalMgrName varchar (50);
DECLARE @FunctionalMgrEmail varchar (255);
DECLARE @TalentMgrName varchar (50);
DECLARE @TalentMgrEmail varchar (255);
DECLARE @Library varchar (50);
DECLARE @AssetMgrName varchar (50);
DECLARE @AssetMgrEmail varchar (255);
   

/* Cursor - looping through specified CasualEmployee Database Records */
DECLARE Emails CURSOR FAST_FORWARD READ_ONLY
FOR

/*Statement to fill cursor with data */
SELECT [CasualEmployeeDetails].[EmployeeID],
       [FunctionalMgrID],
       [Job],
       [WBS],
       [LastworkDate],
       [EmployeeName],
       [FunctionalMgrName],
       [FunctionalMgrEmail],
       [TalentMgrName],
       [TalentMgrEmail],
       [Library]      
  FROM [CDB].[dbo].[CEmpDetails] 
  WHERE [CEmpDetails].[EmailSent] = 0

   
   /* 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, @Library;
 
/*Prepare and send email */
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @mailsubject = 'AMS - Notification: Inactive CASUAL employee with Assets Assigned  ' +@EmployeeName+ ':';

    SET @themailbody = 'Records indicate that the employee shown below: <br/>
                     -- is defined as a CASUAL status employee<br/>
                     -- has not worked for the last 90 calendar days with PARSONS<br/>
                     -- but has assets 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/>Talent Manager: please coordinate with the listed Asset Manager and Functional Manager above to appropriately
                 <br/>re-assign this employee assets listed:' +
                 N'<H1>Casual Employee Assets</H1>' +
                 N'<table boarder="1">' +
                 N'<tr><th>Asset Tag</th><th>AssetModelName</th></tr>' +
                 CAST ( ( SELECT td = em.AssetTag,               '',
                                 td = em.AssetModelName,                     ''
                       FROM       CasualEmployeeAssets 
                       WHERE [CEmpAssets].[EmailSent] = 0  and CEmpAssets.EmployeeID = @EmployeeID
                       FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+ N'</table>' + N'Reference: '+ @Library;
   

    Select distinct top 1 [AssetMgrName]
                        ,[AssetMgrEmail]

          FROM [CDB].[dbo].[CEmpAssets]
          WHERE [CEmpAssets].[EmailSent] = 0  and EmployeeID = @EmployeeID                
----
---- 
----EXEC msdb.dbo.sp_send_dbmail
----       @profile_name = 'CasualAsset',
----       @recipients = @TalentMgrEmail,
----       @copy_recipients = @AssetMgrEmail, 
----       @subject = @mailsubject,
----       @body_format = 'HTML',
----       @body = @themailbody; 
        
      UPDATE [CEmpDetails] Set [EmailSent] = 1, EmailSentDate = GetDate() WHERE EmployeeID = @EmployeeID

--      UPDATE [CEmpAssets]  Set [EmailSent] = 1, EmailSentDate = GetDate() WHERE EmployeeID = @EmployeeID 

      FETCH NEXT FROM Emails INTO @EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName, @FunctionalMgrName,
                                 @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail, @Library;

      END 
      
      
      CLOSE Emails
      DEALLOCATE Emails

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The EXEC msdb.dbo.sp_send_dbmail works well with the static values.

Is there a method that I can check the syntax of the email and send notification before trying to execute?  The emails are stored in the database column with and SMTP prefix.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you!