I thought I am CLR Integration Guru in Sql Server but this morning I came to know that there are lots of things left to grab. While I was moving the local database on the new live server I run some CLR Procedure which Says these procedures required CLR Integration enabled. So to get that done.
I went on SQL Server Surface Area Configuration -> Surface Area configuration for features -> Enable CLR Integration. till here, every thing seems fine I encounter no error.
After that when I re-execute the same query I get a new error:
"Invalid connection (Common languageruntime (CLR) execution is not supported under lightweight pooling.Disable one of two options: "clr enabled or "lightweight pooling".
I really have no idea what the light weight pooling is but the need of the time is to disable it. After googling for a bit I get
this link. but still no use as the query written in this page is not working infact it is giving error.
Query:sp_configure 'show advanced options', 1;GOsp_configure 'lightweightpooling', 0;GORECONFIGURE;GO
Error:The configuration option 'lightweightpooling' does not exist, or it may be an advanced option.
To find what is going wrong, I quried on sys.configurations because this table contain database configuration.
select * from sys.configurations where name like '%light%'
Finally, after running the above query I came to know that there is a space in 'lightweightpooling'. It means it is 'lightweight pooling. Now when I diagnose the problem I execute the following query which make my sql server CLR Integrated.
USE masterGOEXEC sp_configure 'show advanced options', 1GORECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'lightweight pooling', 0GOEXEC sp_configure 'clr enabled', 1goRECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'show advanced options', 0GO
After executing the query, we might need to restart the SQL Services to make it work.