部署调用webservice的clr

参考：http://www.cnblogs.com/doll-net/archive/2009/10/16/SQL_Server_2005_CLR_Invoke_Web_Service.html

http://www.cnblogs.com/jaxu/archive/2009/03/18/1415358.html

--部署clr访问webservice

--打开clr功能

sp_configure?'show advanced options',?1;

GO

RECONFIGURE;

sp_configure?'clr enabled',?1

;

--step 1

--创建一个crl(编译后为SendMailClr.dll)，拥有方法SendMail触发对webservice的调用

核心代码：

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class SendMailClr
??? {
??????? [Microsoft.SqlServer.Server.SqlProcedure()]
??????? public static void SendMail(string sMailFrom,string sMailTo,string sSubject,string sMailBody)
??????? {

???? ???? ???//EmailService_CN为被调用的webservice名称，EmailService为使用的方法
??????????? using (EmailService_CN.EmailService iMailService = new EmailService_CN.EmailService())???????
??????????? {
??????????????? iMailService.SendMail_Text(sMailFrom,"",sMailTo,sSubject,sMailBody);
??????????? }
??????? }
??? }

--调用sgon.exe(一般路径C:Program FilesMicrosoft SDKsWindowsv6.0ABinsgon.exe)

--对dll进行序列化(产生新的dll,SendMailClr.XmlSerializers.dll)

--在cmd中运行sgon.exe "SendMailClr.dll"

--将两个dll拷贝到对应服务器的路径中(例如:D:th73SendMail)

?

--step 2

--使用sysadmin权限账号登陆服务器，进行部署

--step 3

--更改需要部署的DB设置为新任

USE?Temptable

Alter?Database?Temptable?Set?trustworthy?On

?

--step 4

--创建assembly

create?assembly? DBASendMail

from?'D:th73SendMailSendMailClr.dll'

with?permission_Set=?unsafe???????????????????--应为web service是外部对象，需要采用unsafe模式

create?assembly? [DBASendMail.XmlSerializers] ????????//名称必须采用xxx.XmlSerializers格式否则无法识别

from?'D:th73SendMailSendMailClr.XmlSerializers.dll'

with?permission_Set=?unsafe

?

--step 5

--创建引用的sp

CREATE?PROCEDURE?dbo.UP_SendMailClr

@sMailFrom?nvarchar(256),

@sMailTo?nvarchar(256),sans-serif; font-size:14px"> @sSubject?nvarchar(512),sans-serif; font-size:14px"> @sMailBody?nvarchar(max)

WITH?EXECUTE?AS?CALLER

AS

EXTERNAL?NAME DBASendMail.[SendMailClr].SendMail

go

--部署clr完成

--------------------------------------------------------

--调用方法

EXEC?dbo.UP_SendMailClr?'TONY.Z.HAN@NEWEGG.COM','TONY.Z.HAN@NEWEGG.COM','TEST FOR CRL','TEST BADY'

--查看 clr相关信息

--当前部署的assemblies情况
select a.name,f.name,* from
sys.assembly_files f join sys.assemblies a
on f.assembly_id =a.assembly_id ?

--Clr当前使用内存量
select pages_kb + virtual_memory_committed_kb AS [clrNOWUSUED(kb)],*
?from sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLCLR'
--已加载的程序集
from sys.dm_clr_loaded_assemblies as clr?
join sys.assemblies? as a?
?? ?on clr.assembly_id =a.assembly_id?
join sys.dm_clr_appdomains as ad
?? ?on clr.appdomain_address =ad.appdomain_address?
--当前crl的请求状态
select * from sys.dm_exec_requests
where executing_managed_code =1
--查询clr花费的时间
select (SELECT TEXT From sys.dm_exec_sql_text(qs.sql_handle)) as CLRSQL,sans-serif; font-size:14px; line-height:22px">from SYS.dm_exec_query_stats? as qs
WHERE total_clr_time >0