5

I'm trying to setup a SQL Server agent job of type CmdExec for running an executable inside %programdata%\my company folder.

If I use %programdata%\my company\myexec.exe, the job fails because it apparently can't find the file.

But it works with the absolute path:

C:\ProgramData\my company\myexec.exe

How can I run the job successfully using the special folder alias?

Improve this question
0

4 Answers 4

Reset to default
5

SQL Agent doesn't shell out the command like that. You'll need to write a batch file with the system variable in it, then call the batch file from the SQL Agent job. The system variables don't get resolved if they are actually in the agent job step.

Improve this answer
5
  • That's only half true. In some cases, environment variables do get expanded, for example if your job step is 'dir %programfiles%'. But '%programfiles%\some.exe' will fail. I'm not sure I can come up with an explanation for this that makes sense
    – piers7
    Mar 8, 2012 at 7:42
  • 2
    For the reason that I said. The DLL that SQL Agent uses runs dir %programfiles%. The command is dir which is in the path, so the SQL Agent can find it easily enough. At that point it's within the shell and the OS expands %programfiles% to "c:\program files\". %programfiles%\some.exe on the otherhand is the actual executable which when the DLL tries to find it, fails because the DLL can't expand %programfiles% to anything as it doesn't understand variables.
    – mrdenny
    Mar 9, 2012 at 18:37
  • Really. I couldn't find any 'dir' in my path'. Nor any 'echo' either - these two are shell commands surely, not exe's/.coms in the filesystem somewhere. Which makes no sense at all. If they're not exes, to resolve them SQLAgent would have to take the command and pass it INTACT to the shell, at which point environment variable expansion should kick in.
    – piers7
    Mar 12, 2012 at 5:22
  • ...unless SQL Agent has some special cases where it knows how to do a 'dir' and 'echo' and such like (ie forward anything that starts with a shell command to the shell directly). But that seems like a really brittle arrangement (unless there's an API to query to find all the candidates). So I'm still not convinced we have characterised the actual behaviour in a way that makes any sense.
    – piers7
    Mar 12, 2012 at 5:25
  • ...or it's doing the equivilent of CreateProcess, but to do that you have to supply the exe and the arguments seperately, so then it'd be doing it's own cmd-line parsing just to split off the first bit (the process) and leave everything else as arguments. Come to think of it, maybe they use CommandLineToArgvW to do exactly that. But then it'd be up to the receiving process to expand environment parameters in it's arguments, which seems unlikely.
    – piers7
    Mar 12, 2012 at 5:43
2

It will depend on what account you are using for SQL Server Agent service.

If the %ProgramData% is a system variable it should be seen by any account on the server where the instance is running. Where, user variables are local to the user profile it was configured under.

Edit
Good question on SF for adding a system environment variable has some good info on how you would add the %ProgramData% to the server.

Improve this answer
0
2

You can force environment variable expansion by calling cmd.exe /c "mycommand", though you do have to be very careful with your quoting and double quoting. For example, assuming the OP's original job step was (literally, including the quotes)

"%programdata%\my company\myexec.exe"

you can replace this with:

cmd.exe /c ""%programdata%\my company\myexec.exe""

...and it will work.

@Shawn points out that this will only work for system environment variables, or those set for the SQL agent user, but %programdata% should be fine.

Improve this answer
0

Just precede your cmd statement with a CALL. This will resolve the environment variable. Tested on SQL Server 2016

eg

CALL %DTEXEC2016_PATH% /FILE <path to script>
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.