FileRename.BAT won't run when using SQL Agent cmdexec (SQL Server 2008 R2)

Hi guys,

I have a .BAT file that renames all files numerically to a predefined name.  I am trying to schedule this via SQL Agent job.  I have verified that the folder that contains this .BAT is executable by my Domain User, and the SQL Agent job is using a Proxy that is my Domain User. Though I can go directly via Windows Explorer and double-click the .BAT to run, I cannot get it to run via SQL Agent job.  I have also used the service account that the SQL Agent job runs under and used that as the default user in the Step Run As; this does not work either.  I can't figure out why something so simple doesn't work.  The only thing that I know is that the file sits on a different D drive, but I have also moved it to C drive and it won't work either.  Please help!!  The SQL Agent job runs successfully with no errors, but the .BAT file does not execute.

echo %0 - %date% %time%  >> D:\Interfaces\Test\rename.log
setlocal EnableDelayedExpansion
set i=0
for %%a in (MyFile*.txt) do (
    set /a i+=1
    copy "D:\Interfaces\Test\%%a" "D:\Interfaces\Test\Archive\%%a" >> D:\Interfaces\Test\rename.log  2>&1
    ren "D:\Interfaces\Test\%%a" "FILENUM!i!.tmp"
)
gometangAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
So, you are sure that is not run or does it run, but not work?

btw, For %%a In (MyFile*.txt) runs your loop in the current working directory. I guess you need this:

@Echo Off

SetLocal EnableDelayedExpansion

Set BASE_PATH=C:\Temp
Set i=0

Echo %0 - %date% %time%  >> "%BASE_PATH%\rename.log"
For %%a In ("%BASE_PATH%\*.cmd") Do (
	Set /A i+=1
	Copy "%BASE_PATH%\%%a" "%BASE_PATH%\%%a" >> "%BASE_PATH%\rename.log"  2>&1
	Echo "%BASE_PATH%\%%a" "FILENUM!i!.tmp"
) 
 
EndLocal

Open in new window

0
 
gometangAuthor Commented:
Awesome; though your script doesn't actually work in terms of the variables I had, I did as you told me and made it pushd to that directory:

@echo off
setlocal EnableDelayedExpansion

pushd D:\MyFolder

set i=0
for %%a in ("*.txt") do (
    set /a i+=1
    copy "%%a" ".\Archive\%%a"
    ren "%%a" "FILERENAME!i!.tmp"
)
0
 
ste5anSenior DeveloperCommented:
Or use Set BASE_PATH=D:\Interfaces\Test.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.