Using SQL to copy and delete files in directories

See more:

SQL

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)

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

What I have tried:

I have searched MSDN's website and blogs, other message boards.