Script to find Assembly registered in SQL Server by serverku

Sometime we need to know registered an assembly in our SQL Server instance and Today I would like to share a script to find the same. Here is a script for same.

USE <DBName>
GO

SELECT  
 a.name as AssemblyName, 
 af.name as AssemblyPath, 
 a.create_date as CreateDate,
 am.assembly_class as AssemblyClass,
 am.assembly_method as AssemblyMethod,
 a.is_user_defined as IsUserDefined
FROM sys.assemblies AS a
INNER JOIN sys.assembly_files AS af
 ON a.assembly_id = af.assembly_id
LEFT JOIN sys.assembly_modules am
 ON a.assembly_id = am.assembly_id
GO

This script return assembly name, path, created date and object associated with it. I would like to you share, if any, other information can be received for registered assembly.

Example of SQL CLR in SQL Server by serverku

After writing some previous posts of replication and mirroring, today i am writing about SQL CLR (common language runtime) server user defined function. As per msdn it is a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed. So let us implement it with one example.

1. Enable clr in SQL Server.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

2.  This is a sample C# code which have a logic to show up data.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void HelloWorld_CS(
        SqlDateTime ParaDateTime, SqlString ParaVarchar, SqlInt64 ParaInt)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            
            conn.Open();

            SqlCommand command = new SqlCommand("SELECT @ParaVarchar + ' Hello World', @ParaInt + 1, dateadd(dd,1,@ParaDateTime)" , conn);
            command.Parameters.AddWithValue("@ParaVarchar", ParaVarchar );
            command.Parameters.AddWithValue("@ParaInt", ParaInt);
            command.Parameters.AddWithValue("@ParaDateTime", ParaDateTime);

            SqlContext.Pipe.ExecuteAndSend(command);

            conn.Close();
        }
    }
}

3. Register a DLL.

4. Create a assembly with registered DLL.

CREATE ASSEMBLY HelloWorldCLR 
FROM 'C:\SQLCLR\HelloWorldTest.dll'
WITH PERMISSION_SET=SAFE;
GO 

5. Create a stored procedure which calls an assembly.

CREATE PROCEDURE Proc_CLR_HelloWorld
(
 @DateTest DATETIME,
 @VarcharTest NVARCHAR(100),
 @IntTest BIGINT
)
AS 
EXTERNAL NAME
    HelloWorldCLR.StoredProcedures.HelloWorld_CS
GO 

6. Finally, run that stored procedure with parameters.

EXEC Proc_CLR_HelloWorld 
 @DateTest ='01/01/2014',
 @VarcharTest = 'Test',
 @IntTest = 1
GO

You might work with SQL CLR and I would like to share your inputs and ideas the way you are using SQL CLR. Hope you like this post.