Calling stored procedure from web application on UNIX throwing error on xp_cmdshell

Question

Hey all, I'm pulling my hair out on this one.

I've checked all my permissions, on both the database server (SQL Server 2000) and the file system to ensure that what I am trying to do should be possible. Here's the situation:

I have a Java EE web application running on a Tomcat server on my company's intranet. Long story short, this application crunches numbers, creates a pipe-delimited file of records to be BULK INSERTed into our database, and saves the file on the UNIX server.

Then a stored procedure is called which utilizes xp_cmdshell to FTP the file from UNIX to our Windows SQL Server box in order to BULK INSERT the file. When I call this stored procedure (logged in as the tomcat user that we run our web app from) from Management Studio, there is not a single error, and the FTP transfers, and the file is loaded.

This is the part that is aggravating me.

When the Java EE app calls this stored procedure, it actually FTPs the file to the SQL Server box, but I end up with a SQLServerException even upon success. No rhyme or reason. BUT...even though the exception is thrown, my file ends up on the server all nice and cosy.

Does SQL Server have an issue with running xp_cmdshell from a UNIX server (as ludicrous as this sounds to me, since all the program is doing is calling a stored procedure, I am still at the office at 11:55PM on a Tuesday night, so I guess nothing is too ludicrous...)

Any insight would be great...

EDIT:

Also, I have been viewing the traffic to SQL Server using SQL Profiler...and I can see the actual statement I am using to call the stored procedure. When I copy that into SQL Server Management Studio and execute it using my tomcat user credentials it executes flawlessly...

Answer 1

Wow. This is why it is not good to code things at 11PM.

xp_cmdshell returns a result set on every execution. It basically gives you the output from the command line as you would see it at a command prompt. The key here is result set.

The JDBC method I was calling to invoke the stored procedure was issueUpdate(), which was throwing an exception on the returned result set of xp_cmdshell. When I changed to issueQuery(), I did not receive the exception and the rest of my code executed to completion.

I think I have some work to do to clean this up, but I am satisfied with this solution.