Unanswered: Issue with sql server jdbc driver 2005
I have one query which executes properly with mssql 2000 jdbc driver but fails with the 2005 driver.
The query creates a table, using insert exec method tries to get the data in the created table. But, executing this statement fails producing exception com.microsoft.sqlserver.jdbc.SQLServerException: The statement did no
t return a result set.
the command is as:
insert into #temp exec ('dbcc inputbuffer (' + @spid + ')')
I tried executing only the dbcc command on the db with query analyzer, it returns the results correctly, even with the same driver if I execute the dbcc query it runs fine and returns the resultset. But, only when I use it with insert exec it fails.
Does anyone know why this error occurs and what can be work-around.
my apologies for not mentioning that after inserting data into table, I run a select query on the table and after that I delete the table. All of it happens in a single executeQuery call.
Thus, as a result of select query, I should be returned a resultSet.
But, taking cue from your suggestion to user execute, I decided to split the query into three parts, 1. create table; 2. Collect data in the table and 3. Select data and delete the table. I use execute for first two operations and executeQuery for 3rd operation. And I have got it working, I wonder what could be faulty in one big executeQuery operation which works fine with 2000 driver and fails with 2005 driver. I suppose there is a bug in 2005 driver which is unable to partition this call and generate a resultset.