CRL Assembly after reboot

Question

Post reply

CRL Assembly after reboot

Dale Burrell

SSCommitted

Points: 1737
More actions
October 3, 2009 at 7:44 am

#226846

Silly question, but if I load a CLR assembly and then reboot/restart SQL Server does the assemble remain loaded?
Thanks

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Yes, the configuration information to load the assembly is saved in the system "tables".

(Edit: NOTE, actually the entire assembly is also saved in the database itself., see below).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

I'm always curious about what people use CLR's for. What does this one do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dale Burrell SSCommitted Points: 1737 More actions · Answer 3

@jeff Moden haha... now that would be telling 🙂 we have a document system accessed by a web interface. Documents are built from document templates which contain merge fields e.g. [ClientName], the merge fields contain SQL and can access any information that can be 'reached' from the Document record. When a new document is created or an existing document is reset we take the contents of the document template and then replace all the merge fields with their actual instance values and put that into the document record. Now we currently do this in T-SQL but replacing merge fields in an ntext field requires that the field is read into buffers, iterated through, and using data pointers the merge field are replaced with their values. Quite clearly something that T-SQL is not good at, and is slow and puts locks on other tables. Whereas writing the same code in C# is trivial and importantly doesn't lock anything while its merging.

Dale Burrell SSCommitted Points: 1737 More actions · Answer 4

@RBarryYoung - thanks for that, does that mean I need to leave the physical assemblies in the same place I originally loaded them from? ( just slapped them on C: expecting to delete them once they are loaded but would move them somewhere more structured if they need to hang around.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

Dale Burrell (10/3/2009)
@Jeff Moden haha... now that would be telling 🙂 we have a document system accessed by a web interface. Documents are built from document templates which contain merge fields e.g. [ClientName], the merge fields contain SQL and can access any information that can be 'reached' from the Document record. When a new document is created or an existing document is reset we take the contents of the document template and then replace all the merge fields with their actual instance values and put that into the document record. Now we currently do this in T-SQL but replacing merge fields in an ntext field requires that the field is read into buffers, iterated through, and using data pointers the merge field are replaced with their values. Quite clearly something that T-SQL is not good at, and is slow and puts locks on other tables. Whereas writing the same code in C# is trivial and importantly doesn't lock anything while its merging.

Sounds good. Thanks for taking the time for the feedback.

It sounds like you guys rewrote what sp_MakeWebTask does. Between that and some simple set based SELECTs, I've never had to explicity "iterate" through data with T-SQL even to gen things like documents or email docs. I've also never had the problem of blocking while it's merging.

That notwithstanding, it sounds like a decent thing to write a CLR for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Dale Burrell (10/3/2009)
@RBarryYoung - thanks for that, does that mean I need to leave the physical assemblies in the same place I originally loaded them from? ( just slapped them on C: expecting to delete them once they are loaded but would move them somewhere more structured if they need to hang around.

~~Yep,~~ assemblies are just DLLs, so once you load/install one, you can't then move it while it is still loaded. If you try to do it while SQL Server is running, you should get the "Can't ..etc.. because it is opened by another user." error message. If you shutdown SQL Server first, you could move it then, but you'd have problems when you tried to restart the database.

~~If you want to move the assembly, unload it from SQL Server first, then reload from the new location.~~

~~Best Practice: put it in the right place first, then load it. (the \bin subdirectory is one popular place).~~

(edit: Note: almost everything that I said here is wrong, see later posts, below).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 7

When you create an assembly with CREATE ASSEMBLY, it's actually stored internally, and loaded from there from that point on. The DLL on disk doesn't have any bearing on what SQL Server believes the assembly to be.

You will find the data for the assembly in the system view sys.assembly_files, with a file_id of 1.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Matt Whitfield (10/4/2009)
When you create an assembly with CREATE ASSEMBLY, it's actually stored internally, and loaded from there from that point on. The DLL on disk doesn't have any bearing on what SQL Server believes the assembly to be.
You will find the data for the assembly in the system view sys.assembly_files, with a file_id of 1.

Hmm, well you're definitely right. I spent the last hour or so checking this out and then trying to figure out how I got this completely wrong. 🙁 Oh well, at least you were here to correct my mistake, Matt.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 9

Barry, I've learnt such a lot from your posts over the time that I've spent on this forum, I'm glad I could just go a small way to repaying the favour! 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Dale Burrell (10/3/2009)
@Jeff Moden haha... now that would be telling 🙂 we have a document system accessed by a web interface. Documents are built from document templates which contain merge fields e.g. [ClientName], the merge fields contain SQL and can access any information that can be 'reached' from the Document record. When a new document is created or an existing document is reset we take the contents of the document template and then replace all the merge fields with their actual instance values and put that into the document record. Now we currently do this in T-SQL but replacing merge fields in an ntext field requires that the field is read into buffers, iterated through, and using data pointers the merge field are replaced with their values. Quite clearly something that T-SQL is not good at, and is slow and puts locks on other tables. Whereas writing the same code in C# is trivial and importantly doesn't lock anything while its merging.

Out of curiousity, what datatypes do you use in your CLR to accomplish this? I'd assume that you might be going with SqlString or string (most people use these), but you could see benefit from making use of SqlChars and streaming. The memory overhead streaming with appropriate planning/coding would be smaller.

If you are on SQL 2005, why still ntext instead of switching to a varchar(max) datatype which would be much easier to update from TSQL without the complexity you describe.