Solved

SQL 2005 Database Mail

Posted on 2011-09-30
9
302 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
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Annette Wilson, MSIS
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Expert Comment

by:GDG_DBA
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you Very Much  GDG_DBA!



0
 

Author Closing Comment

by:Annette Wilson, MSIS
Comment Utility
Thanks again!
0
 
LVL 10

Expert Comment

by:GDG_DBA
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now