It happens quite often that you need to send the query results from a database process to someone via e-mail. This might be to report information to a customer or perhaps to alert yourself about the result of a SQL Agent job. You might even want to include the result as an attached Excel spreadsheet. In these situations, it would be useful to have a function that takes a query as a parameter and transforms the query results into an HTML table, possibly rotated, that can be rendered into a readable form. There used to be such a procedure, sp_MakeWebTask, which was deprecated in SQL Server 2008.

Of course, it is possible to build a fat or thin client application, which will use an XML transformation in order to get valid HTML. There are many samples on the Internet, which show you how to build such a solution. The XML transformation could be accomplished on the server side, too, using the XML support for T-SQL or an XLST stylesheet. However, I find that this solution is not easy to build and maintain.

Let us consider following example. This code snippet, produce valid HTML as showed in picture below. However, it is difficult to implement CSS styles and make any changes.

USE AdventureWorks2014;
GO
SELECT
(
     SELECT 'AdventureWorks2014.Person.Person'
     FOR XML PATH(''), TYPE
) AS 'caption',
(
     SELECT 'First name' AS th, 'Last name' AS th
     FOR XML RAW('tr'), ELEMENTS, TYPE
) AS 'thead',
(
     SELECT f.FirstName AS td, f.LastName AS td
     FROM
     (
          SELECT TOP 10 FirstName, LastName
          FROM Person.Person
     ) AS F(FirstName, LastName)
     FOR XML RAW('tr'), ELEMENTS, TYPE
) AS 'tbody'
FOR XML PATH(''), ROOT('table');

Let us try something completely different. Let us try to build a solution using .NET. This means using SQLCLR. Let us see how is expensive such solution in terms of performance, how is difficult to use and make extensions. This article explains some of the issues that are involved in building a SQLCLR function, using QueryToHtml as a practical and useful example.

This is how the article is organized.

1. Firstly, I will show you what we can do with such a function as QueryToHtml. Most people do not like to read a long article all the way through without knowing what they can gain from it. Therefore, I decided to start by describing what it does. In this first section, I will discuss HTML, CSS and ADO.NET.
2. In the second section, I will show you the detail of every parameter that the QueryToHtml function takes. To keep this explanation as simple as possible, I will introduce a tool called QueryToHtml tester, a WinForms application, which will help us to test the QueryToHtml function.
3. In the third section, I will show you many examples of the sort of problem you are likely to have to solve. The main goal of this section is to show mapping SQL Server types to System.Data.Sqltypes and .NET native types. Furthermore, I will discuss how values of these types are displayed in HTML.
4. In the fourth section, I will show some basic monitoring possibilities
5. In the last section, I will show how to deploy solution and explain how supported material is organized.

Functionality

Before doing anything else, we have to enable CLR functionality, which is disabled by default. In order to do that we execute this command.

sp_configure 'clr enabled', 1;
--Funny thing is that sp_configure 'clr', 1 works as
--well. Sp_configure uses like operator to determine
--configuration id. Since version, 2005 is always 1562
RECONFIGURE;
--Check is everything is ok 
SELECT
      *
FROM sys.configurations
WHERE configuration_id = 1562;

The end-result of my Visual Studio solution is an assembly, which you will need to install on your SQL server. I will skip a description of how to do this for the time being.

Let us assume, for the moment, that the assembly has been installed correctly. To test this out we can use SSMS Object Browser. Under the database in which you installed the assembly, expand the 'assembly' folder. You should see a new assembly, SqlClrReporter. In addition, in the 'Scalar-valued Functions' folder there are two new functions, both of which are shown in this screenshot

I will show you the basic functionality using AdventureWorks2012 (2014) databases. Let us assume that we would like to send a single result of a query to someone in an email.

DECLARE @body NVARCHAR(MAX);
SET @body =
(
    SELECT dbo.QueryToHtml('SELECT
  p.*,
  e.NationalIDNumber,
  e.LoginID,
  e.OrganizationLevel,
  e.JobTitle,
  e.BirthDate,
  e.MaritalStatus,
  e.Gender,
  e.HireDate,
  e.SalariedFlag,
  e.VacationHours,
  e.SickLeaveHours,
  e.CurrentFlag
FROM HumanResources.Employee e
LEFT OUTER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID = @empBId', '@empBid int = 62', 'Person-Employee', '#', 1, 0, '')
);

It is just a simple query that returns one record. The result of this query has assigned to the @body string variable. Then, later on in the procedure or batch, we have the following statement that adds a second HTML table to the @body. We could just send this in an email. However, we will, instead, add a second result.

SET @body = (SELECT
  DBO.ConCatHtml(@body, (SELECT
    dbo.QueryToHtml('SELECT
 *
FROM Production.Product
WHERE Weight = @weight',
    '@weight decimal(8,2) = 15.77',
    'AdventureWorks2012.Production.Product', 'Auto rotate example', 1, 0, ''))
  ));

This query returns another table with one record, using the same function as before. To add it to the first table, we append it to the same variable, @body. Then we could send result to someone's e-mail, like this.

EXEC msdb.dbo.sp_send_dbmail @body = @body
      ,@subject = 'Usage of QueryToHtml'
      ,@recipients = '<put e-mail address here>'
      ,@body_format = 'HTML';

The resulting email will look similar to the picture below.

You will notice that both the query results contain only one record and the results have been pivoted. Some styling has been applied, even though we did not specify a style when we issued the query.

At this point, you might wonder why we built this functionality when it is possible using the stored procedure, sp_send_dbmail, to attach the query results. Yes, is possible, but this stored procedure is limited in many ways. Microsoft decided many years ago to build such functionality into sp_send_dbmail. It was practical for admins to log in and check their server remotely. It is fine for the purpose of transmitting raw query results but isn't extendable to more sophisticated use.

So far, we have just seen the basic functionality. Before we do more to explain the functionality of the QueryToHtml function, I should write something about HTML and CSS. Every query should return an HTML table of zero or many records, which needs additional information about how it should be rendered, using CSS. Web designers sometimes consider it useful to provide the table within a DIV element. To accommodate this requirements, the rendering the table can be accomplished in the same manner as in this code fragment.

Alternatively, like this, see code below

In the head tag, I will inject a CSS style block. In the body tag, I optionally inject div tag(s) with an appropriate class id. Of course, I am not a web designer and probably there are many better ways to accomplish this rendering. Later I will show you how to apply your own styles. Initially, I had an idea for applying custom rendering by using custom .NET code. Unfortunately, this is just an idea for the future; because it is forbidden to compile source code on fly in SQLCRL for all types of CLR assemblies, (Microsoft SQLCLR designers divide assemblies according to permission level to SAFE, EXTERNAL AND UNSAFE).

Now, is time to say something about .NET. When I started developing this solution, I was thinking about a disconnected approach. This means I would like to use the ADO.NET SqlDataAdapter class and produce an in-memory DataTable collection called a DataSet. It seems to me more logical to process a DataTable and apply some styles and pivoting. However, when I started monitoring how much memory would survive full garbage collection (see the section 'Monitoring'), I was so disappointed. Then I started to use an explicate call to the GC.Collect () method. There was little or no change. After, a lot of investigation I found some useful tips. One of them is that you need to forget a DataSet.

Then, I rewrote the complete solution and used a so-called connected approach. This means using the SqlDataReader class. The situation became better. Finally, I decided to save the results as an instance of type List<object[]>. Therefore, I completely break anything with a DataTable and use an instance of the System.Collection.Generics as result of the data access. It is very interesting when debugging SQLCLR solutions, there is no option to view a DataTable using the magnifying glass. I added reference to Microsoft.VisualStudio.Debugger.DataSetVisualizer.dll, but there is no magnifying glass.

The most interesting thing here is the so-called context connection. According to the Microsoft documentation, the context connection allows you to execute T-SQL statements in the same context that was used when your code was invoked in the first place. In order to obtain the context connection, you must use the 'context connection' connection string keyword, as in the example below.

This is slightly different from classical ADO.NET, which would require us to provide authentication information. However, SQLCLR function code executes in the SQL Server process because someone connected to that server and executed a SQL statement to invoke it. Although you can make a conventional connection if you need to, you will probably want the function to execute in the context of the calling connection, along with its transaction, SET options and so on.

QueryToHtml in Detail

Now, it is time to say something about the parameters of the function. When I started writing the QueryToHtml function, I had many ideas for functionality, so it once had many parameters. As time went on, I reduced the number of parameters to a minimum. A list of parameters is shown on the table below.

I was thinking how to include some help information. This is a scalar function, which returns just a string and I do not want to print help information as text. I decided to accomplish this task by returning key-value pair result as this so, it could be more easily viewed within the SSMS result pane.

When we develop a SQLCLR function, we are in the context of a public class named, by default, UserDefinedFunction. In that class, which is created by Visual Studio automatically for us, we can define another function-method with the same name as one that already exists, to 'overload' the function. Therefore, my idea was to define a separate function also called QueryToHtml, which would return a key-value pair of 'parameters name', and 'parameters description' as a TSQL result. This helper function would, of course, not take any parameter. The assembly should, by .NET convention, be able to figure out which method to call based on the parameter types that you pass.

However, the plan did not go well. Although the solution compiled without errors, and the assembly then installed without errors, there was no resulting function at all. Although the two functions had the same name, they have different parameters and different results. First returns a string and the second returns a SQL result. It is a strange behavior, because I did not see any error, or even warnings.

Finally, had to rename both the .NET name and the SQLCLR name of the help erfunction. Therefore, help is available by issuing following commands.

SELECT
 *
FROM DBO.SqlClrReporterHelp('QueryToHtml');
SELECT
 *
FROM DBO.SqlClrReporterHelp('ConCatHtml');

My main concern, when developing the QueryToHtml function, was in ensuring that we could pass parameters in a way that did not pollute the query plan cache. I wanted the query to be prepared and to be cached once, ready for further reuse. I would like to pass parameters as simply as we enter parameters in SSMS or any query editor. So, parameters should be written as plain text.

When I decided on this, I realized that it would be difficult to parse parameters exactly without any conventions. The picture showed below contains list of parameters written correctly.

This convention include following:

• Every parameter should have name which begin with @
• Every parameter should have a type
• Every parameter should have value

This seems reasonable to me. Of course, some problematic formatting should be handled as well. It means extra white spaces before or after some declaration etc.

Of course, always is possible not to pass query parameters at all. It means to hard coded parameters values in query.

Developing an HTML report interactively

Now, it is time to introduce a tool, which is designed to allow anyone using the function interactively to see the effect of adding, removing, and altering parameters. This tool also helps us in testing and monitoring the QueryToHtml function. It is a WinForms application called QueryToHtmlTester.

When you use any SQL query or function, you can use SSMS interactively, checking your results in the result pane. This is not the case when the result is in HTML and CSS. The result is better viewed in a browser. The QueryToHtmlTester tool makes this design process a great deal faster by allowing you to connect to the server, run the function and display it in a browser pane within the application.

When you start this tool for the first time, you will see a screen similar to this:

On the first tab, you define the connection where the SqlClrReporter assembly has installed! You define the server name, types of authentication, user name, and password and database name.

Once you have defined the parameters, you can click on 'Test connection'. If everything is ok, a new item is added in the List Box on the right side.

In the List Box, connections are saved for later reuse. When you click on the 'Query' tab, you will see a screen similar to this.

This is where you define your query. In the parameters textbox you specify the parameters, as I explained earlier. Of course, you can leave this textbox empty. In that case, you should hard-code the parameters in your query. It means, instead of writing 'LIKE @lastName', you should write 'LIKE 'GOL%'.

It would be common to copy query text from SSMS and paste into the query text box. You can write your parameters in the same way as you write in SSMS. Because parameters are parsed during execution, some apostrophes can be omitted. So, following parameters definition (without apostrophes)

On the third tab named 'Html table' you can define your caption, footer and rotation mode. Although the first two are obvious, the third deserves a little more explanation.

Where the number of columns of the table is greater than the number of rows, the result often looks better when rotated. I use the term 'rotate', rather than 'pivot', because there is no aggregation at all. I simple rotate the table. The function can detect this and rotate accordingly. The behavior I refer to as 'auto rotate'. You can opt to 'not rotate' and 'rotate always'.

When we rotate a table, there are two possibilities too. In first one, we rotate by passing column ordinal position. It should be between zero and the maximal column number. Everything seems to be fine when you pass the column ordinal position as a column, which represents unique values such as a primary key. However, a problem occurs when you pass ordinal position on, let us say, a column that contains information about last name. In that case, it is quite common that there would be a duplicated last name. In that case, I added a suffix at the end of each column value, starting with the second occurrence. See the screenshot below.

There is a special option. When you pass as ordinal position -1. In that case, columns headers are Key, Value, etc. Therefore, results should be like in the screenshot below.

On the fourth tab, you can define styles. Six predefined styles have the ST_ prefix. You can define your own style by selecting 'custom style' from the 'drop down' list. When you select a custom style, you should enter a valid style in the custom style textbox Here is a picture of a custom style

<style type='text/css'> 
table.datagrid
{ 
    font-family: verdana,arial,sans-serif;
    font-size:11px;
    color:#333333; 
    border-width: 0.2px;
    border-color: #666666;
    border-collapse: collapse;
} 
table.datagrid th 
{ 
    border-width: 0.2px;
    padding: 6px;
    border-style: dotted;
    border-color: #666666;
    background-color: #dedede;
} 
table.datagrid td 
{ 
    border-width: 0.2px;
    padding: 6px;
    border-style: dotted;
    border-color: #666666;
    background-color: #ffffff;
} 
table.datagrid thead 
{ 
    font-weight: bold;
    font-family: verdana,arial,sans-serif;
    font-size:13px;
    text-align: center;
    background-color: #dedede;
} 
table.datagrid tfoot 
{ 
    font-family: verdana,arial,sans-serif;
    font-size:11px;
    text-align: center;
} 
</style>

At any point, you can click on 'Get HTML' button or 'Get T-SQL' button. One of the main goal is to use this utility to build T-SQL, which can be used later in some SQL Server Agent jobs or whatever scripts. Clicking on button 'Get T-SQL' you can get in 'Notepad' full formatted T-SQL.

When you click on the button, 'Get HTML', the tab, 'Result', is selected. On this tab there is a Web Browser control, which will display the results.

On the 'Html table' tab, there is checkbox control with the label 'Append to existing content'. This lets you tell the Web Browser control to display two or more tables with the same or different style. On the web browser control there is a context menu with three options.

The first option is 'Copy results to clipboard', which does the same thing the name suggests. From the clipboard, you can copy the results and paste them into an HTML page. The HTML page can be created from Visual Studio, through the File->New->File->Html page, option. The second option is to save the results as an HTML file directly. Finally, you can save the results as an Excel file. Of course, these options are add-ons. These options are helpful for those who like to use this utility to get the final results. As I wrote above, the main purpose of this utility is to build T-SQL, which will be used in some T-SQL automatizations.

Saving content in an xlsx Excel file requires saving the content as an HTML file and then using Microsoft.Office.Interop.Excel.dll to convert the HTML into an xlsx file.

The QueryToHtml tester is a standard WinForm application with its own config file, called app.config. In this file, there are a few parameters, as showed in the picture below.

One of them is folder where we save files. By default, it is C:\TMP. File is named by using caption defined on HTML table tab and current date-time value.

The Microsoft.Office.Interop.Excel.dll is not referenced directly in this tool, instead it is used as an embedded resources. Combining this with the source code written in the config file, under the key 'codeToCompile' and by using an on-the-fly compiler, I produce an Excel file. So, this means if this logic is not suitable for your environment, you can change source code in config file.

The solution depends on the excel driver. In addition to the mentioned DLL, it is necessary to ensure the existence of Microsoft.Vbe.Interop.dll, stdole.dll and office.dll. All of them you can find in source code, added as embedded resources.

The problem commonly encountered with excel is the loss of leading zero. For example, when a PIN has displayed, which is mandatory for 11 digits. One way to overcome it, is to apply the format after generating excel. For example, applying a "00000000000" format. Another way is to add a few apostrophes to the T-SQL query itself. Eg “SELECT '' '' + PIN PIN ... FROM”

The result looks similar to the picture below.

One of the QueryToHtml tester parameters will save your input. This means everything entered in textboxes is saved in the configuration xml file. The next time you start QueryToHtml tester, every textbox is already filled. By default, the file where your inputs are saved is 'config.xml'. You can change this file name using the app.config. Password information is protected by using basic cryptography features. Therefore, no one cannot discover password information by browsing your folder.

More examples

Executing stored procedures is supported, but there is a little difference. The stored procedure should be passed to QueryToHtml with the EXEC prefix. There is no suitable stored procedure in AdventureWorks database. So, let us make a simple one.

CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT
                PersonType
                ,FirstName
                ,LastName
FROM Person.Person
WHERE LastName = @LastName
ORDER BY BusinessEntityID

In our QueryToHtmlTester, we should enter 'EXEC dbo.GetPeopleByLastName' in query textbox. In parameter textbox, we should enter, for example, '@LastName nvarchar(50)=Alexander'. The result should be as showed on screenshot below.

Of course, we get the same result if we issu the following command in SSMS.

SELECT
     dbo.QueryToHtml('EXEC dbo.GetPeopleByLastName','@LastName nvarchar(50)=Alexander', 'Stored procedure sample', '#', 0, 0, '');

If we want to execute the popular stored procedure, sp_WhoIsActive, and send results to someone, we issue a command like this:

SELECT
     dbo.QueryToHtml('EXEC sp_WhoIsActive','','General limitation of SQLCLR functions', '',1,0,'')

In addition, result is

This is a limitation of UDF function generally. See the complete list of UDF limitation at the following link: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine

Mapping Types

It is time to see how different SQL server types are mapped to System.Data.Sqltypes and .NET native types. SqlDBType is a kind of bridge between SQL Server and .NET. In .NET there is an enumerator, called SqlDbType, which is utilized mostly when we would like to issue T-SQL command from .NET code and this command has at least one parameter.

So, let's execute following query.

SELECT
 dbo.QueryToHtml('
SELECT
 [ProductID]
 ,[Name]
 ,[ProductNumber]
 ,[MakeFlag]
 ,[FinishedGoodsFlag]
 ,[Color]
 ,[SafetyStockLevel]
 ,[ReorderPoint]
 ,[StandardCost]
 ,[ListPrice]
 ,[Size]
 ,[SizeUnitMeasureCode]
 ,[WeightUnitMeasureCode]
 ,[Weight]
 ,[DaysToManufacture]
 ,[ProductLine]
 ,[Class]
 ,[Style]
 ,[ProductSubcategoryID]
 ,[ProductModelID]
 ,[SellStartDate]
 ,[SellEndDate]
 ,[DiscontinuedDate]
 ,[rowguid]
 ,[ModifiedDate]
FROM [Production].[Product]
WHERE SizeUnitMeasureCode = @size
  AND WEIGHT = @weight
  AND ModifiedDate = @modDate
  AND rowguid = @rg;',
 ' @modDate datetime = 2014-02-08 10:01:36.827,@weight decimal(8,2) = 2.36, @size nchar(3 ) = CM, @rg uniqueidentifier=3B5F29B6-A441-4FF7-A0FA-FAD10E2CEB4C',
 '1 records [AdventureWorks2014].[Production].[Product]', 'uniqueidentifier + datetime + decimal + nchar', 1, 0, '');

This query produces the following result:

The query returns only one record from the database, AdventureWorks2014. The query takes four (4) parameters. The purpose of this query is educational. I tried to issue the query with different SQL server types. A complete list of mapping can be viewed on this website: Mapping CLR Parameter Data. Let us see how they mapped. 

Uniqueidentifier is the most specific and it is mapped to . SqlDbType.UniqueIdentifier and this one to NET Guid. The value is determined using following .NET code, where valueSplitter is value entered in parameter textbox with or without apostrophe.

else if (s1.SqlDbType == SqlDbType.UniqueIdentifier)
{
  string valueString = Regex.Replace(valueSplitter, ''', '', RegexOptions.IgnoreCase).Trim();
  s1.Value = new Guid(valueString);
}

In the DataAccess.cs class, located in the CustomRoutines folder, you can find three helper methods. DetermineSqlDbType is to determine the SqlDBType called and the other two are used to determine parameter size and parameter value.

NChar ( or NVarchar ) is mapped to corresponding SqlDbType.NChar ( or SqlDbType.Nvarchar ). These are then mapped to .NET string type. Value of these types is determined using following .NET snipped, where valueSplitter is value entered in parameter textbox with or without apostrophe.

string valueString = valueSplitter.Trim();
if (valueString.StartsWith('''))
   valueString = valueString.Substring(1);
if (valueString.EndsWith('''))
   valueString = valueString.Substring(0, valueString.Length - 1);

The DateTime type is mapped to SqlDbType.DateType. Similarly the SmallDateType is mapped to SqlDbType.SmallDateTime. Both of these SqlDbTypes are mapped then to .NET DateTime. The following .NET code snippet is used to determine the .NET DateTime value, where valueSplitter is the value entered in parameter textbox.

else if (s1.SqlDbType == SqlDbType.DateTime || s1.SqlDbType == SqlDbType.SmallDateTime)
{
   string valueString = Regex.Replace(valueSplitter, ''', '', RegexOptions.IgnoreCase).Trim();
   s1.Value = DateTime.ParseExact(valueString, 'yyyy-MM-dd hh:mm:ss.fff', CultureInfo.InvariantCulture);
}

And finally the decimal SQL Server type is mapped to SqlDbType.Decimal. Then this SqlDbType is mapped to .NET Decimal. The following .NET code snippet show how the decimal value is determined, where valueSplitter is the value entered in parameter textbox.

else if (s1.SqlDbType == SqlDbType.Decimal)
{
  decimal j;
  bool succ = Decimal.TryParse(valueSplitter.Replace('.', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator), out j);
  if (succ)
      s1.Value = j;
}

All values are rendered in HTML as strings. There is no special formatting, although is possible to use the T-SQL Format function to apply a special format. The Format function was introduced with SQL Server 2012. More details about T-SQL Format function could be found on this link: T-SQL FORMAT FUNCTION

Executing more then one query

One of the possibilities that QueryToHtml provides is to execute more than one query. As I showed in the first section, there is an option to build the final HTML with a different style. In order to get two or more HTML tables with one QueryToHtml execution, you should separate each query with a semicolon, much as you do in SSMS.

For example, enter the following commands in the query textbox of our QueryToHtmlTester. Note, I am using these queries as part of my diagnostics solution. There is a scheduled job, which executes queries like these and send me an e-mail.

--First query
SELECT
  @@SERVERNAME AS [Server Name]
 ,@@VERSION AS [SQL Server and OS Version Info]
--Second query, third query and so on..
;WITH [Waits] AS
 (SELECT
  [wait_type],
  [wait_time_ms] / 1000.0 AS [WaitS],
  ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0
   AS [ResourceS],
  [signal_wait_time_ms] / 1000.0 AS [SignalS],
  [waiting_tasks_count] AS [WaitCount],
  100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER()
   AS [Percentage],
  ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC)
   AS [RowNum]
 FROM sys.dm_os_wait_stats
 WHERE [wait_type] NOT IN (
  N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
  N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
  N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
  N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
  N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
  N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
  N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
  N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
  N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
  N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
  N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
  N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
  N'DIRTY_PAGE_POLL',  N'BROKER_TRANSMITTER',
                N'BROKER_RECEIVE_WAITFOR')
 )
SELECT
 [W1].[wait_type] AS [WaitType],
 CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
 CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
 CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
 [W1].[WaitCount] AS [WaitCount],
 CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
 CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4))
  AS [AvgWait_S],
 CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4))
  AS [AvgRes_S],
 CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4))
  AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
 ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
 [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount],
 [W1].[Percentage]
HAVING
 SUM ([W2].[Percentage]) - [W1].[Percentage] < 95
OPTION (RECOMPILE); 

You should get results like those in the screenshot below. Of course, the data depends on your server information, the style you choose, and so on.

Cross-databases queries has supported too. It means, if you have appropriate rights, install the assembly on a single database, and HTML can be obtained based on the T-SQL query from other databases. All this is true for the linked servers.

Monitoring

Binding occurs on the Monitor tab, only if the credentials specified on the first tab have view server state permission. In that case, you see a screen similar to the one below. If you do not have the appropriate permission, you see only blanks.

The Monitor tab has combined information from three system views. The information from sys.assemblies, surrounded with red, has a row for each assembly created in the current database (the database you choose on first tab). The information from sys.dm_clr_loaded_assemblies is surrounded with green and has a row for each managed user assembly loaded into the server address space. The information from sys.dm_clr_appdomains, Surrounded with blue, returns a row for each application domain in the server. An application domain is a construct in the Microsoft .NET Framework common language runtime (CLR) that is the unit of isolation for an application.

According to the Microsoft documentation, the sys.dm_clr_loaded_assemblies view has a many-to-one relationship with sys.dm_clr_appdomains.appdomain_address. In addition, sys.dm_clr_loaded_assemblies view has a one-to-many relationship with sys.assemblies. So, it is possible that you have different user principals. We only have one user principal. In that case, we can assumed that the relation between views is one to one.

When you create an assembly in your database, you have a corresponding record in sys.assemblies system view. This contains information like the principal id, assembly id, permission set, etc.

When a user asks for some functionality located in your assembly the first time, an application domain is created. As I wrote above, the appdomain is a lightweight process used to enforce isolation between running .NET code within the same SQLOS process. SQLCLR uses appdomains to isolate execution of .NET code on a per database and per assembly owner basis.

This process of loading an appdomain takes time (this is a performance penalty). Therefore, the first execution of your SQLCRL function is always much slower than the next execution. After our appdomain is loaded, unlike .NET appdomains, it remains loaded for further reuse. This behavior increase performance, so that the managed database objects contained in the assembly can be called in the future without having to reload the appdomain. The appdomain remains loaded until SQL Server comes under memory pressure.

Typically in the SQL Server log you see messages like this:

When the appdomain is unloaded, on the Monitor tab you see information such as that shown in the picture below. So, a lots of 'N/A' information aperies. Only the information from sys.assemblies is available.

One of you may ask, it is possible to prevent unloading of our appdomain. The simple answer is no. Unlike a standard .NET application, the SQLCRL object requests memory from SQL Server, not from the operating system. Theoretically, I could have a SQL Agent job which pulls information from sys.clr_loaded_assemblies and when it detects that the appdomain is unloaded, executes a simple task that forces the loading of our appdomain.

On this Monitor tab I put two buttons. One is for loading the application domain and the other is for unloading. When I said optionally, I meant the display is controlled by app.config. The default behavior is not to show these buttons. The reason is quite simple. Their functionality is limited. However, if you change these application settings, the Monitor tab is slightly changed with two more buttons.

The handler for unloading the application domain uses a simple trick to alter permission of the assembly. It will set the permission from unsafe to safe and back to unsafe. Alternatively, in our case, vice versa. This is enough to shut down the application domain.

Of course, there are many drawbacks. First, you need to have appropriate rights to do that. One shortcut is to put your database in trustworthy mode, which is not recommended. The correct way to do this is to create an asymmetric key based on the assembly signature. Then to create a login based on that key. Finally grant appropriate rights to that login, create a user based on the newly created login and alter authorization on the assembly (see the script below). The appropriate rights means grant external or unsafe access assembly to that login. In all other cases, tester will produce a message box that it is unable to proceed with the request.

At the end of this article you can find a link to the source code location. Writing about signing assemblies is out of the scope of this article. I have to write that this assembly, 'SqlReporter', is signed as showed in the picture below.

Using this T-SQL code you can create appropriate login and grant needed rights. I will repeat, the reason why we do this is just to force unloading the AppDomin.

USE MASTER
GO
--Copy snk from VS solution to path visible for your sql server instance
--Replace this path value, you password as you want
CREATE ASYMMETRIC KEY keySqlReporter
FROM FILE = 'C:\TMP\SqlReporter.snk'
ENCRYPTION BY PASSWORD = '@Str0ngP@$$w0rd'
GO
--Use database where your installed the assembly
USE AdventureWorks2012
GO
--Create login
CREATE LOGIN [CLRSQLReporter] FROM ASYMMETRIC KEY keySqlReporter
GO
USE MASTER
GO
--Grant rights to newly created login
GRANT UNSAFE ASSEMBLY TO [CLRSQLReporter];
GO 
USE AdventureWorks2012
GO
--Create user based on newly created login
CREATE USER [CLRSQLUser] FROM LOGIN [CLRSQLReporter]
GO
ALTER AUTHORIZATION ON ASSEMBLY::[SQLCLRReporter] TO [CLRSQLUser]
GO

In my opinion, it is good practice for each assembly to have a special user defined that will be the assembly owner (this means a user different from the default 'dbo' and created by using the T-sql script above). Altering the permission set ( from 'SAFE' to 'UNSAFE' or vice versa ) or altering authorisation ( from 'dbo' to 'CLRSQLUser' ) will force an application domain to shutdown.

Of course, there is an exception with 'Microsoft.SqlServer.Types' which does not allowed such a dirty trick (this refers to any assembly that is not user-defined. On the Monitor tab you can see checkbox labeled 'User defined' unchecked). The button for loading the application domain just calls a well-known method in the given assembly. In case of SqlClrReporter, the help function has called. This is enough to load the application domain.

However, by now you probably have figured out that the SQLCLR is not a first class citizen in SQL Server.

Another common problem that could occur is a memory leak. By memory leak, I mean some value in the red textbox labeled with 'Survived memory'. This value indicates the number of kilobytes that survived the last full, blocking collection and that are known to be referenced by the current application domain. This is equivalent to System.AppDomain.MonitoringSurvivedMemorySize.

In our example, if you execute query that return rows greater then 1K, there is great chance that 'Survived memory' will be greater than zero. The means that during memory pressure, SQL Server will decide to unload the application domain. This indicator was introduced with SQL Server 2012. With this version, many things changed. Prior to the 2012 version, SQLCLR was governed by so-called multi-page allocation, unlike the single page allocation, which is responsible for Buffer Pool allocations and governed by 'max server memory'. Prior to SQL Server 2012, you would have to reduce 'max server memory' in order to have more memory for SQLCLR.

Let us go back to the main topic. The main issue in building SQLCLR functions with data access is how to release memory. I figured out that without forcing garbage collection, I cannot stabilize my application domain. At the end, of every function I put following snippet of code, see picture below.

I did not find any suggestion on the internet for how to accomplish garbage collection for SQLCLR. It seems that garbage collection could not be done successfully in the same thread at the end of function call. My impression is that types that are use in everyday ADO.NET programming are not good with SQLCLR. So, I developed a special function that will just call the garbage collector. I named this function 'CleanMemory'.

When calling a QueryToHtml function inside 'QueryToHtml tester', I add a call to CleanMemory. After doing that, the application domain became more stable. The survived memory is much smaller then before, even when executing queries that return large number of records.

One interesting thing is that changing GCSettings, by setting LargeObjectHeapCompactionMode, is not allowed without changing the assembly permission level. We would have to set the assembly permission to unsafe.

Probably, some of you noticed that under the 'Assembly' folder in SSMS, there is a predefined assembly called Microsoft.SqlServer.Types.

This is responsible for spatial types like geometry, geography, hierarchy etc. This can be viewed using 'QueryToHtml tester' as well.

When you issue a command like this, see the picture below, you may notice a slight delay. That is because the application domain should be loaded on the first usage of these types.

The main conclusion in monitoring should be to take care of memory and to watch periodically the three system views: sys.dm_clr_appdomains, sys.dm_clr_loaded_assemblies, and sys.assmblies. The main rule should be to use paging to retrieve a large number of records.

Supporting Materials and Deployment

SQLCLR installation can be found the following link SQLCLR installation. The installation is divided into three folders, as showed on the picture below.

The SQL Server version 2008 R2 should use the folder, 'FRM_3_5'. SQL Server version 2012 should use the folder, 'FRM_4_0'. SQL Server version 2014 should use the folder, 'FRM_4_6_2'.

Every folder contains a T-SQL script, which should be modified before executing. First, you should put this script in SQLCMD mode, by choosing Query-SQLCMD mode in SSMS. Then replace string '<your database name>', with name of your database.

Replace the string 'default data path' with your default data path. This information could be found on your database properties (right click on your database in SSMS and then choose properties), and then the File page. Do the same thing with the 'default log path'. This is all. Press F5.

The 'QueryToHtml tester' can be found at the following link: QueryToHtml tester installation. Just download RAR file, extract with an appropirate location and start the application. The source code is available on this link: SQLCLRReporter source code and tester is available on this link: QueryToHtml tester source code. Both solutions are Visual Studio 2015 solutions.

Summary

For sure, the SQLCLR is not a first class citizen in SQL SERVER. However, it is worth exploring and using in solving practical problems. Although 'QueryToHtml' and 'QueryToHtml tester' are in first release, they could help in many ways, at least educational.

Both provide an easy to use interface and could be extended in many ways in the future. And finally, I would like to thank Andrew Clarke, whose helpful suggestions gave me valuable input for writing this article.