Solved

SQL 2005 Database Mail

Posted on 2011-09-30
9
334 Views
Last Modified: 2012-06-21
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
Comment
Question by:Annette Wilson, MSIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895100
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36895467
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
 
LVL 10

Accepted Solution

by:
GDG_DBA earned 500 total points
ID: 36898383
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Annette Wilson, MSIS
ID: 36904691
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 36904722
You are missing an END for your while loop.

It should go before the CLOSE statement.

-G
0
 
LVL 10

Assisted Solution

by:GDG_DBA
GDG_DBA earned 500 total points
ID: 36904745
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
 

Author Comment

by:Annette Wilson, MSIS
ID: 36904827
Thank you Very Much  GDG_DBA!



0
 

Author Closing Comment

by:Annette Wilson, MSIS
ID: 36904841
Thanks again!
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 36904924
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question