Sending SQL Server Query Results as Pretty HTML Email

 So my goal here is to be able to send out a pretty HTML email based on data from a SQL Query using only SQL Server 2005 (or greater).

 First we need to make sure the environment is all set up by doing the following

1. Make sure that CLR integration is enabled on the instance of SQL Server
2. Make sure that Database Mail is enabled and configured.
3. Make sure Openrowset is enabled.
4. Create our SampleData table and populate it with some data.
5. Create a table to store our XSL data.

Step 1.

 Make sure that CLR Integration is enabled on your SQL Server Instance by opening the SQL Server Surface Area Configuration tool and clicking on the enable CLR Integration checkbox like shown below. 

 

Step 2.

While you are there also make sure that Database Mail is enabled by checking the checkbox like shown here.

If you haven't configured DBMail yet here is a helpful step by step guide.

Step 3.
And also make sure the OPENROWSET is enabled.

Step 4.
Next we need some sample data to work with.
This will create the SampleData table and populate it with some sample data.

CREATE TABLE dbo.SampleData
    (
    ID int NOT NULL IDENTITY (1, 1),
    ItemNumber nvarchar(50) NULL,
    ItemDescription nvarchar(50) NULL,
    Cost decimal(18, 2) NULL,
    QtySold int NULL
    )

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)                 

values ('2WE4R5T', 'Thinga Ma Bob', '2.10', '1000')

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)

values ('96KL69YLK', 'DooHickey', '995.10', '22')

Insert into SampleData (ItemNumber, ItemDescription, Cost, QtySold)

values ('9FJ438JD', 'Whatchama Calut', '2256.25', '56')

Step 5.
 We could just store the XSL Files in the filesystem but we want to do as much in SQL Server as possible so we run the following to create a table to store our XSL.

CREATE TABLE [dbo].[XMLTransforms](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [XSL] [xml] NULL
)

Now that our environment is all set up we are ready to have some fun.

6. Write the query to get the data we want to send.
7. Write the XSL file to transform the XML returned in the above query to pretty HTML.
8. Import the XSL into the Table created in step 4 above. 
9. Write the CLR Function in C# to transform the XML to HTML.
10. Add the CLR Function to SQL Server.
11. Put it all together and watch the magic happen

Step 6.
  The only requirement is that the query returns it's data as the XML Data Type. So this will return an XML doc for us to work with.

    Declare @xmlDoc XML;

    Select @xmlDoc = (Select * from SampleData for xml auto, Root('Sample'), elements)

Here I am declaring a variable @xmlDoc as being of the XML Data Type.
I am just pulling all the data from the table we created above and sticking it into the @xmlDoc variable.

• The "for xml auto" portion of the above statement tells SQL to return the query results as nested XML elements.
• The "Root('Sample')" portion nests the results of the query in a root element named 'Sample'
• The "elements" portion tells SQL to return each column as a subelement rather than a an attribute of an element (read more about this here)

The resulting XML from the statement above should look like this.

Step 7.
Now we need to specify how we want to transform the XML that we have into html. We do this using XSL. We are going to keep things simple and just return the data in an html table and base our XSL file on the example shown here 
You can use Visual Studio or Notepad++ or your favorite text editor to edit the file. 
Here is what our XSL file looks like or you can download it here

Step 8.
 To import the XSL file from your Filesystem into the SQL Table created in step 5 run the following

INSERT INTO XMLTransforms(XSL)
    SELECT * FROM OPENROWSET(
     BULK 'c:\XSL\Sample.xsl', SINGLE_BLOB) AS x

Step 9.
Now we need a way to apply the XSL to the XML and spit out the pretty HTML we are looking for. We do this using a CLR function.
The CLR function looks like this and you can download the code here

To compile the above code you can use the csc.exe command line compiler that comes with the .Net Framework.In the case of my PC I ran the following from a command prompt

    cd C:\Windows\Microsoft.NET\Framework\v3.5
    csc.exe /target:library /out:C:\Code\XML2HTML.dll C:\Code\XML2HTML.cs

This creates the XML2HTML.dll in the C:\Code Directory

Step 10.
The next step is to add the map the Function in the DLL to a function in SQL Server. We do this by doing the following.

First we need to add the Assembly to SQL Server like this:

CREATE ASSEMBLY XML2HTML 
FROM 'C:\Code\XML2HTML.dll'
WITH PERMISSION_SET = SAFE;

Then we need to map a SQL Function to the CLR Function in the DLL.

CREATE FUNCTION [dbo].[ApplyXslTransform](@inputXML [xml], @inputTransform [xml])   
RETURNS [xml] WITH EXECUTE AS CALLER                                                                                      
AS                                                                                                                                                                   
EXTERNAL NAME [XML2HTML].[UserDefinedFunctions].[Transform]                                      

Step 11.
Now we can finally send out our HTML Email by executing the following

Declare @xmlDoc XML;
Select @xmlDoc = (Select * from SampleData for xml auto, Root('Sample'), elements)
Declare @xsl XML;
Set @xsl = (Select xsl from XMLTransforms where id = 1)
Declare @messBody nvarchar(max);
Set @messBody = cast((Select dbo.ApplyXslTransform(@xmlDoc, @xsl)) as nvarchar(max))
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Profile Name Here',
        @recipients = 'someone@sample.com',
        @body = @messBody,
        @body_format = 'HTML',
        @subject = 'Sample Info.' ;

Ok so maybe it isn't the prettiest HTML, but you should be able to see the potential. As you create new and different XSL files and import them into the XMLTransforms table you can change the whole layout of your email just by changing the  id in the
"Set @xsl = (Select xsl from XMLTransforms where id = 1)" statement.
You could return a customized "Thanks For Your Order" email in the format of a letter or an automated "Friendly Reminder" that a customer's account is overdue from your AR system.
Once you have the framework detailed above you are only limited by your HTML skills.
Good Luck! 

Importing XML from a file into an XML column

The following will import the XML from a file into a column in a table with the XML Data Type in SQL Server.

INSERT INTO T(XmlCol)
    SELECT * FROM OPENROWSET(
     BULK 'c:\SampleFolder\SampleData3.txt', SINGLE_BLOB) AS x

You must include the as X to name the column or the import will fail. 

THis example and more can be taken from here http://msdn.microsoft.com/en-us/library/ms191184.aspx

Recovering T-SQl Code when SSMS crashes

By default SQL Server 2005 and 2008 Management Studio will save whatever you are working on every 5 minutes or so into your  \Documents and Settings\username\My Documents\SQL Server Management Studio\Backup Files\ dir.
This has saved me time on multiple occassions. 
There is no way to turn this feature off in SSMS but you can control it by changing the following registry settings where 1 is enabled and 0 is disabled.


[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

How to retrieve all enabled users from Active Directory.

The following code will return the name and email address of all users in a specified Active Directory domain that have not been disabled.
The only tricky part is setting up the LDAP query to only return users that have not been disabled. This is accomplished by setting this bitwise filter
"(!(userAccountControl:1.2.840.113556.1.4.803:=2))"

In this line we are saying look at the "UserAccountControl" attribute in Active Directory and if it is not equal to "2" then return true.
The 1.2.840.113556.1.4.803 tells it to use the LDAP_MATCHING_RULE_BIT_AND operator to make the comparison.
There is a little more on using the bitwise operators in ActiveDirectory here .

public static DataTable GetListOfActiveUsers(string domainName)
        {
            DirectoryEntry entry = new DirectoryEntry("LDAP://DC=" + domainName + ",DC=com");
            DirectorySearcher search = new DirectorySearcher(entry);
            string query = "(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(&(mail=*)))";
            search.Filter = query;
            search.PropertiesToLoad.Add("name");
            search.PropertiesToLoad.Add("mail");

            SearchResultCollection mySearchResultColl = search.FindAll();

            DataTable results = new DataTable();
            results.Columns.Add("name");
            results.Columns.Add("mail");

            foreach (SearchResult sr in mySearchResultColl)
            {
                DataRow dr = results.NewRow();
                DirectoryEntry de = sr.GetDirectoryEntry();
                dr["name"] = de.Properties["Name"].Value;
                dr["mail"] = de.Properties["mail"].Value;
                results.Rows.Add(dr);
                de.Close();
            }
            return results;
        }
    }

How to retrive a list of all computers with Last Logon time from Active Directory

I was driving myself crazy trying to retrieve the LastLogonTimestamp from Active Directory with the System.DirectoryServices namespace.
You can read here about the joy of converting the IADSLargeInteger data type (which is what the LastLogonTimestamp is) to something usable  here.
It was pointed out to me that new in .Net 3.5 is the System.DirectoryServices.AccountManagement namespace which make this task much simpler.
Here is the code that finally did the trick for me.
 

  public static DataTable GetListOfComputers(string domainName)
        {
            DataTable results = new DataTable();
            results.Columns.Add("name");
            results.Columns.Add("lastLogonTimestamp");

            PrincipalContext pc = new PrincipalContext(ContextType.Domain, domainName);
            PrincipalSearcher ps = new PrincipalSearcher(new ComputerPrincipal(pc));
            PrincipalSearchResult psr = ps.FindAll();
            foreach (ComputerPrincipal cp in psr)
            {
                DataRow dr = results.NewRow();
                dr["name"] = cp.Name;
                dr["lastLogonTimestamp"] = cp.LastLogon;
                results.Rows.Add(dr);
            }
            return results;
        }

Truncating all tables in a database with sp_msforeachtable

I recently wrote an application that retrieves a list of all computers for a specific domain from Active Directory and proceeds to query each computer for specific info. like Make, Model, Serial Number, OS, Service Packs, Service Tags, RAM, Processor info, Installed Applications etc.
Basically it just automates the process of keeping an up to date inventory of all the computers in my domain and stores the results in a SQL Server 2005 DB.
During the development and testing phase I wound up populating the database with loads of worthless data. after each execution of the partially completed app I wanted to be able to simply Truncate each user table in the user database.
Using the sp_msforeachtable undocumented stored procedure is as simple as this:

use MyDB
exec sp_msforeachtable 'truncate table ?'
If I only want to truncate the tables that belong to a specific schema I could run the following.

Use MyDB
Exec sp_msforeachtable @command1 = '
if (Select Object_Schema_name(object_id(''?''))) = ''dbo''
Begin
TRUNCATE TABLE ?
print ''truncated '' + ''?''
End
'

How to schedule backups in SQL Server 2005 Express

I recently had a customer that needed a database to serve as the backend for a small Document Management system. SQL Server Standard or Enterprise would have been overkill for such a small application.

I decided to use the free SQL Server 2005 Express Edition. The problem is that it is free for a reason. See some of the limitations listed here.

http://www.dotnetspider.com/tutorials/SqlServer-Tutorial-158.aspx

In order to make this work I had to come up with a way to schedule backups. The following stored procedure takes as parameter the type of backup (db OR Log) and will back up all databases on the target server to the specified target folder.

Create PROCEDURE usp_Backup
@Type as varchar(10),
@Path as varchar(255)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @dbs as TABLE
(
id int identity,
name varchar(50),
recovery_model varchar(25)
)
Insert into @dbs Select name, recovery_model_desc from sys.databases where name <> 'tempdb'

Declare @dest as varchar(max)
Declare @sql as varchar(max)
Declare @dbname as varchar(50)
Declare @recovery_model as varchar(50)
Declare @retention_days as int
Declare @cnt as int
Declare @id as int
Select @cnt = count(*) from @dbs
set @id = 1
while @id < @cnt
Begin
Select @dbname = name from @dbs where id = @id
Select @recovery_model = recovery_model from @dbs where id = @id
set @dest = @path + '\' + @dbname + '_' + @type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'

if @type = 'DB'
Backup Database @dbname to disk = @dest
else
If @recovery_model <> 'simple'
Backup Log @dbname to disk = @dest

set @id = @id + 1
End
END
GO

To schedule it I simply created a text file like so.

Use Master
Go
Exec usp_backup @Type='Log', @Path='C:\SQL_Backups'
Go

And scheduled the following to execute using Windows Task Scheduler.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S Server -E -i C:\SQL_Scripts\AllDBBackups.sql

Works like a charm so far.