Sql Server Agent failed to executed a scheduled ba

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Sql Server Agent failed to executed a scheduled ba

Author

Topic

jadeite100
Starting Member

5 Posts

Posted - 2010-10-07 : 22:48:44

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.

jadeite100
Starting Member

5 Posts

Posted - 2010-10-08 : 13:31:36

Hi All:

I think it is a security issue.
On one of the Sql Server 2005, I can run the bat file through the "Sql Server Agent" and it works.
I logged into "Sql Server Management Studio" as sa. In the "Sql Server Agent",under the "General link" using Owner:computer1\test1. I logged into the box remotely using "computer1\test1" as the id. The computer name is "computer1".

Another Sql Server 2005, under the "General link", I cannot find the owner "computer2\test2" under the "General" link for owner? I logged into "Sql Server Management Studio" as sa.
I logged into the computer remotely "computer2\test2". The computer name is computer2. How can I added the "computer2\test2" under the "General" link for owner?

Any help or hint is greatly appreciated.

Yours,

Frustrated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-08 : 14:39:17

SQL Agent account needs permissions on the batch file, and to perform any actions contained within.

We use a domain account for SQL Agent service account and assign it permissions as appropriate.

Also, why use sqlcmd instead of just making step type of T-SQL and put the index rebuild script there?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources