Professional SQL Server™ 2005 Programming

14.2. Compiling an Assembly

Use of .NET assemblies requires that you enable the Common Language Runtime (CLR) in SQL Server — which is disabled by default. You can enable the CLR by executing the following in the Management Studio:

sp_configure 'clr enabled', 1
GO

RECONFIGURE

There really isn't that much to this beyond compiling a normal DLL. The real key points to compiling a DLL that is going to be utilized as a SQL Server .NET assembly are:

• You cannot reference any other assemblies that include functions related to windowing (dialogs, etc.).

• How the assembly is marked (safe, external access, unsafe) will make a large difference to whether or not the assembly is allowed to execute any functions.

From there, most things are not all that different from any other DLL you might create to make a set of classes available. You can either compile the project using Visual Studio (if you have it), or you can use the compiler that is included in the .NET SDK.

Let's go ahead and work through a relatively simple example for an assembly we'll use as a stored procedure example a little later in the chapter.

Create a new SQL Server project in Visual Studio using C# (you can translate this to VB if you wish) called ExampleProc. You'll find the SQL Server project type under the "Database" project group. When it comes up, cancel out of any database instance dialogs you get, and choose Class Library project in Visual Studio.

NOTE

The actual project type you start with does not really matter other ...