Avatar of Annette Wilson, MSIS
Annette Wilson, MSIS
Flag 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

Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Annette Wilson, MSIS

8/22/2022 - Mon
Jini Jose

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, 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

Jini Jose

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Annette Wilson, MSIS

ASKER
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
Jini Jose

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Annette Wilson, MSIS

ASKER
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 Jose

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Annette Wilson, MSIS

ASKER
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 Jose

no errors here.
i think some of your data may have illegal characters.
print each values and check
Annette Wilson, MSIS

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Annette Wilson, MSIS

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Annette Wilson, MSIS

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Annette Wilson, MSIS

ASKER
Thank you!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.