One of the things that seems to be missing from Visual Studio's support of SQL Server 2005 for CLR based functions, stored procedures and such it the ability to build a deployment script. Visual Studio makes it easy to deploy CLR functionality to SQL Server while you are in a developement enviornment, but no way to make a script to do the deployment. Of course the purpose of Visual Studio deployment feature is to make it easy to change functionality as you proceed developing it, by removing most everything that needs to be removed before deploying the new versions.
I've put togther a sample program that uses SMO [SQL Server Management Objects] that allows you to select some assemblies for deployment, then build a script. It looks like:
The main purpose of this sample is just to show some of the features of SMO, its not a production utility. There are a lot of things that could be added to it and in my copious spare time I just might add them. The sample is implemented using C# and you can download the project from here.
The main problem with deploying assemblies is that the must be deployed in the correct order. The sys.assembly_references system view shows you the dependencies. If assembly A depends on assembly B, then assembly B must be loaded before assembly A. Fortunately a recursive common table expression can sort this all out and SMO can execute ad hoc SQL and get the results back in a dataset.
Look in the DoScript function to see how SMO can be used to build an object model of a database. Objects in SMO can be scripted just be calling their associated Script function. So just look through the comments in this method to see how this is done.
Posted
Jul 27 2006, 08:41 AM
by
dan-sullivan