ProcessStartInfo returning The directory name is invalid when invoking from SQL Job

Question

I have a SQL Job that's running on a DB Server and on one of the steps it calls an application server to run an exe "cmd Exec step type"

the exe application "C#" is invoked properly and does all its intended tasks, as a final step it runs cmd.exe to merge couple of files.

** When running the exe application manually, it's working as intended, but when invoking through SQL Job it's returning the below error:

Error in MergeFiles() Method : System.ComponentModel.Win32Exception (0x80004005): The directory name is invalid
   at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
   at System.Diagnostics.Process.Start()
   at System.Diagnostics.Process.Start(ProcessStartInfo startInfo)
   at _700CreditBillingScheduler.Billing700C.MergeFilesInPath()

Worth saying everything is running on E:\ drive

Here is my code:

    string filePath = "E:\\HomeFolder\\ApplicationFolder"; // where merged file will be created
    string sourcePath = "E:\\HomeFolder\\ApplicationFolder\\Transaction.Indiv.Files\\*.csv"; // to be merged
    string commandLine = String.Format("/c copy /b {0} {1}", sourcePath, "mergedFileName");
    var cmd = new ProcessStartInfo("cmd.exe", commandLine);
    cmd.WorkingDirectory = filePath;
    cmd.UseShellExecute = false;
    Process.Start(cmd);

Which dbms are you using? (This isn't really related to the language.) — jarlh, Aug 19 '20 at 18:30
MSSQL, it has to do with the application itself running windows cmd. — Marwah Abdelaal, Aug 19 '20 at 18:31
"it calls an application server to run an exe " Does E:\ exists on this application server and map to the same location? Does it work with a UNC path instead? — Ross Bush, Aug 19 '20 at 18:32
@RossBush Yes, E:\ exists and using UNC. it's invoked properly and running on the app. server. the last step for the application is to merge all files generated using cmd.exe where it fails "The directory name is invalid". — Marwah Abdelaal, Aug 19 '20 at 18:33
Do you really need the `/c` before copy? All the arguments are already part of your commandLine arguments anyhow — Icepickle, Aug 19 '20 at 19:39
@Icepickle The command cmd /c will close the command-prompt window after the exe runs — Marwah Abdelaal, Aug 21 '20 at 15:21

score 0 · Answer 1 · answered Aug 19 '20 at 18:31

0

Try to remove the line where you specify the working dir ?

answered Aug 19 '20 at 18:31

Romka

144
8

score 0 · Answer 2 · answered Aug 21 '20 at 15:26

I found the issue, it has to do with the UNC path. The command was trying to merge the files on DB Server using the UNC path of the file, and UNC isn't supported through cmd.exe, so instead it ran on DB Server where the directory doesn't exist.

I switched to merge the files on application server using the C# application itself.

ProcessStartInfo returning The directory name is invalid when invoking from SQL Job

2 Answers2