Using  CLR integration we can create

• CLR Stored Procedure
• CLR User Defined Function (Scalar and Table valued)
• CLR Trigger
• CLR User defined data types
• CLR Aggregates

Let see how to create a CLR object using Visual studio

Before even developing a CLR object  , we have to know what version of .Net framework is sql server is using for the CLR environment

I using the Sql sever 2008 , we can see here CLR runs in .Net framework v2

With the .net framework 4.0 we now have the ability to load two or more distinct versions of the CLR within a single process. In previous releases of the .net framework, a process could only load a single instance of the CLR. Given this restriction the CLR team recommended that hosts, such as SQL Server, use the LockClrVersion function to determine the version of the CLR to load prior to initialization. So although we are developing our objects in .Net framework v3.5 we will be still able add the assembly to the sql server

 

 

 

 

 

Creating SQL CLR project

or

CREATE ASSEMBLY [SQL CLR]
AUTHORIZATION [dbo]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\SQL CLR\bin\Debug\SQL CLR.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO;

 

 

 

 

 

 

 

 

 

 

 

 

 

TITLE: Microsoft SQL Server Management Studio
——————————

Create failed for SqlAssembly ‘SQL CLR’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE ASSEMBLY for assembly ‘SQL CLR’ failed because assembly ‘SQL CLR’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. (Microsoft SQL Server, Error: 10327)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=10327&LinkId=20476