declare @cmd nvarchar(2000) declare @messageid nvarchar(200) declare @serialid nvarchar(200) declare @oldFileName nvarchar(500) declare @fileName nvarchar(500) declare @filePath nvarchar(4000) declare @oldfilePath nvarchar(4000) --将路径D：改成D$ declare @newFilePath nvarchar(4000) declare ccur cursor for select d.MessageId,m.serialid,m.AttachmentName,m.AttachmentPath from d left join r on d.MessageID=r.MessageID left join DataAttachment m on m.MessageID=d.MessageID where d.messageid='0.abe3b3d58161ccb3de7ab63754bb3f15' --通过数据库查找所需要的附件 open ccur fetch ccur into @messageid,@serialid,@fileName,@filePath while @@fetch_status=0 begin --判断附件文件名中是否含有特殊字符 if CHARINDEX('+',@filename)>0 or CHARINDEX('$',@filename)>0 or CHARINDEX('&',@filename)>0 --含有+、$、& begin set @[email protected] set @filename=replace(@filename,'+','') set @filename=replace(@filename,'$','') set @filename=replace(@filename,'&','') --过滤这些特殊字符，替换后新的文件名 set @newFilePath=dbo.GetDirectoryPath(@filePath) + '\' + @filename --新路径名(<span style="font-family: Arial, Helvetica, sans-serif;">dbo.GetDirectoryPath方法为根据路径获取文件目录</span><span style="font-family: Arial, Helvetica, sans-serif;">)</span> --旧路径名更改成磁盘驱动 set @oldfilePath=replace(@filePath,'D:','D$') set @oldfilePath='\\192.168.21.36\' + @oldfilePath --附件在远程服务器下面，如果不是远程，不用加磁盘路径 -- exec master.dbo.xp_cmdshell 'net use \\<span style="font-family: Arial, Helvetica, sans-serif;">192.168.21.36</span><span style="font-family: Arial, Helvetica, sans-serif;">\d$ "123456" /user:"192.168.21.36\administrator"'</span> --exec master.dbo.xp_cmdshell 'ren \\<span style="font-family: Arial, Helvetica, sans-serif;">192.168.21.36</span><span style="font-family: Arial, Helvetica, sans-serif;">\d$\Book1.xls,Book2.xls'</span> set @cmd='ren ' + @oldfilePath + ',' + @filename + '' --拼凑需要自行的命令 --修改文件物理位置名称(连接远程服务器及其需要修改文件名所在的盘符) exec master.dbo.xp_cmdshell 'net use \\192.168.21.36\d$ "123456" /user:"192.168.21.36\administrator"' exec master.dbo.xp_cmdshell @cmd [email protected]，不能设置为nvarchar(max)，会报“过程需要类型为 'varchar' 的参数 'command_string'”错误 --插入文件名修改日志表 insert into DataFileNameUpdateLog(messageid,oldFileName,newFileName,oldFilePath,newFilePath) values(@messageid,@oldfileName,@filename,@filePath,@newFilePath) --根据serialid更新新文件名和新路径(serialid为唯一性) update A set [email protected],[email protected] where [email protected] --给我插入消息提醒 declare @content nvarchar(max) set @content='集团来文附件名含有特殊符号，已过滤，请注意查看。serialid:' + @serialid + ',messageid:' + @messageid + ' ,原文件名:' + @oldfileName + ',新文件名:' + @filename + '(系统自动发送)' exec aa.dbo.[aaRemind] 0,'',11182,@content end fetch ccur into @messageid,@serialid,@fileName,@filePath end close ccur deallocate ccur end根据路径获取文件目录dbo.GetDirectoryPath方法
-- ============================================= -- Author: Paul Griffin -- Create date: 18 January 2015 -- Description: Returns the path without the file name -- from a full path: -- D:\Temp\Resources\Images\My.Picture.jpg -- ==> D:\Temp\Resources\Images -- ============================================= CREATE FUNCTION [dbo].[GetDirectoryPath] ( @Path NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @FileName NVARCHAR(MAX) DECLARE @ReversedPath NVARCHAR(MAX) DECLARE @PathLength INT SET @ReversedPath = REVERSE(@Path) SELECT @PathLength = CHARINDEX('\', @ReversedPath) SELECT @FileName = LEFT(@Path, LEN(@Path) - @PathLength) RETURN @FileName END 版权声明：本文为博主原创文章，未经博主允许不得转载。