Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

  • 5 stars! I just finished this and really enjoyed it. This is turning into a very excellent stairway Solomon and I can't wait to read the rest of the "levels". I also like how you included the additional resources again - (as you did in the first stairway.)

    One technical note: your link to .Net languages is broke: http://www.dotnetlanguages.net/DNL/Resources.aspx.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (2/19/2014)


    Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    I am a CLR noob too so forgive me if this is not the correct answer or what you are looking for but...

    If you have already created the assembly you can open SSMS, go to <yourdb> then Programmability then Assemblies. Then you could right-click the assembly > select "Script Assembly AS" > Create To > New window...

    This will give you the DDL to create the assembly; the hex string after FROM is the code you are looking for. You can then copy that code and convert it to varbinary as shown below...

    Hope this helps. If not, I'm sure Solomon will point you in the right direction.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B (2/19/2014)


    SQLRNNR (2/19/2014)


    Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    I am a CLR noob too so forgive me if this is not the correct answer or what you are looking for but...

    If you have already created the assembly you can open SSMS, go to <yourdb> then Programmability then Assemblies. Then you could right-click the assembly > select "Script Assembly AS" > Create To > New window...

    This will give you the DDL to create the assembly; the hex string after FROM is the code you are looking for. You can then copy that code and convert it to varbinary as shown below...

    Hope this helps. If not, I'm sure Solomon will point you in the right direction.

    Oops I meant binary representation as shown in the examples. If you already have a CLR deployed, that is great. But if you are building from scratch, how to get that binary representation so you don't have to deploy an external dll too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Alan.B (2/19/2014)


    5 stars! I just finished this and really enjoyed it. This is turning into a very excellent stairway Solomon and I can't wait to read the rest of the "levels". I also like how you included the additional resources again - (as you did in the first stairway.)

    One technical note: your link to .Net languages is broke: http://www.dotnetlanguages.net/DNL/Resources.aspx.

    Hi Alan. Thank you for that great compliment. Of all of the articles I have published (14 so far), I was the most nervous about this one. I am trying really hard to establish a general understanding of what SQLCLR is and can do before getting into doing anything with it. I feel it best to have a context of "why am I doing this" before getting into "how can I do this". So I have what I feel is a less-than-traditional article where I show code and I need to explain it do a degree but not to the point of getting someone to be able to reproduce it. I had no idea how it would be received so I am very glad to see that I am on the right track :-D.

    Regarding the .Net languages list: It worked as of a few weeks ago so that is too bad. I will have to find another link. Thanks for pointing it out.

    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

  • Alan.B (2/19/2014)


    SQLRNNR (2/19/2014)


    Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    I am a CLR noob too so forgive me if this is not the correct answer or what you are looking for but...

    If you have already created the assembly you can open SSMS, go to <yourdb> then Programmability then Assemblies. Then you could right-click the assembly > select "Script Assembly AS" > Create To > New window...

    This will give you the DDL to create the assembly; the hex string after FROM is the code you are looking for. You can then copy that code and convert it to varbinary as shown below...

    Hope this helps. If not, I'm sure Solomon will point you in the right direction.

    Hey there. That method of getting the "bytes" starts out ok but then you inadvertently alter the bytes in the CONVERT, which is actually an extraneous step anyway. The varbinary representation IS the FROM 0x4D5A900003.....

    There is no point in converting it from there as it renders it useless. The 0x3078... is unusable.

    I know that the question has been clarified, and to a degree this is jumping ahead a bit as I will cover this later in the series, but you can also get the varbinary out of SQL Server by doing:

    SELECT * FROM sys.assembly_files

    I will answer the other question (getting the varbinary prior to it being in SQL Server) separately.

    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

  • SQLRNNR (2/19/2014)


    Alan.B (2/19/2014)


    SQLRNNR (2/19/2014)


    Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    I am a CLR noob too so forgive me if this is not the correct answer or what you are looking for but...

    Oops I meant binary representation as shown in the examples. If you already have a CLR deployed, that is great. But if you are building from scratch, how to get that binary representation so you don't have to deploy an external dll too.

    Hi there, Jason. First off, thanks for that positive feedback. I am glad that you enjoyed the article.

    Second, regarding getting the "bytes" / VARBINARY / hex code of the Assembly prior to it going into SQL Server, that is jumping a little bit ahead as I will cover that stuff in Level 4, but I can give the basics of it here :-). There are two ways to do it:

    1) In the newer versions of Visual Studio, in the Project Properties, be sure to check "Generate DDL" and then Publish the project to a local DB. The generated DDL should have the assembly via FROM 0x....

    2) use a little command-line utility to convert a binary file into hex code text. I created one years ago to help me automate the building of SQL# as I include it in a post-build event :-). I hadn't yet decided if I was going to share that utility when I do Level 4-Development, or if so if I would include it in the article or publish it on https://SQLsharp.com/. For now, if you are interested then send me a PM here or contact me via the SQL# website.

    Hope that helps. Take care, Solomon...

    • This reply was modified 5 years, 6 months ago by  Solomon Rutzky.
    • This reply was modified 5 years ago by  Solomon Rutzky. Reason: fix link

    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 Rutzky (2/19/2014)


    SQLRNNR (2/19/2014)


    Alan.B (2/19/2014)


    SQLRNNR (2/19/2014)


    Great article. Very useful information.

    One question I think could be answered and would be very useful to those noobs to CLR.

    How do you get the varbinary representation for the creation of the assembly?

    I am a CLR noob too so forgive me if this is not the correct answer or what you are looking for but...

    Oops I meant binary representation as shown in the examples. If you already have a CLR deployed, that is great. But if you are building from scratch, how to get that binary representation so you don't have to deploy an external dll too.

    Hi there, Jason. First off, thanks for that positive feedback. I am glad that you enjoyed the article.

    Second, regarding getting the "bytes" / VARBINARY / hex code of the Assembly prior to it going into SQL Server, that is jumping a little bit ahead as I will cover that stuff in Level 4, but I can give the basics of it here :-). There are two ways to do it:

    1) In the newer versions of Visual Studio, in the Project Properties, be sure to check "Generate DDL" and then Publish the project to a local DB. The generated DDL should have the assembly via FROM 0x....

    2) use a little command-line utility to convert a binary file into hex code text. I created one years ago to help me automate the building of SQL# as I include it in a post-build event :-). I hadn't yet decided if I was going to share that utility when I do Level 4-Development, or if so if I would include it in the article or publish it on sqlsharp.com. For now, if you are interested then send me a PM here or contact me via the SQL# website.

    Hope that helps. Take care, Solomon...

    Perfect all the way around. I will watch for level 4 as well.

    Thanks for answering that question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (2/19/2014)


    Solomon Rutzky (2/19/2014)


    ...regarding getting the "bytes" / VARBINARY / hex code of the Assembly prior to it going into SQL Server, that is jumping a little bit ahead as I will cover that stuff in Level 4, but I can give the basics of it here :-). There are two ways to do it:

    1) In the newer versions of Visual Studio, in the Project Properties, be sure to check "Generate DDL" and then Publish the project to a local DB. The generated DDL should have the assembly via FROM 0x....

    2) use a little command-line utility to convert a binary file into hex code text. I created one years ago to help me automate the building of SQL# as I include it in a post-build event :-). I hadn't yet decided if I was going to share that utility when I do Level 4-Development, or if so if I would include it in the article or publish it on https://SQLsharp.com/ . For now, if you are interested then send me a PM here or contact me via the SQL# website.

    Hope that helps. Take care, Solomon...

    Perfect all the way around. I will watch for level 4 as well.

    Thanks for answering that question.

    No problem at all. I should mention two things about Visual Studio generating the DDL:

    1) If you are running the generated SQL in SSMS, Management Studio doesn't handle "long" lines very well. I don't know at exactly what point a single set of characters (no breaks) is considered "long", but opening a SQL script with a long line will get an error and that tab in SSMS might slow down when trying to move across that line. Keep in mind that a single byte is really two characters: 0 - 255 => 00 - FF. Meaning, a 50k Assembly is really 100k hex characters. One of the things I did in that custom utility is to break the line at a fixed number of characters, each line ending with a back-slash (as shown in the article and in the example code: each line is 500 characters). I don't know if Visual Studio breaks the VARBINARY / hex code up at all (I assume not).

    2) The generated SQL script should be in the output folder that also has the DLL and some other files. This depends on your Configuration, but is typically either "bin\Debug" or "bin\Release".

    Take care,

    Solomon..

    • This reply was modified 5 years, 6 months ago by  Solomon Rutzky.
    • This reply was modified 5 years ago by  Solomon Rutzky. Reason: fix link

    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

  • Thank you. Excellent article. Everything for start with CLR in one place.

    Have a nice day, Matjaz

  • SQLRNNR wrote:

    How do you get the varbinary representation for the creation of the assembly?

    Hey there, Jason. I had forgotten that this question was out there. While I did address this within the context of Visual Studio / SSDT in levels 7 and 8:

    I did also mention a command-line utility that I wrote that is completely independent of Visual Studio / SSDT. I have a short-cut to it set up in my "Send To" folder so that I can right-click on any DLL and convert it to a nicely formatted (i.e. no more than 70 characters per line) VARBINARY / hex bytes string, saving directly to the clipboard so all I need to do it paste it into a script or directly into an SSMS query window. It can also output to a file if you want to incorporate it into a build / CI process.

    It's open-source, called BinaryFormatter, and found on GitHub at:

    https://github.com/SqlQuantumLeap/BinaryFormatter/

    I hope that helps. 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

  • Comments posted to this topic are about the item Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

    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