title: Publishing an SQL Server CLR Assembly
body: <p>I'm developing a CLR stored procedure for my client.  There are two assemblies that are involved.  The CLR assembly that contains the "stored procedure" and an "engine" that does some work.  (I'm developing both.)  I can go into more detail on this if necessary, but I don't believe it's directly related to the problem.</p>
  
  <p>When I test out my early prototype, I have Visual Studio generate the script that registers the assembly and the CLR procedure on the SQL server.  It uses the following syntax to register the assembly...</p>
  
  <pre><code>CREATE ASSEMBLY [Blah.Blah.Engine]
      AUTHORIZATION [dbo]
      FROM 0x4D5A9000030000000....
  
  CREATE ASSEMBLY [Blah.Blah]
      AUTHORIZATION [dbo]
      FROM 0x4D5A90000300.....
  </code></pre>
  
  <p>Then it creates the procedures.  This works fine.  But, rather than send the hexadecimal bits as part of the <code>CREATE ASSEMBLY</code> statement, what we want to do is copy the compiled .DLL files to the disk and use the path to the assemblies to register them, like this...</p>
  
  <pre><code>CREATE ASSEMBLY [Blah.Blah.Engine]
      AUTHORIZATION [dbo]
      FROM 'C:\MyFolder\Blah.Blah.Engine.dll'
  </code></pre>
  
  <p>However, when I do that, I get the following error:</p>
  
  <blockquote>
    <p>Msg 10301, Level 16, State 1, Line 26 Assembly 'Blah.Blah.Engine'
    references assembly 'system.data.datasetextensions, version=4.0.0.0,
    culture=neutral, publickeytoken=b77a5c561934e089.', which is not
    present in the current database. SQL Server attempted to locate and
    automatically load the referenced assembly from the same location
    where referring assembly came from, but that operation has failed
    (reason: 2(The system cannot find the file specified.)). Please load
    the referenced assembly into the current database and retry your
    request.</p>
  </blockquote>
  
  <p>Yes, my Engine is referencing system.data.datasetextensions.  But if I use the first syntax, I don't get this error.  I'd strongly prefer to not have to us the <code>WITH PERMISSION_SET = UNSAFE</code> phrase and have to manually copy all the referenced assemblies into "MyFolder" on the server.  That doesn't seem like the right way to go at all.  But if sending the bits over runs correctly, why is it not working when I do it the second way?</p>