Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)

  • Comments posted to this topic are about the item Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • I have never seen all of this written out so concisely before, thank you for the hard work that's gone into this.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (3/11/2015)


    I have never seen all of this written out so concisely before, thank you for the hard work that's gone into this.

    Hi Phil. You are quite welcome. And thank you, not just for being complimentary, but also for this specific compliment. It helps validate my perception that this information, while being terribly important to know, has not been made readily available / accessible. Yet this is what anyone doing SQLCLR work eventually runs into, but usually only after trial and error and then, if they can even find any information on a particular issue, it is uncertain whether it is truly useful or just misinformation. Hence my trying to improve that situation :-).

    Thanks again and take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon, you know I'm already a fan, but wow!! I agree with Phil, this article is completely unique across the internet. Anybody considering SQL CLR development should start with your Stairway and this article will be invaluable.


    David

  • SumOfDavid (3/11/2015)


    Solomon, you know I'm already a fan, but wow!! I agree with Phil, this article is completely unique across the internet. Anybody considering SQL CLR development should start with your Stairway and this article will be invaluable.

    David, thank you very much. I (and my wife 😉 ) greatly appreciate hearing that the time and effort spent on gathering the info, organizing it, writing it up, coming up with representative examples, testing those, etc is fulfilling the intention of providing help and clarity to hopefully improve the quality of SQLCLR projects.

    Thanks and take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Really love this SQLCLR series. Many thanks Solomon for your hard-work.

  • jeffrey yao (3/13/2015)


    Really love this SQLCLR series. Many thanks Solomon for your hard-work.

    Hi Jeffrey. You are quite welcome. And thank you for sharing that :-).

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Great job Solomon. Many advanced topics not found anywhere else.

    Thus, I've not found a solution to my problem.

    I would like to manage windows local groups on server through SQLCLR. I deploy my signed assembly using asymmetric key, login with this key, setting UNSAFE...

    I added the .net assemblies : System.DirectoryServices.

    My C# code works perfectly in console logged as administrator.

    The same code in stored procedures, with impersonnation, always give me "Access Denied"

    Any ideas to solve that?

    Thanks.

  • fw200811 (4/3/2015)


    Great job Solomon. Many advanced topics not found anywhere else.

    Thus, I've not found a solution to my problem.

    --snip

    Thanks.

    Rather than hijacking this thread, I suggest you start another.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi,
    Is there any possibility of the "The .NET Environment" table being updated to through SQL Server 2016 (and maybe even 2017)?

    Sincerely,
    Daniel

  • Hi,

    Thanks for updating your table up to 2022.

    I tried to find a Microsoft Road Map to see where .Net 6 ,7,8 fit in the SQLCLR picture.

    Most of my CLR use is related to accessing API sites, whereby a simple C# code does the trick. For analysis purposes, I have checked the response time of the same .Net 6 (or 7) based C# running as a console app versus the same C# in .Net 4.81.

    I got a mixed bag of results, where the console app was FASTER if the data retrieved from the API was large - like 1 meg + . For smaller returned streams, SQLCLR was faster.

    I put these differences on the latency of calling the console.

    Comparing the .Net 4.8 console to the 7 one, it was slower, for any size of returned data.

    I wish I could develop in one .Net world, but I love the 6 and 7 better language!

    Any comment is welcome.

    Thanks for the staircase, as it has helped me a lot over the years.

  • Hello. So sorry for the long delay in responding.

    jcboyer-1091017 wrote:

    Thanks for updating your table up to 2022.

    Which table are you referring to? I haven't actually had a chance to update the table in this article, and I do need to do that. But, I believe I have updated a similar table in an answer on StackOverflow ( "Which version of .NET framework SQL Server supports?" ). Is that what you were referring to? Just curious 😉

    I tried to find a Microsoft Road Map to see where .Net 6 ,7,8 fit in the SQLCLR picture.

    I doubt such a roadmap exists as, unfortunately, I don't know when (or even if) Microsoft is going to make any improvements in their CLR integration. Which is truly disheartening given what can already be accomplished with SQLCLR and how much more potential there is with even minimal improvements. However, I believe that they do not currently have anyone who truly understands the CLR integration feature, and are instead trying to push everyone to use the newer "external scripts" feature (that can make use of R, Python, JAVA, and C#, which is nice). I get the sense that the folks at Microsoft are under the mistaken belief that external scripts can accomplish the same things that CLR integration can, and it simply cannot: it's only a stored procedure API, meaning no scalar functions, TVFs, triggers, user-defined aggregates, user-defined types, context connection (to access objects within the calling transaction, etc), etc. SAD!

     

    I got a mixed bag of results, where the console app was FASTER if the data retrieved from the API was large - like 1 meg + . For smaller returned streams, SQLCLR was faster. I put these differences on the latency of calling the console.

    Yes, the startup overhead of executing a program in a command window does add up over many calls. I have experienced that myself. Executing many SQL scripts in separate calls of SQLCMD is much slower than executing all of those same scripts, concatenated together, in a single call of SQLCMD.

    Thanks for the staircase, as it has helped me a lot over the years.

    You are quite welcome. I'm glad the info has been helpful and hopefully I will soon be able to complete the series by including articles on each object type.

     

    Take care,

    Solomon....

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply