SQL 2005 Database Mail

Annette Wilson, MSIS
Annette Wilson, MSIS used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jini JoseSenior .Net Developer

Commented:
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,
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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

Jini JoseSenior .Net Developer

Commented:
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
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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

Senior .Net Developer
Commented:
change like below

        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 ;


EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'MyProfileName',
       @recpients = @TalentMgrEmail,
       @copy_recipients = @AssetMgrEmail,
       @subject = @mailsubject,
       @body = @mailbody;
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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

Jini JoseSenior .Net Developer

Commented:
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
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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 ';'.
Jini JoseSenior .Net Developer

Commented:
no errors here.
i think some of your data may have illegal characters.
print each values and check
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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.
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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

Jini JoseSenior .Net Developer
Commented:
can you try the EXEC msdb.dbo.sp_send_dbmail as a seperate one ?

just try with the static values
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
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.
Annette Wilson, MSISSr. Programmer Analyst
Commented:
Found the problem.
At the beginning of the
Create Procedure dbo.Name
As

I have BEGIN right after As.  This was the final error.  When I removed this, the stored procedure executed perfectly.
Annette Wilson, MSISSr. Programmer Analyst

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial