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 want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

I know this is a bit brutal, but what about disabling the CLR and re-enabling it?

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
share|improve this answer
2  
One important detail about this method is that it works when executed against a STANDBY (read-only) database; all other methods I've tried do not. I needed this because an update to a CLR assembly propagated as normal via log shipping to a STANDBY catalog, but the AppDomain didn't reload---so it continued running the code from the old version of the .dll for about a day. –  Granger Oct 28 at 16:19
    
@Granger very interesting and good to know :). However, I would consider that a bug within SQL Server. You might want to report that via the Connect site: connect.microsoft.com/SQLServer/Feedback –  srutzky Oct 29 at 17:32
    
@srutzky - Thanks for the suggestion; I expect they'd just close the report as "Won't fix". The setting is server-wide, not per-catalog (just like 'nested triggers', 'filestream access level', etc.). That's quite the can of worms I'd be trying to open. –  Granger Oct 30 at 15:02

There is a more elegant solution that won't affect all other assemblies: just change the PERMISSION_SET of one of the assemblies in the app domain (app domains are per user).

ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = {1 of the 2 levels that 
                                                      this assembly is not current at}

Just remember that you will need to set the PERMISSION_SET back to what it was. Also, you need to access a method in the assembly before changing the PERMISSION_SET will unload it; changing an assembly that is not currently loaded into an app domain that is active, but with another assembly, has no effect on the app domain (App Domains are per-DB, per-User, not per-Assembly).

share|improve this answer

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.