• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

SQL 2005 Database Mail

I have set up a mail account and archive for the email.

Since the logs store the entire html formatted email in xml format,  Auditors can't easily view the data.  How can I extract the data using a stored procedure and display the email and its contents in a table from the archive?

Some of the rows in my table does not have an email address.  How can I prevent null errors?

0
Annette Wilson, MSIS
Asked:
Annette Wilson, MSIS
2 Solutions
 
Anthony PerkinsCommented:
You can use the following system views:
sysmail_sentitems: All messages that were successfully sent.
sysmail_faileditems:   All messages that failed.
sysmail_allitems:  All messages sent.

It will be up to you to write an app using something like .NET to convert the Xml code.
0
 
Alpesh PatelAssistant ConsultantCommented:
Here you can use the XSLT to see XML file in HTML. Just create a XSLT file based on the XML structure.

In addition create one routine to add reference of XSLT file to XML file and you can see the XML file to be converted in HTML when you open it.
0
 
GDG_DBACommented:
You can shred the xml into columns.  I do this for our email archive system which stores searches and other actions in a log, but the details are in XML. The app has a report which included the XML (worthless for most users), so I shred the XML into useable tabular data.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Thank you very much for your input.  I work through the various suggestion and get back to you.
I do have another problem, I thought it was because of null values in my table but I eliminated the nulls and I'm still getting an error with this stored procedure.  

It's stopping after sending the first record.  I can't figure out why.  I would appreciate any help you can provide.

Msg 16916, Level 16, State 1, Line 91
A cursor with the name 'CasualEmails' does not exist.
ALTER Procedure [dbo].[MyStoredProcedure]
AS
DECLARE @subjectmatter nvarchar(MAX)
DECLARE @themailbody nvarchar (MAX)
DECLARE @emailrecipients nvarchar (MAX)

/* Cursor Variables */
DECLARE @EmployeeID varchar (50)
DECLARE @FMId varchar (50);
DECLARE @Job varchar (6);
DECLARE @WBS varchar (5);
DECLARE @LastworkDate datetime;
DECLARE @EmployeeName varchar (50);
DECLARE @FMName varchar (50);
DECLARE @FMEmail varchar (255);
DECLARE @TMName varchar (50);
DECLARE @TMEmail varchar (255);
DECLARE @Library varchar (50);
DECLARE @AMName varchar (50);
DECLARE @AMEmail varchar (255);

/* Cursor - looping through specified CasualEmployee Database Records */
DECLARE CasualEmails CURSOR FAST_FORWARD FOR
SELECT     CDetails.EmployeeID, CDetails.FMId, CDetails.Job, CDetails.WBS, 
                      CDetails.LastworkDate, CDetails.EmployeeName, CDetails.FMName, 
                      CDetails.FMEmail, CDetails.TMName, CDetails.TMEmail, CDetails.Library, 
                      CTheGOODS.AMName, CTheGOODS.AMEmail
FROM         CDetails INNER JOIN
                      CTheGOODS ON CDetails.EmployeeID = CTheGOODS.EmployeeID
WHERE     (NOT (CTheGOODS.AMName IS NULL))

OPEN CasualEmails

FETCH NEXT FROM CasualEmails INTO 
@EmployeeID,
@FMId,
@Job,
@WBS,
@LastworkDate,
@EmployeeName,
@FMName,
@FMEmail,
@TMName,
@TMEmail,
@Library,
@AMName,
@AMEmail;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @subjectmatter = 'AMS - Notification: Inactive CASUAL employee with Assets Assigned:'+ @EmployeeName + ' '+'('+ @EmployeeID +')';

SET @themailbody = 'TO:  '+ @TMName + '     (Talent Manager)<br/>
                    CC:  '+ @AMName + '     (Asset Manager) <br/>' +
                    N'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp   ' +@FMName +  ' (Functional Manager) <br/>
                    -------------------------------------------------------------<br/>    
                    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/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Employee ID:     ' +@EmployeeID+
                '<br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Employee Name:   ' +@EmployeeName+ 
                '<br/><br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp     Last Work Date:  ' +convert(varchar(30), @LastworkDate, 101)+
                '<br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Last Work Job-WBS:  '+@Job+ '-' +@WBS+
                '<br/><br/><b>Talent Manager:</b>&nbsp  please coordinate with the listed Asset Manager and Functional Manager above to appropriately 
                 <br/>re-assign this employee theGOODS listed:' + '<br/>' +
                 N'<style type="text/css">, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:.5px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
                 N'<table boarder="1">' +
                 N'<tr><th>Asset Tag</th><th>Asset Model Name</th></tr>' +
                 CAST ( ( SELECT td = CTheGOODS.Tag,               '',
                                 td = CTheGOODS.ModelName,                     ''
                       FROM       CTheGOODS 
                       WHERE  CTheGOODS.EmployeeID = @EmployeeID and CTheGOODS.AMName = @AMName 
                       FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+ N'</table>' + N'<br/><br/>Reference Library: '+ @Library;


DECLARE @MailRecipients AS VARCHAR(500)
	SET @MailRecipients = REPLACE(@TMEmail+';'+ @AMEmail+';'+ @FMEmail, 'SMTP:', '')     

 
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'CasualEmpAssets',
		@recipients = @MailRecipients,
		@subject = @subjectmatter,
		@body_format = 'HTML',
		@body = @themailbody;

FETCH NEXT FROM CasualEmails INTO
@EmployeeID,
@FMId,
@Job,
@WBS,
@LastworkDate,
@EmployeeName,
@FMName,
@FMEmail,
@TMName,
@TMEmail,
@Library,
@AMName,
@AMEmail

CLOSE CasualEmails
DEALLOCATE CasualEmails

END

Open in new window

0
 
GDG_DBACommented:
You are missing an END for your while loop.

It should go before the CLOSE statement.

-G
0
 
GDG_DBACommented:
Like this...
ALTER Procedure [dbo].[MyStoredProcedure]
AS
BEGIN

	DECLARE @subjectmatter nvarchar(MAX)
	DECLARE @themailbody nvarchar (MAX)
	DECLARE @emailrecipients nvarchar (MAX)

	/* Cursor Variables */
	DECLARE @EmployeeID varchar (50)
	DECLARE @FMId varchar (50);
	DECLARE @Job varchar (6);
	DECLARE @WBS varchar (5);
	DECLARE @LastworkDate datetime;
	DECLARE @EmployeeName varchar (50);
	DECLARE @FMName varchar (50);
	DECLARE @FMEmail varchar (255);
	DECLARE @TMName varchar (50);
	DECLARE @TMEmail varchar (255);
	DECLARE @Library varchar (50);
	DECLARE @AMName varchar (50);
	DECLARE @AMEmail varchar (255);

	/* Cursor - looping through specified CasualEmployee Database Records */
	DECLARE CasualEmails CURSOR FAST_FORWARD FOR
	SELECT     CDetails.EmployeeID, CDetails.FMId, CDetails.Job, CDetails.WBS, 
						  CDetails.LastworkDate, CDetails.EmployeeName, CDetails.FMName, 
						  CDetails.FMEmail, CDetails.TMName, CDetails.TMEmail, CDetails.Library, 
						  CTheGOODS.AMName, CTheGOODS.AMEmail
	FROM         CDetails INNER JOIN
						  CTheGOODS ON CDetails.EmployeeID = CTheGOODS.EmployeeID
	WHERE     (NOT (CTheGOODS.AMName IS NULL))

	OPEN CasualEmails

	FETCH NEXT FROM CasualEmails INTO 
	@EmployeeID,
	@FMId,
	@Job,
	@WBS,
	@LastworkDate,
	@EmployeeName,
	@FMName,
	@FMEmail,
	@TMName,
	@TMEmail,
	@Library,
	@AMName,
	@AMEmail;

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @subjectmatter = 'AMS - Notification: Inactive CASUAL employee with Assets Assigned:'+ @EmployeeName + ' '+'('+ @EmployeeID +')';

		SET @themailbody = 'TO:  '+ @TMName + '     (Talent Manager)<br/>
							CC:  '+ @AMName + '     (Asset Manager) <br/>' +
							N'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp   ' +@FMName +  ' (Functional Manager) <br/>
							-------------------------------------------------------------<br/>    
							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/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Employee ID:     ' +@EmployeeID+
						'<br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Employee Name:   ' +@EmployeeName+ 
						'<br/><br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp     Last Work Date:  ' +convert(varchar(30), @LastworkDate, 101)+
						'<br/>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp          Last Work Job-WBS:  '+@Job+ '-' +@WBS+
						'<br/><br/><b>Talent Manager:</b>&nbsp  please coordinate with the listed Asset Manager and Functional Manager above to appropriately 
						 <br/>re-assign this employee theGOODS listed:' + '<br/>' +
						 N'<style type="text/css">, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:.5px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
						 N'<table boarder="1">' +
						 N'<tr><th>Asset Tag</th><th>Asset Model Name</th></tr>' +
						 CAST ( ( SELECT td = CTheGOODS.Tag,               '',
										 td = CTheGOODS.ModelName,                     ''
							   FROM       CTheGOODS 
							   WHERE  CTheGOODS.EmployeeID = @EmployeeID and CTheGOODS.AMName = @AMName 
							   FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+ N'</table>' + N'<br/><br/>Reference Library: '+ @Library;


		DECLARE @MailRecipients AS VARCHAR(500)
			SET @MailRecipients = REPLACE(@TMEmail+';'+ @AMEmail+';'+ @FMEmail, 'SMTP:', '')     

		 
			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'CasualEmpAssets',
				@recipients = @MailRecipients,
				@subject = @subjectmatter,
				@body_format = 'HTML',
				@body = @themailbody;

		FETCH NEXT FROM CasualEmails INTO
		@EmployeeID,
		@FMId,
		@Job,
		@WBS,
		@LastworkDate,
		@EmployeeName,
		@FMName,
		@FMEmail,
		@TMName,
		@TMEmail,
		@Library,
		@AMName,
		@AMEmail
	END -- WHILE

	CLOSE CasualEmails
	DEALLOCATE CasualEmails

END -- PROC

Open in new window

0
 
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Thank you Very Much  GDG_DBA!



0
 
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Thanks again!
0
 
GDG_DBACommented:
I should mention that in the code I posted above I added a BEGIN for the proc also.  

This puts the proc in a BEGIN/END block (nicer to read) and the while loop in  a BEGIN/END block (mandatory).

(I did not look at it for functionality).
-G
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now