Niels, This is a great tool that I plan on using. Does it have any features to help out with deploying an assembly that accesses a web service? I.e. can it auto SGEN the serializer assemblys and deploy that as well?
No, at the moment It cannot handle web-service references (even though, you can have other assembly references in your project and those will be handled). This is definitely something to look at for a new feature.
This seems like a great tool. I was looking at writing a similar utility to automate our deployment of SQL CLR code, but fortunately you have done most of the work!
Is it possible to get more information about how dependencies are registered along with the binary representation of the assembly?
When I reference a single dependency
SqlClrCode.dll -> Constants.dll
The DeployAssembly task works exactly as expected. I get both dlls registered in sql server, with the second as a dependency of the first.
However, in real life, my project has a much more complicated dependency structure. Something like
SqlClrCode.dll -> BusinessObjects.dll -> XML.dll -> Data.dll -> Constants.dll
In this situation, DeployAssembly will fail with the message “Assembly ‘xml, version=1.0.2949.20834, culture=neutral, publickeytoken=039109d9ceb6f2f1.’ was not found in the SQL catalog.”
I did some experiments with trying to register the assembly as a file, and came across a possibly related problem.
If I build a regular, signed C# project with the same SqlCLR code and dependencies in it, I can then say
CREATE ASSEMBLY [MyCLR] FROM ‘C:\MyCLR\bin\Debug\MyCLR.dll’ WITH PERMISSION_SET = UNSAFE
This works, and pulls in all the dependent assemblies.
When I try to register the assembly from my SqlClrProject project (also signed) in this fashion, however, I get the error “The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.”
What is special about the SqlClrProject dll that prevents it from being registered in this fashion?
Kathy was asking about source code … I would be interested as well :) I’m using your tool to deploy my assemblies, but I have many servers to handle. I’d like to add some kind of automation, depending on my needs!
I have just started using Sql Clr assemblies and came across your tool trying to decide how to script them to install at customer sites, as I work for a software vendor.
I came across your tool, and it sounds great – I can’t find any information however on restrictions on using it, cost, etc. Are there any restrictions on its use or distribution? i.e. Is it free for us to use both internally, and as a component integrated into our install packages that they supply to customers?
Also, currently, all of our DB updates are scripted in TSQL, and we run a single upgrade utility that uses a sequence file to determine the order in which to run the scripts. I had been hoping to integrate the SQL CLR installs into that same structure as just another script or set of scripts. I’m not sure whether your tool could fit into that model, or if not, whether you’d be willing to share the source and we might be able to integrate the essential logic into our installation utility?
Currently, we are migrating our TSQL stored procedure to SQL CLR stored procedure. Is your tool capable to convert the existing SP and provide us .NET code to create CLR SP?
There are 100s of SP and it is very big task to convert all. It would be great if there is any tool available which can do that for us. Can you please let me know if you have any idea about such a tool.
Hitesh, I doubt there’s a way to automate that kind of move, but in any case, you may want to rethink migrating all stored procedures to SQL CLR. There are performance concerns with the context switching that is necessary to run SQL CLR stored procedures. The documentation I’ve seen advises that they should be used only for tasks that are not easily or efficiently do-able in TSql – for example regular expression string validations.
This is great, I’ve been using this since an earlier version ~2006.
Noticed some things: v.2.6 when deploying I keep getting a message after attempting to CREATE ASSEMBLY that it can not find the deployproperties assembly. I see it up there, the information seems to match. Rolled back to 2.5.
In v2.5 – In an Aggregate: Accumulate([SqlFacet(MaxSize=8000)] SqlString value) does not seem to work, it keeps getting defined as @Value nvarchar(50). The [return: SqlFacet(MaxSize=-1)] for Terminate() works find though.
I am having similar issues to Judah but with 2.6 in relation to aggregates. have the attribute [SqlFacet(MaxSize =-1) on the public SqlString Terminate() method and the sql script is defining this as nvarchar(50)
If MyCustomType is part of a referenced assembly, the code compiles, but on deploying the type deploy task throws an ReflectionTypeLoadException.
(Failed to load at least one type, check LoaderExceptions-Property)
Many of the things that can be done with this tools should be available with PowerShell commandlets. I’ve written a number of them, and would be happy to help – you have my email :)
Using the latest build of the project with VS.net 2008 3.5sp1 on Vista 64bit. Checked out of source control (was using the project on VS.Net 2005), converted the project with the wizard, and now the DeployMeth portion won’t deploy. Actually looks like the sql.proj build file has errors with 2008
Console output is:
Done building target “DeployMeth” in project “sql.proj” — FAILED.
Done building project “sql.proj” — FAILED.
W:\dev\raqm\trunk\clrprocs\sql.proj(42,5): error : The device is not ready.
W:\dev\raqm\trunk\clrprocs\sql.proj(42,5): error :
And yeah – that’s it – nothing for the second error, but I’m a bit confused and wonder if you’d have a minute for some feedback.
Thanks, and I look forward to hearing your opinions on the PowerShell CmdLets, as they would fit into my deployment process quite nicely (and we do CI with PS too)…
As you probably know, version 2.6 contains hardcoded string in yukondeploy.dll, DeployAssembly.DeployAttributeDll(), that uploads deployattributes.dll to server. The problem is that this string contains v2.5 of such dll, and all references are set to version 2.6, so nothing works as expected. Can you, please, fix this little problem, it won’t take a long.
First the kudos – this is a great tool. It has made me infinitely more productive with clr procs etc.
I have a question about a feature that is causing me a great deal of trouble.
If the ConnectDatabase property (to msbuild from the command line) is set to false, the tool does not introspect the assembly and drop the procs etc prior to the assembly drop/create cycle.
I was REALLY hoping to use this in a CI environment… I have a project with a number of other manually created scripts that are run in sequence, and I was just tonight putting together the PS script that would call MS build, rebuild the dll, and then call this tool to create the appropriate sql scripts.
I will NOT be connecting to the database at build time – in fact I cannot, but I’d like to be able to drop procs/functions etc, drop assembly, create assembly, create procs/functions…
This allows a tagged set of scripts to be run against an existing environment or a new environment with no change, as well as allowing me to script the renaming of the generated script files such that they fit into the sequence of the other scripts I have (some of my scripts call the clr objects…)
ANY input on how I can accomplish this, or the rationale behind the “resuse” of the connect database property would be greatly appreciated. I keep recommending your tool rather than what’s built into VS or other options, but this limitation is hurting a lot today as I have to manually copy and paste from the generated scripts to assemble the script I want for automated deployments…
Hopefully this “problem” is just me being dense and there is some perfectly easy way to do what it is I’m looking for…
Again – your time in developing this excellent utility is greatly appreciated – the VS integration is great (one button macro to redo everything – I’m lazy), and building it as a series of MSBuild tasks gets me SO close to what I’m looking for. I’m looking forward to your enlightenment.