Executing .NET Assembly in SQL CLR

Many developers become sluggish when it comes to learning TSQL stored procedures and deal with functions and triggers. As we, all know the famous saying “If necessity is the mother of invention, then laziness is sometimes its father” then welcome the tricky way of doing what you want in SQL without writing complex TSQL syntax. This method is by using .NET assemblies and calling .NET DLLs inside the SQL Server using SQLCLR.

What is SQLCLR?

SQL Server 2005 introduced the integration of the Common Language Runtime (CLR) component of the .NET Framework for Microsoft Windows. This means that you can write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. The Microsoft.SqlServer.Server namespace contains a set of new application programming interfaces (APIs) so that managed code can interact with the Microsoft SQL Server environment.

Therefore, SQLCLR is a framework that bridges the environment of the SQL Server database engine with the rich programming environment of .NET. This allows for:

  • Extending capabilities of queries beyond T-SQL built-in functions.
  • Performing certain operations faster than being done in T-SQL.
  • Better interaction with external resources than can be done via xp_cmdshell.

Enabling CLR Integration:

The Common Language Runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration using Transact-SQL, use the “clr enabled” option of the “sp_configure” stored procedure as shown:

sp_configure ‘clr enabled’, 1

GO

RECONFIGURE

GO

Now, let us play with some C#.NET code and do practical stuff based on what introduces above. For this purpose, we are going to create a new assembly in other words a class library in Visual Studio and name it TestSqlClrAssembly. The needed assemblies in the References section of the library are:

using Microsoft.SqlServer.Server;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

By default, if you are using Visual Studio 2015 or 2017 these assemblies are already found in the references. These assemblies are not all used but usually these are the ones that SQLCLR integration normally need them.

Now, create a class called TestClass where this class will include a simple method that will return the length of a string provided a parameter for the function. Instead of having this as a stored procedure in SQL and use TSQL syntax to create it and use it to get the input string length, we will simply create a method to do this functionality and call it using SQLCLR later from the class library that includes the TestClass class.

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StringCharCount (string inputString)

{

SqlContext.Pipe.Send(inputString.Length.ToString());

}

As shown above, the declared method should be static since the SQL Server does not instantiate the class TestClass or create any new object from it to call the method. Moreover, the return type must be always void since the result is channeled via the SqlContext.Pipe class. We use the attribute [Microsoft.SqlServer.Server.SqlProcedure] before the method declaration in order to inform the SQLCLR to recognize this method as a stored procedure. As shown below, the method Send in the SqlContext.Pipe class allows you to provide it with other parameters type like SqlDataRecord object and SqlDataReader object in addition to the input of type string, which is used in our example. We chose to use the input of type string in order not complicate things in our example but you can try it using the same technique and methodology of work where the provided object type differs according to what you need in your project.

StringLengthMethod

When we finish writing the method declaration, we build the class library to generate the assembly (dll file) that will be used in SQL for calling this stored procedure. For testing purposes, we will create a testing database and run the below TSQL statement in it (you can use this dll in any target database you’re working on following the same steps and commands)

CREATE ASSEMBLY TestSqlClr

FROM  ‘(class library compilation path)\ TestSqlClrAssembly.dll’

WITH PERMISSION_SET = SAFE

GO

The upper statement loads the TestSqlClrAssembly.dll binary file into the testing database and this means that SQL does not reference this dll at its own path but completely loads it into the MDB database file. This implies that anytime you want to do any changes to the class library or modify the code, then you have to rebuild this library again to generate the new dll file and use the same statement above to load the new dll into the database but replacing the CREATE with ALTER. The permission set defined as SAFE since we do not need to access any information or data outside out testing database in addition that we are not using any unmanaged code inside it. Moreover the StringLengh() method can’t be recognized by SQL CLR as a stored procedure, so we have to manually point to this method by creating a procedure that calls it and returns its value. The way of doing this is by following the below statement. Note the same parameter naming in the created SP.

CREATE PROCEDURE spStringLength (@inputString nvarchar(max))

AS

EXTERNAL NAME TestSqlClr.[TestSqlClrAssembly.TestClass].StringLength

GO

Now we are ready for fun by executing the upper SP that will call our created method in the class library, but before doing so do not forget to enable the CLR by executing the commands mentioned at the beginning of this post, which will enable the SQLCLR at the serve side.

EXEC dbo.spStringLength ‘This is my first sql clr assembly run’

The returned value of the upper statement is 37!

We can create much more complex scenarios using the same strategy mentioned in this post, where the created methods to be linked to SPs in the SQL Server. Do not forget that each time you modify the code in the class library; you have to load again the new dll assembly file to the database by altering the assembly command statement. I hope that I can discuss in further posts scenarios that are more complex by using triggers and functions in SQL CLR.

 

 

SQL Server: Cleaning all Database Tables and Reset Identity Columns by forcing Truncate or Delete

We all know that during the development process of any software or web application connected to an SQL Server database, all the tables contain what we call ‘trash’ data which is non useful data entered by developers for testing their code. This data should be removed before the delivery of the application to the client, where the database should be clean and all the tables indexes regarding the Identity column must be reset to zero index.

I’ve faced such an issue several times. The data removal using the usual Delete or Truncate statements isn’t a hard task when the number of tables in the database and the relations between them are not that large. But when it comes to a database that contains a large bunch of tables and complicated relationships among them all, then this represents a serious problem.

Well, the solution is by iterating over all tables in the database, disable the constraints and then delete the data found in each table. Don’t panic, we’re not going to write any script to accomplish this mission and of course we’ll not do this task manually by going to each table and disable the constraints and after that deleting the records. Thanks for the built in stored procedure sp_MSforeachtable which allows you to loop over all the tables in the desired database and execute any command you supply within the parameters.

The solution will be as follows:

  1. Disable Constraints and Triggers by executing the following command:
    exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
  2. Now delete all the records found in all tables in your database by forcing cleanup through Delete or Truncate
    exec sp_MSforeachtable 'DELETE ?'
  3. We have to enable the Constraints and Triggers back again:
    exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
  4. The final step is to reset the Identity column in all tables back to zero base index:
    exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Now all the tables in the database are clean and their identity columns are reset to zero.

SQL Server Configuration Manager – Cannot connect to WMI provider

I was trying to access my SQL Server 2008 R2 Network Configuration to enable Tcp/Ip connections to my server through the SQL Server Configuration Manager from the Configuration Tools. But suddenly an error message appeared indicating that I can’t access my configuration manager through the WMI provider. The error message that popped up is shown down below:

 

 

As we can see above, the error pop up dialog indicates that I don’t have the permission to access the server or it’s unreachable. But SQL Server is already installed properly on my machine and I can access the instance through the Microsoft SQL Server Management Studio. So, why this error occurred ?

This error occurs when the .mof files (Microsoft Object Format) are damaged or not properly installed and registered during the MS SQL Server 2008 R2 installation process.

In order to solve this issue, we have to do the following steps:

  1. Run the command prompt as administrator.
  2. Change the directory to the following path: “C:\Program Files (x86)\Microsoft SQL Server\100\Shared”.
  3. Use the mofcomp.exe to register the .mof file again by running the following command: mofcomp.exe “C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof”

using mofcomp.exe

Remark: mofcomp.exe is used to compile Managed Object Format (MOF) code into binary form stored in the WMI repository. Use when creating or modifying the MOF file for a WMI provider. mofcomp.exe is one of the WMI Command Line Tools in Windows.

Now the MOF file in SQL Server 2008 R2 is parsed successfully and the SQL Configuration Manager will execute without any error.