Click here to Skip to main content
15,068,646 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Here is what I was given to work with and what has been working for several weeks:

SQL
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:

SQL
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:
SQL
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.
Posted
Updated 9-Apr-19 20:52pm
Comments
Richard Deeming 13-Dec-16 14:19pm
   
This is the sort of thing that SQL was never intended to do. If you're running it as a SQL Agent job, you'd do better to create a batch file or Powershell script, and execute that as an Operating System (CmdExec)[^] step.
Member 10379103 13-Dec-16 14:34pm
   
Richard,
Thanks for the suggestion. I'm not used to relying on SQL for these sorts of actions, either. This was the path that had already been set when I got here.
I will research further your suggestion. There is another suggestion posted to my question that seems quite similar. Thanks!

I can't say why the copy or delete failed, but I would suggest not using xp_cmdshell to run OS commands directly.

The most important reason is that while you can execute commands you have no proper tools to log or handle errors.

If you really need to use xp_cmdshell I would suggest at least running powershell scripts and adding error handling and logging to the script. In my opinion a better way would be to create a CLR procedure which you could call from the database. Inside this procedure you could utilize (almost) all the possibilities .Net offers as a framework.

If interested, have a look at for example Writing into a file from database[^]. The example is not exactly what you need but the procedure has similar file operations as you have in your commands.
   
SQL
--For copy 

Exec xp_cmdshell 'copy "D:\OldLocationfolder\abc.txt" "E:\NewLocationfolder\"'

-- copy abc.txt file from D drive to E drive

--================================================

--For delete 

Exec xp_cmdshell 'del "D:\myfolder\abc.txt"' 

-- delete abc.txt file from D drive.
   
Comments
CHill60 10-Apr-19 6:10am
   
The OP knows how to do that already - see the detail in their post. The question was why it would suddenly stop failing and this code does not even attempt to answer that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900