Post reply

Sql Server Agent failed to executed a scheduled bat file that contains sqlcmd command

  • October 7, 2010 at 8:59 pm

    #239500

    Hi All:

    I am currently using Windows 2003 R2, Standard Edition.

    I have Microsoft Sql Server 2005 Installed with the latest service pack which is sp3.

    I logged into the operating system as an administrator. The windows administrator id is "test2".

    I logged into the Microsoft Sql Server 2005 database using "Microsoft Sql Server Management Studio" as an "sa" which is database admin.

    I placed a bat file called "test1.bat" in the directory c:\test3, the file "test1.bat" contains the following content:

    sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"

    In the dos-prompt, I can run the "test1.bat" batch file with no errors.

    It gives me the following output:

    c:\test3\sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"

    ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD

    Using "Sql Server Agent", I created a new job called "Scripts1".

    For the "General" link, I have the following values:

    Name:test2

    Owner:SA

    Category:Database Maintenance

    For the step link, I have the following values:

    Step Name:test2

    Type:Operating system(CmdExec)

    Run As:SQL Agent Service Account

    Command:

    sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"

    When, I right click the job "test2" and click "Start Job at Step..", when I view History I get the following error:

    The job failed. The Job was invoked by user DOMAIN\test2. The last step to run was step 1[test2]

    Executed as user. LCA1-B-W-Q-WEB2\SYSTEM. The process could not be created for step 1 of job 0x4EC27C08D25E94(reason: The system cannot find the file specified). The step failed.

    Any help or hint is greatly appreciated.

    Yours,

    Frustrated.

  • October 15, 2010 at 4:02 am

    #1237384

    Why creating a job to run a .BAT which runs a SQL command?

    You can do that in T-SQL job right? Are you trying to run this on Express edition...?

    If you want to run a .BAT file periodically, another option is to create a Windows Scheduled Task.

    Hope this helps you.

  • October 15, 2010 at 6:38 am

    #1237467

    hello,

    change your SQL Agent job step test 2 to this;

    EXEC master..xp_CMDShell '"C:\test3\test1.bat"'

    also change the job type from Operating System (CmdExec) to (T-SQL)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply