SQLServer filters out special characters in the file name (using SQLServer to modify the physical address name of the file) and special characters in sqlserver

SQLServer filters out special characters in the file name (using SQLServer to modify the physical address name of the file) and special characters in sqlserver

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) -- change path D: To 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 '-- search for the required attachment open ccur fetch ccur into @ messageid, @ serialid, @ fileName, @ filePath while @ fetch_status = 0 begin -- determines whether the attachment file name contains special characters if CHARINDEX ('+', @ filename)> 0 or CHARINDEX ('$ ', @ filename)> 0 or CHARINDEX ('&', @ filename)> 0 -- contains +, $, & begin set @ oldfileName = @ filename set @ filename = rep Combine (@ filename, '+', '') set @ filename = replace (@ filename, '$','') set @ filename = replace (@ filename, '&', '') -- filter these special characters. After replacement, the new file name is set @ newFilePath = dbo. getDirectoryPath (@ filePath) + '\' + @ filename -- New Path (<span style = "font-family: Arial, Helvetica, sans-serif;"> dbo. the GetDirectoryPath method is used to obtain the file directory according to the path </span> <span style = "font-family: Arial, Helvetica, sans-serif;">) </span> -- change the old path name to the disk drive set @ oldfilePath = replace (@ FilePath, 'd: ', 'd $') set @ oldfilePath = '\ 192.168.21.36 \' + @ oldfilePath -- the attachment is under the remote server. If not, do not add the disk path -- exec master. dbo. xp_{shell '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_mongoshell '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 + ''-- assemble your own commands -- modify the physical location name of the file (connect to the remote server and the drive letter where the file name needs to be modified) exec master. dbo. xp_mongoshell 'net use \ 192.168.21.36 \ d $ "123456"/user: "192.168.21.36 \ administrator" 'exec master. dbo. the length of xp_cmdshell @ cmd -- @ cmd must be set to specific data, not nvarc Har (max) reports the "command _ string 'parameter whose process type is 'varchar'" error -- insert file name modification log table insert into DataFileNameUpdateLog (messageid, oldFileName, newFileName, oldFilePath, newFilePath) values (@ messageid, @ oldfileName, @ filename, @ filePath, @ newFilePath) -- update the new file name and path based on the serialid (serialid is unique) update A set AttachmentName = @ filename, AttachmentPath = @ newFilePath where serialid = @ serialid -- insert A message to me to remind declare @ content nvarchar (max) set @ Content = 'the attachment name of the group communication contains special characters, which have been filtered. Please check them carefully. Serialid: '+ @ serialid +', messageid: '+ @ messageid +', original file name: '+ @ oldfileName +', New File Name: '+ @ filename +' (automatically sent by the System) 'exec aa. dbo. [receivemind] 0, '', 11182, @ content end fetch ccur into @ messageid, @ serialid, @ fileName, @ filePath end close ccur deallocate ccur end

Obtain the file directory dbo. GetDirectoryPath by path

-- =============================================  -- 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  

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.