I am trying to execute a simple command via a job. The job step is of type "Operating System". The following is the command:
sqlcmd -Q "select * from sys.databases" -E -S <ServerName> -o %SQLJobRoot%\test3.txt
%SQLJobRoot% is defined on my server as a directory. I can execute this fine via XP_CMDSHELL. SQL Service and SQL Agent are running with the same account. The following works fine via a job: "DIR > %SQLJobRoot%\test3.txt"
Yet when I try to run the SQLCMD I get the error:
Executed as user: <DOMAIN\Account>. Sqlcmd: Error: Error occurred while opening or operating on file %SQLJobRoot%\test3.txt (Reason: The system cannot find the path specified). Process Exit Code 1. The step failed.
I am not looking for a workaround because we already have one but I just want to know why is it not possible to use environment variables in this case?
View Complete Post