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!

@RogerLindsjö Okay so I tried getting the ErrorStream, and it shows the error "Sqlcmd: Error: Error occurred while opening or operating on file 2> (Reason: The filename, directory name, or volume label syntax is incorrect" — GPX
– GPX, Commented Dec 7, 2011 at 11:31
Do you have to redirect the streams? It should be the job of cmd to handle parsing of those. However, can you make it work if you skip the redirections completely and handle the stream form Java? — Roger Lindsjö
– Roger Lindsjö, Commented Dec 7, 2011 at 11:39
I do not want to do anything with the streams using Java. So I decided to let SQLCMD handle it. Now that this issue cropped up, I tapped into the ErrorStream to take a look at it. Also, take a look at my second update. — GPX
– GPX, Commented Dec 7, 2011 at 11:44

H-Man2 · Accepted Answer · 2011-12-07 11:37:24Z

3

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.

edited Dec 7, 2011 at 11:37

answered Dec 7, 2011 at 11:31

H-Man2

3,18921 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

GPX Over a year ago

Wow, this is exciting to learn so many things about Runtime! So I tried passing the entire command as a single-element array, like Runtime.getRuntime().exec(new String[] {strCmd});. Now the ErrorStream is empty, but the tables are still not getting created!

H-Man2 Over a year ago

Did you tried to pass an array with three elements? 0. The command cmd 1. First argument to cmd: /c 2. Second argument to cmd: "sqlcmd -m ..."

GPX Over a year ago

Okay so I tried that. I'm getting the error "Access is denied." in the ErrorStream. Could sqlcmd be throwing this error?

H-Man2 Over a year ago

Hm, try only the sqlcmd part and ignore for now the log files. It seems like sqlcmd returns error messages with the prefix "sqlcmd:".

GPX Over a year ago

THAT WORKS! I passed sqlcmd -m 11 -S SERVER -d DB -U USER -P PASS -i "SCRIPT.sql" and it works! But logging is extremely essentially for my purpose =[

|

Community · Accepted Answer · 2017-05-23 11:48:47Z

1

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

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Dec 7, 2011 at 11:25

Matt Felzani

80511 silver badges25 bronze badges

1 Comment

GPX Over a year ago

Matt, I'm using JRE 1.6.0.03 and the sqljdbc4 JAR. Also do check my second update!

harsh · Accepted Answer · 2011-12-07 11:37:55Z

0

First enable log/view commands output (since exec() returns 1), which would point out possible cause of the issue. Use proc.getInputStream() and print the contents to a file or console.

answered Dec 7, 2011 at 11:37

harsh

7,7023 gold badges33 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQLCMD not working when run using Java

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related