Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a dll in .NET for some custom CLR spatial functions in SQL Server 2008 R2. I sent the dll to a colleague along with a script to deploy it (CREATE ASSEMBLY then create the external functions etc).

She asked whether she needed to keep the file in the same location after it had been registered using CREATE ASSEMBLY. I didn't know, so I experimented by registering an assembly and creating the functions from it in sql server. Then I renamed the dll and restarted sql server. I found that the functions still worked after this so it appears that sql server has loaded the dll in some way and it doesn't need to stay on the file system. What's puzzling, though, is that the table sys.assembly_files does store the original path of the dll. Why would it do that if it isn't relevant?

So I'm not sure my test for this was right. But does anyone know what CREATE ASSEMBLY does with a dll? Does it copy/import it somewhere? Or was my test flawed and sql server does require the original dll to stay in the same location?

Thanks in advance.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

The bits are copied into the database where you call CREATE ASSEMBLY or ALTER ASSEMBLY.

The path you select in Management Studio is only a convenience for it to read the file and dump out the bits into a varbinary(MAX) value (hit the Script button in that dialog to see what I mean).

This also means that the assembly is "carried" with the database if you do something like backup/restore on another server.

share|improve this answer
    
Thanks very much. That makes a lot of sense. I scripted the assembly and did see a long binary value like this: CREATE ASSEMBLY [AlphaShapes] AUTHORIZATION [dbo] FROM 0x4D5A900003000000 ... WITH PERMISSION_SET = SAFE It was about 19000 characters long, which sounds roughly right for a 10K dll. –  andyb Jun 27 '12 at 16:30
    
@andy: Yep. One other advantage of having the bits directly in script is that in a deployment scenario, you don't have to rely on the file system, which is great. Also (aside), you can use backticks (`) to delimit code both in answers and comments. –  Jon Seigel Jun 27 '12 at 16:34
    
Thanks for the tip. –  andyb Jun 27 '12 at 16:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.