Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm trying to setup a sqlserver agent job of type CmdExec for running an executable inside %programdata%\my company folder. But if I use %programdata%\my company\myexec.exe, the job fails to start because it says it can't find the file. With the absolute path C:\ProgramData\my company\myexec.exe it works. Anybody knows a way to run the job successfully using the special folder alias?

share|improve this question

migrated from stackoverflow.com Jan 20 '12 at 21:17

3 Answers

up vote 2 down vote

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.

share|improve this answer
up vote 2 down vote

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.

share|improve this answer
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 '12 at 7:42
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 '12 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 '12 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 '12 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 '12 at 5:43
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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