Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 9:51 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
Comments posted to this topic are about the item Stairway to SQLCLR Level 2: Sample Stored Procedure and Function




SQL# - http://www.SQLsharp.com/
Post #1542849
Posted Wednesday, February 19, 2014 7:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 553, Visits: 2,575
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1543031
Posted Wednesday, February 19, 2014 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1543130
Posted Wednesday, February 19, 2014 10:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 553, Visits: 2,575
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1543140
Posted Wednesday, February 19, 2014 11:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1543141
Posted Wednesday, February 19, 2014 11:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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 .

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# - http://www.SQLsharp.com/
Post #1543146
Posted Wednesday, February 19, 2014 11:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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# - http://www.SQLsharp.com/
Post #1543149
Posted Wednesday, February 19, 2014 12:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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...





SQL# - http://www.SQLsharp.com/
Post #1543168
Posted Wednesday, February 19, 2014 12:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1543174
Posted Wednesday, February 19, 2014 1:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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 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..





SQL# - http://www.SQLsharp.com/
Post #1543213
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse