如何开启SQL CLR

 如何开启SQL CLR

 

开启SQL CLR有两步操作，如下所示：

1.       开启SQLCLR，在sql server2005中执行如下脚本，开启SQL CLR功能

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-- Turn advanced options back off

EXEC sp_configure 'show advanced options' , '0';

go

ALTER DATABASE test SET TRUSTWORTHY ON

reconfigure

go

2.       　注册程序集，因为使用C#实现SQL功能，有些DLL文件没有被SQL SERVER安装，所以要手动注册，在在sql server2005中执行如下脚本：

CREATE ASSEMBLY

SMDiagnostics from

'C:WindowsMicrosoft.NETFrameworkv3.0Windows communication FoundationSMDiagnostics.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY

[System.Web] from

'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY

[System.Messaging] from

'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Messaging.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY 

[System.IdentityModel] from

'C:Program FilesReference AssembliesMicrosoftFrameworkv3.0System.IdentityModel.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY 

[System.IdentityModel.Selectors] from

'C:Program FilesReference AssembliesMicrosoftFrameworkv3.0System.IdentityModel.Selectors.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY -- this will add service modal

 

[Microsoft.Transactions.Bridge] from

'C:WindowsMicrosoft.NETFrameworkv3.0Windows Communication FoundationMicrosoft.Transactions.Bridge.dll'

with permission_set = UNSAFE

 

GO