October 8, 2010 at 7:49 am
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 8, 2010 at 9:10 am
Check if the SQL Agent service account has privileges on the batch file's folder. Check if the sqlcmd's folder is present in the Server's environment variable. If not try using the absolute path while calling sqlcmd.exe
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 8, 2010 at 11:26 am
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.
October 8, 2010 at 2:30 pm
You can manually type the owner name into the Owner textbox or use the ... button to browse and select it
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply