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]

Amber (gu**********@gmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Amber]

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.syscategories 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 QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Test',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DBS\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollback
/****** 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_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'E:
cd E:\firefly
copy a.txt b.txt',
@flags=0
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId,
@start_step_id = 1
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollback
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_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070305,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
@server_name = N'(local)'
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
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 0x0AADA959AC29F343ADD373C3DC57A375 (reason: ¾Ü¾ø·ÃÎÊ¡£). The
step failed.
(DBS is my server's name).

[Amber]

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

[Roy Harvey]

On 26 Mar 2007 18:11:09 -0700, "Amber" <gu**********@gmail.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:&cd E:\firefly&copy a.txt b.txt',

Roy Harvey
Beacon Falls, CT

[Amber]

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