"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

"Sometimes"- Error message

I've a SSIS Package, what executes a stored procedure, who calculates a lot of stuff with a recursive algorithm (TSQL). Sometimes the package aborts with this error message:

"Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The first few german words mean: "Syntax error, Security error or general error"...

If i restart the packages, the error usually does'nt appear again. All releated data are'nt changed in the meantime. I can not reproduce the the error and a think, it depends on the connection (but SSIS and SQL Server are running on the same virtual maschine). By the way, the executed stored procedure does not have any resultset what is given back...

Every idea is very welcome...

Torsten

Since you say there is a lot of complexity in the stored procedure and that it only happens sometimes, I wonder if it isn't something in the procedure causing the problem (a concurrency-induced deadlock, a random number causing a divide by zero, the recursion limit being reached, etc.)

I suggest you try running a profiler trace and see if any errors are getting thrown by the procedure which aren't being reported by SSIS.
|||

Thanks for your message!

Running the profiler reduces the performance in a way, that it would take a fews days...

When talking about recusion limit, what is ment by it? In my recursion i've an hirarchie with not more then 10 levels. the max-recursion option i've set to 500 and increased to 1000. Doesn't matter - i get the error sometimes like before... A division by zero is not possible, i'm only summerizing. A deadlock doesn't make sense, i'm not changing the data while running these process (and no other does).

But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?

"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "

Where is the place to increase the system limit of 64 threads? Sp_configure says following:

name min max config run_value

Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 3 3
affinity mask -2147483648 2147483647 3 3
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 1
allow updates 0 1 0 0
awe enabled 0 1 1 0
blocked process threshold 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1031 1031
default language 0 9999 1 1
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 4000 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 128 32767 128 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 16
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Web Assistant Procedures 0 1 0 0
xp_cmdshell 0 1 0 0

|||

Torsten_Katthoefer wrote:

But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?

"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "

Good grief! You must have a huge Data Flow. You need to break that up into multiple smaller data flows. Increasing the thread limit is not a good solution (even if it is possible). A raw file destination can be placed in a data flow to persist the pipeline to the disk, and then another data flow started with a raw file source to pick up where the other left off. I think you'll find your performance will be much better.
|||

Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.

It would like to increase the thread limit, only to observe these process. Is it possoble to do this?

|||

Torsten_Katthoefer wrote:

Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.

It would like to increase the thread limit, only to observe these process. Is it possoble to do this?

Well then something must be really messed up for SSIS to think it needs 107 threads. I don't know for sure, but I doubt you can raise that limit. If your package so simple, I would delete it and start over. Something in there is not right.