SQLAgent 2005 SP1 failed to run .bat file.

[Amber]

Following is our test steps.
1. Create directory E:\firefly in E:.
2. Create a test file a.txt in E:\firefly.
3. Create E:\firefly\test .bat file with the following commands:
cd E:\firefly
copy a.txt b.txt
4. Create a new job in SQL Server Management Studio with only one
step, this only step's command
type is set to "Operating system(CmdExec) ", and click the "Open"
button to choose E:\firefly\test .bat
file, then the commands in test.bat are copyed to the command editor.
5. Save the job and run it, Management Studio tells the job faild, but
did not tell an error message.

If I only use the following command :copy E:\firefl\a.txt E\firefly
\b.txt, and SQLAgent can
run the command successfully via the job.

The real functionality of our job is to backup database to a file,
then use our source control tool's
command line tool to submit the backup file to the server.

[Erland Sommarskog]

Re: SQLAgent 2005 SP1 failed to run .bat file.

Amber (guxiaobo1982@g mail.com) writes:

Following is our test steps.
1. Create directory E:\firefly in E:.
2. Create a test file a.txt in E:\firefly.
3. Create E:\firefly\test .bat file with the following commands:
cd E:\firefly
copy a.txt b.txt

Where is SQL Server installed? If it's installed on C:, the CD command
which change the default directory on the E drive, but it will not
change the drive. You need to add this line to the file:

E:

5. Save the job and run it, Management Studio tells the job faild, but
did not tell an error message.

Find the job under SQL Server Agent, right-click and select View History.
For each job execution there is a cross, click on this to expand the
job steps. The read the output for the individual steps.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Amber]

Re: SQLAgent 2005 SP1 failed to run .bat file.

Thanks a lot, but it still fails, my test job's ddl script is as
following:

USE [msdb]
GO
/****** Object: Job [Test] Script Date: 03/27/2007 09:03:48
******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script
Date: 03/27/2007 09:03:48 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscat egories WHERE
name=N'[Uncategorized (Local)]' AND category_class= 1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add _category @class=N'JOB',
@type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add _job @job_name=N'Tes t',
@enabled=1,
@notify_level_e ventlog=0,
@notify_level_e mail=0,
@notify_level_n etsend=0,
@notify_level_p age=0,
@delete_level=0 ,
@description=N' No description available.',
@category_name= N'[Uncategorized (Local)]',
@owner_login_na me=N'DBS\Admini strator', @job_id = @jobId OUTPUT
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
/****** Object: Step [1] Script Date: 03/27/2007 09:03:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add _jobstep @job_id=@jobId,
@step_name=N'1' ,
@step_id=1,
@cmdexec_succes s_code=0,
@on_success_act ion=1,
@on_success_ste p_id=0,
@on_fail_action =2,
@on_fail_step_i d=0,
@retry_attempts =0,
@retry_interval =0,
@os_run_priorit y=0, @subsystem=N'Cm dExec',
@command=N'E:
cd E:\firefly
copy a.txt b.txt',
@flags=0
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXEC @ReturnCode = msdb.dbo.sp_upd ate_job @job_id = @jobId,
@start_step_id = 1
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXEC @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id=@jobId,
@name=N'Back up Firmbank',
@enabled=1,
@freq_type=4,
@freq_interval= 1,
@freq_subday_ty pe=8,
@freq_subday_in terval=1,
@freq_relative_ interval=0,
@freq_recurrenc e_factor=0,
@active_start_d ate=20070305,
@active_end_dat e=99991231,
@active_start_t ime=0,
@active_end_tim e=235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXEC @ReturnCode = msdb.dbo.sp_add _jobserver @job_id = @jobId,
@server_name = N'(local)'
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollbac k:
IF (@@TRANCOUNT 0) ROLLBACK TRANSACTION
EndSave:

And the error message is :
Executed as user: DBS\SYSTEM. The process could not be created for
step 1 of job 0x0AADA959AC29F 343ADD373C3DC57 A375 (reason: ¾Ü¾ø·ÃÎÊ¡£). The
step failed.
(DBS is my server's name).

[Amber]

Re: SQLAgent 2005 SP1 failed to run .bat file.

And my SQL Server was installed on D:\Program Files\Microsoft SQL
Server

[Roy Harvey]

Re: SQLAgent 2005 SP1 failed to run .bat file.

On 26 Mar 2007 18:11:09 -0700, "Amber" <guxiaobo1982@g mail.comwrote:

> @command=N'E:
>cd E:\firefly
>copy a.txt b.txt',

If these were in a bat file and you executed the bat file that should
work.

However, you are trying to execute all three commands as one, and I
believe that is the problem. You can only execute one command.

In some versions of Windows there is an option for stacking multiple
commands on a single line. I have not used it, but you might explore
using &, or &&. If that works @command would look like

@command=N'E:&c d E:\firefly&copy a.txt b.txt',

Roy Harvey
Beacon Falls, CT

[Amber]

Re: SQLAgent 2005 SP1 failed to run .bat file.

I put all these command in c:\test.bat, and in SQLAgent job step I use
this OS command "C:\test.ba t", it works.
Thrank you all.
Amber