Solved

SQL Job that calls a windows batch file cannot start

Posted on 2009-05-08
2
959 Views
Last Modified: 2012-05-06
Hi Experts,

I have just created a SQL job, which will do 3 steps:
 - step 1, call a batch file to ftp a text file
 - step 2, import that text file into a table by using a DTS
 - sync the table to other servers

I created the step 1 batch file, run perfectly on command prompt.  Then I created a SQL job, make the first step to run, but it couldn't go.  The error return is:

The job failed.  The Job was invoked by User sa.  The last step to run was step 1 (Name of Step1).  The job was requested to start at step 1 (Name of Step1).

When I show the step details, this is what I got:

Executed as user: Server\UserAccount. The step did not generate any output.  Process Exit Code 1.  The step failed.

I couldn't figure out what this job cannot be started, I made sure that the "owner" is sa, but still no help.  Tried digging around the forums, some said that it might be permission issue... does anyone has an idea of why I couldn't get it start?  thanks very much.




0
Comment
Question by:wingkchan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
mfreuden earned 500 total points
ID: 24335835
Try changing the useraccount that the SQL Agent process runs under to a Domain account with Local Admin access on the SQL server.
0
 
LVL 2

Author Closing Comment

by:wingkchan
ID: 31579415
Mfreuden, thanks for your advice.  From the information I gathered on Google, I thought it would be the permission problem that cause the failure.  I tried changing the "owner" under the Job's property page; tried assigning full control of the folder where the batch file existed... but then, at last, I realized that it was the batch's file's syntax error.  Although the batch file completed flawlessly on Command Prompt, but I guess some minor synatx errors are capable of completing there... as when the batch file is ran by SQL Job calling, it refused.  I found this out, but removing the "@echo off" line, and make a output of the job to a logfile... at then, I see that the batch hangs on a del statment.  Before this, application log, job history didn't tell me this... so kinna stumped me there.  thank you for your input.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question