SQLCLRProject

The SQLCLRProject has a new home. You can find it here.

This page will be deleted eventually.

17 Responses to “SQLCLRProject”

  1. Brett J Says:
    January 16, 2008 at 1:40 pm

    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?

  2. nielsb Says:
    January 16, 2008 at 2:03 pm

    Hi Brett,

    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.

    Niels

  3. Kathy M Says:
    January 28, 2008 at 9:58 pm

    Woudl you be willing to make the source available if we agreed not to post it anywhere?

  4. nielsb Says:
    January 29, 2008 at 6:39 am

    Kathy,
    Just out of curiosity – what improvements were you thinking of doing when having the source?

    I have sent you an email.

    Niels

  5. Doug Rank Says:
    January 29, 2008 at 6:03 pm

    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?

    Any help or insight is appreciated.
    Doug

  6. nielsb Says:
    January 30, 2008 at 8:05 am

    Doug,
    As you know – this should now have been fixed.

    For you that wants the fixed file 9or to download the whole project) go to the download page.

    Niels

  7. Christophe Says:
    March 7, 2008 at 4:49 pm

    Hi Niels,

    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!

    Would it be possible?

    Regards,
    Christophe

  8. Shira Says:
    March 21, 2008 at 10:25 pm

    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?

    Thank you very much!

  9. Hitesh Says:
    March 26, 2008 at 4:46 pm

    Hi,

    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.

    Thanks & Regards,
    Hitesh

  10. Shira Says:
    March 27, 2008 at 5:20 pm

    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.

  11. Judah Says:
    May 15, 2008 at 9:01 pm

    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.

  12. jafin Says:
    July 21, 2008 at 1:29 pm

    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)

  13. thk Says:
    November 14, 2008 at 1:34 pm

    Hello, first of all.. great & helpful tool.

    I found a little issue about the yield operator, which isn’t a problem of the tool itself I think, but nice to know, since it saves much time searching for the real error.

    [SqlFunction(Name = “XY”, FillRowMethodeName = “FillRowXY”)]
    public static IEnumerable MyCLRFunction( )
    {
    MyCustomType value;
    ….
    yield return value;
    ….
    }

    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)

  14. Brian G Says:
    January 13, 2009 at 4:05 am

    An evolution and a problem:

    Evolution:
    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 🙂

    Problem:
    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.
    Build 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)…

    Thanks,
    B

  15. Redart Says:
    January 14, 2009 at 8:58 am

    Hi, Niels.
    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.

  16. Redart Says:
    January 14, 2009 at 9:15 am

    Here, in case somebody need it:
    http://rapidshare.com/files/183067722/yukondeploy.zip
    fixed version of v2.6, extract and put it in “C:\Program Files\DevelopMentor\YukonDeploy\bin\”, instead of existing one.

  17. Brian Greene Says:
    February 3, 2009 at 5:16 am

    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.

Comments are closed.