Where can I find the dll file which was auto deployed to SQL 2005?

Question

I can see the SP it created, but I could not find the DLL, any idea?
thanks!

Answer 1

The bits are in sys.assembly_files (content column) in the database in which
the assembly was deployed. The assembly bits are cataloged into the database
itself, they are not put into or kept in external files.

Cheers,
Bob Beauchemin
SQLskills

Answer 2

thanks
is there a way I can store that into a dll file?

Answer 3

Hi David,

There's not a built in way (in SQL Server) to do this, but I'm posting a quick SQLCLR stored procedure I wrote that will. Considerations:
1. This assembly must be cataloged into the same database where the assembly you want to extract lives.
2. The assembly must be cataloged as "permission_set = external_access"
3. You need to write to a directory that the SQL Server service account has access to (the code does not do impersonation).

Hope this helps,
Bob Beauchemin
SQLskills

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SaveAsm(string asmname, string path)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = @"select content from sys.assembly_files f
                            join sys.assemblies a
                             on f.assembly_id = a.assembly_id
                            where file_id = 1
                            and a.name = @name";
            cmd.Parameters.Add("@name", SqlDbType.VarChar);
            cmd.Parameters[0].Value = asmname;

            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            SqlBytes b = rdr.GetSqlBytes(0);
            FileStream fs = new FileStream(path, FileMode.CreateNew);
            fs.Write(b.Value, 0, (int)b.Length);
            fs.Close();
        }
    }
};