SQL SERVER CLR stored procedure

Short notes regarding how to begin with the CLR store procedure and help full reference links in the bottom.

View/Set the Database setting :

 select * from sys.dm_clr_properties  
 select * from sys.dm_clr_appdomains  
 select * from sys.dm_clr_loaded_assemblies  
 select * from sys.dm_clr_tasks  

Enabling the Clr in SQL Server.

 sp_configure 'show advanced options', 1;  
 GO  
 RECONFIGURE;  
 GO  
 sp_configure 'clr enabled', 1;  
 GO  
 RECONFIGURE;  
 GO

Creating StoreProcedure.

 USE database_name  
 GO  
 EXEC sp_changedbowner 'sa'  
 ALTER DATABASE database_name SET TRUSTWORTHY ON  
 CREATE ASSEMBLY Database2 FROM 'C:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Database2.dll';  
 create PROCEDURE SqlStoredProcedure11 AS EXTERNAL NAME Database2.StoredProcedures.SqlStoredProcedure1;  
 EXEC dbo.SqlStoredProcedure11  

Code Snippet:

using System;  
 using System.Data;  
 using System.Data.SqlClient;  
 using System.Data.SqlTypes;  
 using Microsoft.SqlServer.Server;  
 using DataTracker;  
 using System.Text;  
 public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SqlStoredProcedure1 ()  
   {  
     // Put your code here  
     SqlPipe sp;  
     sp = SqlContext.Pipe;  
     String strCurrentTime = "Current System DateTime is: "  
     + System.DateTime.Now.ToString();  
     sp.Send(strCurrentTime);  
   }  
 }  

Reference Link

http://blog.sqlauthority.com/2008/10/19/sql-server-introduction-to-clr-simple-example-of-clr-stored-procedure/

http://msdn.microsoft.com/en-us/data/tools.aspx

http://msdn.microsoft.com/en-us/data/hh297027

http://msdn.microsoft.com/en-us/jj650015

http://www.codeproject.com/Tips/147250/SQL-Server-failed-to-load-assembly-with-PERMISSI

How to UNLOCK a table in MS SQL ?

Follow this steps to UNLOCK the sql table which has been LOCKED  by some other process/script.

 1.  sp_lock      --Run this  
 2.  sp_who2     --Run this  
 3.  Look for Status column and the DBName column.  
 4.  Get the session Id from SPID.  
 5.  Kill <SPID>    --Run this