SQL Common Language Runtime (Example)

What is SQLCLR? ( Choose database project, write code , set up database info, deploy it to SQL server)

* compiled .NET managed code into assembly and deploy .dll into SQl Server 2005 as stored procedure

* technology for hosting of the Microsoft .NET common language runtime engine within SQL Server.

1. Enable CLR integration into SQL Server 2005

SP_Configure "clr enabled" , 1

go

Reconfigure

go

2. Import your assembly into SQL Server

Programmability> Assemblies

Right click > New Assembly > Config Properties and Create Assembly

3. Import .NET assembly into Sql Server 2005

VS 2005 > New project > Database > SQL Server Project

Select Datababase Reference > Bingo (proj created)

4. Deploy Assembly (VS05)

Build > Deploy ProjectaName

5. Additional Security Precautions for Unsigned Assemblies

* Turn on Trustworthy property

Alter database dbName set trustworthy on

* Grant user login "external Access Assembly"

Management Studio > object explorer> Database > Right Click > Permission

Grant "External Access Assembly" or "Unsafe Assembly"