troubleshooting Question

SQL 2005 Database Mail

Avatar of Annette Wilson, MSIS
Annette Wilson, MSISFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
15 Comments3 Solutions272 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Jini Jose
Senior .Net Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros