I am seeking ways to simplify the deployment of user coded assemblies, functions, procs etc.
I have coded a simple test script that accepts the full path of some DLL and then uses dynamic TSQL to construct a 'CREATE ASSEMBLY' command that is then run to install the assembly.
But I'd like to have a way to then automatically create the functions and procedures defined in the assembly.
There does not seem to be any automatic way, so I want to know if it is sensible to code some C# support logic?
What I am thinking of doing is creating the assembly (as I do above with dynamic TSQL) from the full file path, and then invoke a special reflection based operation in a specially coded support routine.
This could be named 'implement_assembly' or something and would accept the assembly name.
In that C# code, I could use reflection to obtain all defined procs, functions etc (as these have a special attribute as you know) and extract all relevant details.
I could then create TSQL strings that contain the various CREATE PROCEDURE and CREATE FUNCTION commands, with all required args and so on.
Finally I could use the SqlClrContext and stuff to submit the full commands back into SQL server to be executed as standard TSQL queries.
I can probably do this, BUT is this something already supported in some way? perhaps with SQL SERVER 2008 R2 (I am not using R2)?
What are people's thoughts?
View Complete Post