SQLCMD not working when run using Java

Question

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!

Answer 1

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.

Answer 2

I don't have privs to post comments - which is what this is - but what if you try putting in a bogus user id for the DB? Does that cause a different execution path? Will that give you a Java error? Or an Auth error in your DB? Also, def tweak the user, not the password and learn from my experience that if you tweak the password that's a great way to get an account locked out!

The other thing - and this may be a shot in the dark - but what are the JRE and driver you're using? I believe there's a known issue with JRE 1.6.0.29 and the sqljdbc4 JAR. I have more details on this, but I'll have to post the link once I get to work.

Edit:

I know it's been established that the JRE/sqljdbc combo isn't your issue, but if folks search and find this, here is the link I spoke of above:

Driver.getConnection hangs using SQLServer driver and Java 1.6.0_29