Results 1 to 2 of 2

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

  1. #1
    Join Date
    Aug 2009
    Posts
    5

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

    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
    Categoryatabase 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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Means sql agent doesn't know where's sqlcmd, add full path of it in the command. By the way, why don't just run 'alter index' in job step?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •