参考：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;
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
--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 Files\Microsoft SDKs\Windows\v6.0A\Bin\sgon.exe)
--对dll进行序列化(产生新的dll,SendMailClr.XmlSerializers.dll)
--在cmd中运行sgon.exe "SendMailClr.dll"
--将两个dll拷贝到对应服务器的路径中(例如:D:\th73\SendMail)
--step 2
--使用sysadmin权限账号登陆服务器，进行部署
--step 3
--更改需要部署的DB设置为新任
USE Temptable
GO
Alter Database Temptable Set trustworthy On
--step 4
--创建assembly
create assembly DBASendMail
from 'D:\th73\SendMail\SendMailClr.dll'
with permission_Set= unsafe --应为web service是外部对象，需要采用unsafe模式
create assembly [DBASendMail.XmlSerializers] //名称必须采用xxx.XmlSerializers格式否则无法识别
from 'D:\th73\SendMail\SendMailClr.XmlSerializers.dll'
with permission_Set= unsafe
--step 5
--创建引用的sp
USE Temptable
GO
CREATE PROCEDURE dbo.UP_SendMailClr
@sMailFrom nvarchar(256),
@sMailTo nvarchar(256),
@sSubject nvarchar(512),
@sMailBody nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DBASendMail.[SendMailClr].SendMail
go
--部署clr完成
--------------------------------------------------------
--调用方法
USE Temptable
GO
EXEC dbo.UP_SendMailClr 'TONY.Z.HAN@NEWEGG.COM','TONY.Z.HAN@NEWEGG.COM','TEST FOR CRL','TEST BADY'
--查看 clr相关信息
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'
--已加载的程序集
select a.name,ad.appdomain_name,clr.load_time ,*
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,*
from SYS.dm_exec_query_stats as qs
WHERE total_clr_time >0