Saturday, 25 December 2010

How to use CLR in the MSSQL server

To use the CLR in MSSQL Server you need to enable CLR Integration first. It can be enabled in the sql server surface area configuration.

Then create a .NET assembly that you want to work with:

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class SqlTemplate
  public static SqlGuid CreateGuid()
    return new SqlGuid();

using the query designer, upload the assembly into the sql server:

FROM 'C:\SQLCLR\SqlClrLibrary.dll' 

Alternatively you can select Programmability->assemblies->New assembly in the sql database you are working with. You also need to expose the functions from the assembly into the sql server:

EXTERNAL NAME SqlClrLibrary.SqlTemplate.CreateGuid

SQLCLRProject is a free tool that allows you to deploy .NET assemblies to SQL Server (version 2005 and later).

VS Professional Edition has an SQL Server Project template built into it.

No comments:

Post a Comment