部署clr存储过程

ALTER DATABASE WS SET TRUSTWORTHY ON
USE WS
GO
--Enable CLR Integration   
exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Concatenate]') AND type = N'AF')
DROP AGGREGATE [dbo].[Concatenate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IMSend]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IMSend]

--Drop the functions if they already exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RegExIsMatch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RegExIsMatch]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RegExReplace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RegExReplace]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RegExMatches]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RegExMatches]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RegExSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RegExSplit]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RegExSplitPart]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RegExSplitPart]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'IDMTSqlExtLib')
DROP ASSEMBLY [IDMTSqlExtLib]
GO
/*
alter database idmtCode set trustworthy on
GO
*/
/*
--
--EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
*/
CREATE ASSEMBLY [IDMTSqlExtLib]
FROM
--'E:/Personal/My Documents/Visual Studio 2005/Projects/IDMT/ENTAPP/SqlExtLib/SqlExtLib/bin/Release/IDMT.ENTAPP.SqlExtLib.dll'
'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/IDMT.ENTAPP.SqlExtLib.dll'
with permission_set =
--EXTERNAL_ACCESS
UNSAFE
GO

CREATE AGGREGATE [dbo].[Concatenate]
(@value NVARCHAR(4000))
RETURNS NVARCHAR(max)
EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.Concatenate]
GO
/*
select dbo.Concatenate(empid) from (select top 10 empid from VW_ADUsers ) U
*/

CREATE PROCEDURE [dbo].WebS(
@web NVARCHAR(50),@action NVARCHAR(50),@datas NVARCHAR(4000))
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.WS].SendIM
GO

/*
EXEC dbo.IMSend '1047','sql hihi'
*/
CREATE FUNCTION [dbo].[ufn_RegExIsMatch]
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)        
RETURNS BIT 
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.RegEx].RegExIsMatch
GO

CREATE FUNCTION [dbo].[ufn_RegExReplace]
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @Replacement NVARCHAR(MAX), @IgnoreCase BIT) 
RETURNS NVARCHAR(MAX)             
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.RegEx].RegExReplace
GO

CREATE FUNCTION [dbo].[ufn_RegExMatches]
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)        
RETURNS TABLE (Match NVARCHAR(MAX), MatchIndex INT, MatchLength INT) 
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.RegEx].RegExMatches
GO

CREATE FUNCTION [dbo].[ufn_RegExSplit]  
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)        
RETURNS TABLE (Match NVARCHAR(MAX))          
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.RegEx].RegExSplit
GO

CREATE FUNCTION [dbo].[ufn_RegExSplitPart]  
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX),@Part int, @IgnoreCase BIT)        
RETURNS NVARCHAR(MAX)         
AS EXTERNAL NAME [IDMTSqlExtLib].[IDMT.ENTAPP.SqlExtLib.RegEx].RegExSplitPart
GO

--exec WebS 'IOWS.asmx','FileMove','fromPath=E:/22.txt&toPath=E:/Study/22.txt'
--exec WebS 'IOWS.asmx','FileCopy','fromPath=E:/22.txt&toPath=E:/Study/22.txt&overWrite=false'
 --exec WebS 'IOWS.asmx','FileDelete','filePath=E:/22.txtneedBak=true' --不能删除
-- exec WebS 'IOWS.asmx','FolderCreate','folderPath=E:/rr'
 --exec WebS 'IOWS.asmx','FolderMove','srcfolderPath=E:/rr&tarfolderPath=E:/Study/rr'
--exec WebS 'IOWS.asmx','FolderDelete','srcfolderPath=E:/rr&needBak=false'

 exec WebS 'RTXWS.asmx','RTXSendTipC','RECVUsers=2266*IMMsg=中国*Title=test*DelayTime=0'
--exec WebS 'RTXWS.asmx','RTXSendTipA','RECVUsers=2266&IMMsg=中国'

regex.replace("||||")