Question regarding appropriate applications of SQL CLR

Question

Question regarding appropriate applications of SQL CLR

jshahan

SSCarpal Tunnel

Points: 4622
More actions
April 5, 2012 at 8:10 am

#256030

I originally posted this as a discussion point from the recent article regarding CLR integration but decided that this should probably have its own thread.
I have a general question/issue I would like to raise about the when the use of this technology is appropriate.
A fellow worker with some experience with SQL CLR integration said that it makes more sense in an organization that supports a limited number of databases because “it is a pain to install and support” the assemblies (sorry I don’t have the specifics regarding what caused his grief).
I work for a software company with around 100 installations with a minimum of two databases per site.
Is my coworker correct or is SQL CLR something that should be easily supported across multiple physical locations and databases?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

just my two cents:

if you've created a CLR assemby on one of your servers, it is trivial to script the assembly as a binary string, which can then be executed just like any other TSQL script.

so if the deployment headache was "here's a script and a zip file of DLLs, you have to change the script c:\temp\ path to match wherever you unzipped the DLL's"

there is a much easier way to do that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 2

Thanks much, Lowell. We may have not been aware of how easily this can be accomplished and I'm really glad I asked. I'll run it by my coworker to see if he is using this methodology.

Seems too good to be true. 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

jshahan (4/5/2012)
Thanks much, Lowell. We may have not been aware of how easily this can be accomplished and I'm really glad I asked. I'll run it by my coworker to see if he is using this methodology.
Seems too good to be true. 🙂

i was ECSTATIC when i learned this...

no kidding, i used to do it the super hard way: i saw a code example to do the binary thing,so i used that as a model... and i'd manually load the dll into a varbinary max filed, then selecti it, copy and paste it....edit the model so that the dll's matched...the deployed code...

oh man what a pain.

i felt like an Id-ten-T after i learned this...so i want to help others learn the right way on this one!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

It is way cool. Do you know if the assembly itself is stored in the system table in the format in which it scripts out or is the dll actually sitting out there on the server somewhere?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I would actually go one further - why not use the deploy option from within Visual Studio? You don't have to build out the scripts at all. Build the assembly, test it, then project, deploy, point it at a server where yuou want to execute it and let it go. You'll have to secure it afterwards, but the hard part is done.

I don't understand the resistance to using that technique, and yet - very few folks actually use it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

The initial deployment would be from visual studio to a local version of the database and then scripted for the upgrade process for databases across the country.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 7

Let’s say that I have created and tested an assembly locally on a database named HOME.

Now I want to deploy that assembly on a database called PRODUCTION on another network (different database and instance name).

Should creating a CREATE ASSEMBLY script on HOME (by right-clicking the assembly) and running it on the remote PRODUCTION database successfully install the assembly with its functionality on the PRODUCTION database? Or would the assembly be looking for a database called HOME?

Lowell SSC Guru Points: 323518 More actions · Answer 8

whhjops! posted to the wrong thread!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!