SQLSEVER2005中找不到存储过程xp_getfiledetails 解决办法

当在sqlserver2005中使用"存储过程xp_getfiledetails "时，会报找不到该存储过程的错误。

原因是该存储过程在sqlserver2000的版本存在，而在2005中已经不存在。用以下方法可以解决该问题，相当于重新写了一个csp_getfiledetails的存储过程替代原来的xp_getfiledetails存储过程。

在C盘下，新建Temp文件夹，放Conchango.SqlServer.SqlClrToolkit.GetFileDetails.dll文件到里面。再在跑下下面的sql脚本：

--I'm creating it in master. You may wish to put it somewhere elseUSE    masterGO--Enable clr on your server-- WITH OVERRIDE forces the change that conflicts with the IO affinity masksp_configure 'clr enabled', 1reconfigure with override--Enable assemblies with PERMISSION_SET=EXTERNAL_ACCESS -- to be cataloged in the databaseALTER DATABASE master SET trustworthy ON--Drop the assembly if it exists alreadyIF    EXISTS (SELECT * FROM sys.procedures WHERE name = 'csp_getfiledetails')    DROP PROCEDURE csp_getfiledetailsGO--Drop the procedure if it exists alreadyIF    EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Conchango.SqlServer.SqlClrToolkit.GetFileDetails')    DROP ASSEMBLY [Conchango.SqlServer.SqlClrToolkit.GetFileDetails]GO--Catalog the assembly with PERMISSION_SET=EXTERNAL_ACCESS.-- This permission set is required because the sproc accesses an external resource (i.e. a file)-- Script assumes you have saved the assembly to c:\temp, local to SQL ServerCREATE    ASSEMBLY [Conchango.SqlServer.SqlClrToolkit.GetFileDetails]FROM    'C:\Temp\Conchango.SqlServer.SqlClrToolkit.GetFileDetails.dll'WITH    PERMISSION_SET = EXTERNAL_ACCESSGO--Have a look at the assembly within the database-- At this stage the assembly can be deleted from the file systemSELECT * FROM sys.assembliesSELECT * FROM sys.assembly_filesGO--Create our procedure from the assemblyCREATE    PROCEDURE dbo.csp_getfiledetails( @pFileName nvarchar(4000) )AS    EXTERNAL NAME [Conchango.SqlServer.SqlClrToolkit.GetFileDetails].[Conchango.SqlServer.SqlClrToolkit.GetFileDetails].csp_getfiledetails-------------------------------------Run it!!!!!csp_getfiledetails 'c:\boot.ini'

----经过测试，正常可用。2011-07-14