使用SQL复制和删除目录中的文件 [英] Using SQL to copy and delete files in directories
问题描述
以下是我的工作经历以及几周来的工作：
Here is what I was given to work with and what has been working for several weeks:
declare @cmdstring varchar(1000)
DECLARE @PATH VARCHAR(256)
DECLARE @FILENAME VARCHAR(500)
SET @PATH = 'dir \\myservername\c$\myFolderName\SubFolderName\*Everything*.xls /A-D /B /O-D'
set @cmdstring = 'DEL "\\myservername\c$\myFolderName\SubFolderName\TheOutFolder\FinalFile.csv'
exec master..xp_cmdshell @cmdstring,no_output
PRINT 'The file has been deleted.'
set @cmdstring = 'copy "\\myservername\c$\myFolderName\SubFolderName\' + @filename + '" "C:\myFolderName\SubFolderName\' + @filename + ' "'
exec master..xp_cmdshell @cmdstring,no_output
Print 'The file has been copied.'
以上作为预定工作每天都在运行。然后，昨天，进程运行但文件未被复制。我检查了与计划作业的此步骤关联的日志文件，并且打印命令位于日志文件中，但未复制或删除任何内容。该工作没有记录任何错误。服务器未记录任何错误。
以下是日志文件：
The above has been running every day as a scheduled job. Then, yesterday, the process ran but the file was not copied. I checked the log file associated with this step of the scheduled job and the print commands were in the log file but nothing was copied or deleted. The job did not log any errors. The server did not log any errors.
Below is the log file:
Job 'Job_Name' : Step 1, 'Import and Process the files' : Began Executing 2016-12-12 00:30:00
dir \\myservername\c$\myFolderName\SubFolderName\*Everything*.xls /A-D /B /O-D [SQLSTATE 01000]
The File has been deleted. [SQLSTATE 01000]
File copied from myservername to myfoldername [SQLSTATE 01000]
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Copy to myservername Processed folder done [SQLSTATE 01000]
Server copy deleted [SQLSTATE 01000]
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Return Value
------------
0
(1 rows(s) affected)
当我尝试手动运行时DEL（正如我过去所做的那样），打印了我的打印语句，但文件不会删除。我检查了我的系统管理员，并且有一些回滚进程阻塞并锁定了一些数据库进程。
然后，两个小时后一切都像往常一样删除和复制。
我的问题是：
1.为什么我的失败复制命令没有记录作业失败（该步骤设置为记录失败并发送电子邮件）。
2.为什么我无法通过SQL从我的目录中删除文件（就像我之前所做的那样神秘地能够删除我的文件并让我的工作成功复制并删除我的文件。
我注意到的一个区别是，当我通过预定作业调用存储过程时，我这样做：执行myStoredProcedureName。
这个作业调用这样的存储过程：
When I tried to manually run the DEL (as I have done in the past), My "print" statement printed but the file would not delete. I checked with my system admin and there were some rollback processes blocking and locking some of the database processes.
Then, everything was deleting and copying as usual two hours later.
My questions are:
1. Why didn't my failed copy command log a job failure (the step is set to log a failure and send an email).
2. Why would I not be able to delete a file from my directory via SQL (like I have done before then mysteriously be able to delete my file and have my job successfully copy and delete my file.
One difference I notice is that when I call a stored procedure via a scheduled job, I do it like this: EXECUTE myStoredProcedureName.
This job calls the stored procedure like this:
USE [MyDatabaseName]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[myStoredProcedure]
SELECT 'Return Value' = @return_value
GO
我尝试过：
我搜索了MSDN的网站和博客，其他留言板。
What I have tried:
I have searched MSDN's website and blogs, other message boards.
推荐答案
\ myFolderName \ SubFolderName \ * Everything * .xls / AD / B / O-D'
set @ cmdstring = ' DEL \\ myservername\c
\myFolderName\SubFolderName\*Everything*.xls /A-D /B /O-D' set @cmdstring = 'DEL "\\myservername\c
\ myFolderName \ SubFolderName \TheOutFolder \FinalFile.csv'
exec master..xp_cmdshell @ cmdstring ，no_output
PRINT ' 该文件已被删除。'
set @cmdstring = ' copy\\myservername \ c
\myFolderName\SubFolderName\TheOutFolder\FinalFile.csv' exec master..xp_cmdshell @cmdstring,no_output PRINT 'The file has been deleted.' set @cmdstring = 'copy "\\myservername\c
\ myFolderName \ SubFolderName \' + @ filename + ' < span class =code-string>C：\ myFolderName \ SubFolderName \' + @ filename + ' '
exec master..xp_cmdshell @ cmdstring ，no_output
打印 ' 该文件已被复制ed。'
\myFolderName\SubFolderName\' + @filename + '" "C:\myFolderName\SubFolderName\' + @filename + ' "' exec master..xp_cmdshell @cmdstring,no_output Print 'The file has been copied.'
以上作为预定工作每天都在运行。然后，昨天，进程运行但文件未被复制。我检查了与计划作业的此步骤关联的日志文件，并且打印命令位于日志文件中，但未复制或删除任何内容。该工作没有记录任何错误。服务器未记录任何错误。
以下是日志文件：
The above has been running every day as a scheduled job. Then, yesterday, the process ran but the file was not copied. I checked the log file associated with this step of the scheduled job and the print commands were in the log file but nothing was copied or deleted. The job did not log any errors. The server did not log any errors.
Below is the log file:
Job 'Job_Name' : Step 1, 'Import and Process the files' : Began Executing 2016-12-12 00:30:00
dir \\myservername\c
这篇关于使用SQL复制和删除目录中的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
