All managed code assemblies installed using CREATE ASSEMBLY are actually stored in binary form within the SQL database itself: when you execute a CREATE ASSEMBLY statement using a file path (or load an assembly using the Sql Server Management Studio dialogues) it actually creates an internal copy of that file, so it doesn't matter where you put the original file because it won't be referenced once the assembly is installed.

Provided they are all 100% managed code (.Net) you can load multiple assemblies into SQL Server and have them reference each other: for example if you have a general-purpose .NET library which isn't SQL-specific, and want to add some SQL CLR wrappers in another assembly to expose the library's function within SQL, you need to load the original library first, and can then add a reference to it from within Visual Studio when writing your wrapper (once you've set up the database connection to the database containing the original library). Thus an assembly does not need to be written specifically for SQL CLR in order to use its functionality within SQL.

The one exception to all this is if you need to call unmanaged code using P/Invoke (e.g. a third-party library) from a .Net assembly marked for UNSAFE access (best avoided if possible!). In this case you cannot install the library into SQL Server itself as it will not allow you to install unmanaged code. Instead in that case you need to put the assembly somewhere SQL Server can see it - e.g. c:\Program Files\Microsoft SQL Server\100\shared\ (for SQL Server 2008). The server already has read access to that folder, which is all you need. That will work - but note that once you've invoked a function within such a library you will need to re-start SQL server if you want to replace the file (it opens a file lock on it and never seems to relinquish it).

Charles Southey

www.totallysql.com