Run Batch Files with SQL Server Agent

Hi,
I am trying to run a batch file using the SQL Server Agent
(I have scheduled a job). I have chosen the step type to
be 'Operating System Command (CmdExec)'. But I am not sure
how to code the actual command.
The batch file is located at: e:\sqlbackups\delete.bat.
How do specify the Agent to call this batch file?
Thanks,
DeeJayHi,
In SQL Agent Jobs -- Step option, after selecting 'Operating System Command
(CmdExec)' , In the command window you can type
e:\sqlbackups\delete.bat
(This will call the batch file delete.bat based on the schedule interval)
Thanks
Hari
MCDBA
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:14f3201c3fa33$599d4120$a001280a@.phx
.gbl...
> Hi,
> I am trying to run a batch file using the SQL Server Agent
> (I have scheduled a job). I have chosen the step type to
> be 'Operating System Command (CmdExec)'. But I am not sure
> how to code the actual command.
> The batch file is located at: e:\sqlbackups\delete.bat.
> How do specify the Agent to call this batch file?
> Thanks,
> DeeJay
>|||Hi
Thanks, but that's exactly how I have been trying to run
it, but it does not work.
I am running the job with an administrative account that
has the required permissions.
I have tested the script by running is manually on the
server and it works fine.
I wonder what gives.
DeeJay
>--Original Message--
>Hi,
>In SQL Agent Jobs -- Step option, after
selecting 'Operating System Command
>(CmdExec)' , In the command window you can type
>e:\sqlbackups\delete.bat
>(This will call the batch file delete.bat based on the
schedule interval)
>Thanks
>Hari
>MCDBA
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:14f3201c3fa33$599d4120$a001280a@.phx
.gbl...
Agent
sure
>
>.
>|||Do you get any error message, what happens when you try to
run the job ?
How long the batch file takes to run ? We had an issue
where sql agent timed out if the batch file took more than
30 minutes to run. It happened only once and we ran the
file manually from command prompt. You can also try to run
it as a tsql command using Exec
master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
hth.

>--Original Message--
>Hi
>Thanks, but that's exactly how I have been trying to run
>it, but it does not work.
>I am running the job with an administrative account that
>has the required permissions.
>I have tested the script by running is manually on the
>server and it works fine.
>I wonder what gives.
>DeeJay
>selecting 'Operating System Command
>schedule interval)
>Agent
to
>sure
>.
>|||That's the weird part...I do not get any errors. Actually
according to the agent the job completes successfully, but
the files are not deleted.
I tried your suggestion and it did not work. But this time
I do get an error:
The process could not be created for step 1 of job
0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped I/O
operation is in progress). The step failed.
This is really confusing. I do not want to run the
manually every other day.
Thanks,
DeeJay

>--Original Message--
>Do you get any error message, what happens when you try
to
>run the job ?
>How long the batch file takes to run ? We had an issue
>where sql agent timed out if the batch file took more
than
>30 minutes to run. It happened only once and we ran the
>file manually from command prompt. You can also try to
run
>it as a tsql command using Exec
>master.dbo.xp_cmdshell 'e:\sqlbackups\delete.bat'
>hth.
>
>to
e:\sqlbackups\delete.bat.
>.
>|||For real time error info, try to execute xp_cmdshell from
query analyzer.
I wonder if it is a permission issue, if the job owner is
a sysadmin then then sql server will use service startup
account to execute xp_cmdshell, otherwise it will use a
proxy account. Does sql startup account has permissions to
delete those files ?
hth.

>--Original Message--
>That's the weird part...I do not get any errors. Actually
>according to the agent the job completes successfully,
but
>the files are not deleted.
>I tried your suggestion and it did not work. But this
time
>I do get an error:
>The process could not be created for step 1 of job
>0xC38683087ECAA14AB6614A1A74F27555 (reason: Overlapped
I/O
>operation is in progress). The step failed.
>This is really confusing. I do not want to run the
>manually every other day.
>Thanks,
>DeeJay
>
>to
>than
>run
run
that
type
not
>e:\sqlbackups\delete.bat.
>.
>|||I tested running the job with me as the owner and I am a
local administrator on this server. I have also tried
using 'sa' and the service startup and nothing is working.
I have tried running the xp_cmdshell procedure in QA and
it also does not work...I just get the output as 'NULL'.
I wonder if it is ONLY using the proxy account. That
account may not have the required permissions.
I will check that.
DeeJay
>--Original Message--
>For real time error info, try to execute xp_cmdshell from
>query analyzer.
>I wonder if it is a permission issue, if the job owner is
>a sysadmin then then sql server will use service startup
>account to execute xp_cmdshell, otherwise it will use a
>proxy account. Does sql startup account has permissions
to
>delete those files ?
>hth.
>
Actually
>but
>time
>I/O
>run
>that
the
Server
>type
>not
>.
>|||Hi,
I forgot that SQL Server 2000 does not have this proxy
account on the local server. I did find a domain
account 'SQLServerAcctCmdExec' and I added this to the
local administrators account and it still did not work.
DeeJay
>--Original Message--
>I tested running the job with me as the owner and I am a
>local administrator on this server. I have also tried
>using 'sa' and the service startup and nothing is working.
>I have tried running the xp_cmdshell procedure in QA and
>it also does not work...I just get the output as 'NULL'.
>I wonder if it is ONLY using the proxy account. That
>account may not have the required permissions.
>I will check that.
>DeeJay
from
is
>to
>Actually
try
the
the
>the
>Server
>.
>|||Make a little change to the batch file and add folloiwng
line at different places:
@.echo In the batch file at line <#>
when you run the batch file in QA it should display these
messages in results window.
In Enterprise Manager, if you open properties of SQL
Server Agent, click on Job System, you will see option to
specify proxy account, use a local admin for it, and then
restart sql services.
hope this helps.

>--Original Message--
>Hi,
>I forgot that SQL Server 2000 does not have this proxy
>account on the local server. I did find a domain
>account 'SQLServerAcctCmdExec' and I added this to the
>local administrators account and it still did not work.
>DeeJay
working.
>from
>is
startup
>try
issue
>the
to
>the
message
>.
>|||I made the change and the script did not return all of the
expected lines. The scripts is doing a 'for' loop so that
could be the reason for not returning all of the lines.
I tried to enter the proxy account, but I am getting
errors:
'Error executing extended stored procedure: Specified User
can not login'.
I am connected to SQL Server with 'sa' and I tried the
local administrator account (will it be a problem if the
local administrator account has been renamed?) and I also
tried with my own domain ID (sysadmin on sql server) and
same results. I did not think I would need change the
account since 'only sysadmins' can run cmdshell jobs and I
have been trying to run the jobs/commands with sysadmin
rights.
Here is the actual script which works fine manually on the
server:
@.echo off
setlocal
:: *** Root path of the directory where the files are to
be deleted:
set
Root=E:\sqlbackups\archtlog\CopyArchdTlo
gBackups\kdirect
:: *** Number of files to keep:
set Keep=590
if not exist "%Root%" goto :err_Root
for /f "skip=%Keep% tokens=*" %%a in ('dir /b /a:-d /o:-
d "%Root%" 2^>NUL') do (
echo Deleting %%a ...
:: *** Remove the "echo" in the next line to 'arm' the
script:
del "%Root%\%%a"
)
goto leave
:err_Root
echo The directory "%Root%" does not exist.
:leave
This is really bugging me!
DeeJay

>--Original Message--
>Make a little change to the batch file and add folloiwng
>line at different places:
>@.echo In the batch file at line <#>
>when you run the batch file in QA it should display these
>messages in results window.
>In Enterprise Manager, if you open properties of SQL
>Server Agent, click on Job System, you will see option to
>specify proxy account, use a local admin for it, and then
>restart sql services.
>hope this helps.
>
a
>working.
and
as 'NULL'.
>startup
a
permissions
successfully,
Overlapped
>issue
more
>to
to
>message
am
>.
>