This is an extremely strange situation, but I just cannot point out what I'm doing wrong.
I'm executing a big bunch of SQL scripts (table creation scripts, mostly). They are executed through Java, using sqlcmd. Here's the sqlcmd command I use.
sqlcmd -m 11 -S SERVER -d DB -U USER -P PASS -r0 -i "SCRIPT.sql" 2> "ERRORS.log" 1> NULL
Note: I use the -r0 and redirects to make sure only errors go into the log file. I chuck out all STDOUTs.
Now I execute this command in Java, using getRuntime.exec(), like this.
Runtime.getRuntime().gc();
strCmd = "cmd /c sqlcmd -m 11 -S SERVER -d DB -U USER -P PASS -r0 -i \"SCRIPT.sql\" 2> \"ERRORS.log\" 1> NULL"
Process proc = Runtime.getRuntime().exec(strCmd);
proc.waitFor();
Note: I use cmd /c, so that the command runs in its own shell and exits gracefully. Also, this helps in immediately reading the error log to look for errors.
The Problem!
This command works perfectly when run by hand on the command prompt (i.e. the tables are getting created as intended). However, when executed through Java as shown, the scripts are run, and and there are no errors, no exceptions, nothing in the logs. But, when checking in SSMS, the tables aren't there!
Where do I even begin debugging this issue?
UPDATE: I'M A MORON
The return value from the getRuntime().exec method is 1. It should be 0, which denotes normal execution.
Any pointers on how to fix this?
UPDATE 2
I've looked at the process' ErrorStream, and this is what it has.
Sqlcmd: Error: Error occurred while opening or operating on file 2> (Reason: The filename, directory name, or volume label syntax is incorrect).
Looks like the path I'm passing is wrong. The error log goes into my profile directory, which is C:\Documents and Settings\my_username. Do the spaces in the path matter? I'm anyways double-quoting them!
Have a look at the exec method with an string array as parameter:
java.lang.Runtime.exec(String[] cmdArray)
The JavaDoc for this method says:
Executes the specified command and arguments in a separate process.
So, the first item in the array is the command and all of your arguments are appended to the array, e. g.,
Runtime.getRuntime().exec(new String[] {"cmd", "/c", "sqlcmd ... "});
After looking at your comment and the implementation of exec(String) it seems to be, that the exec method recognizes the pipe operator > as an argument to cmd, because exec(String) splits the command string to an array using whitespaces as seperators.