"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.

"Select top (n) from <table>" missing from Management Studio

In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?

TIA,

Barkingdog

No, its definitely not there, you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

>>you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.
>>

What "item" are you referring to? The only way I can see to do this is open a new Query Editor pane and type the sql directly.

TIA,

Barkingdog

|||If you open a table you will have an icon with "SQL" written on it, you can change to the SQL Statement whcih was executed to have the data displayed, which is normally SELECT * From SomeTable and can change it the way you want to query the data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

"Not In" results set error

I've got two tables with email addresses. I'm trying to return the result set from one when the email address is not contained in the other. Here's my select statement

SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master)
Returns 0 rows

Here are the results from the individual select statements for each table:

select NUSOID from lists_master where email ='jon@.lackeydesign.com'
Returns 0 rows

select NUOSID from itsj_pivot where email ='jon@.lackeydesign.com'
1-8174

How can this be? The email is in the ITSJ_Pivot table and not in Lists_Master but the "not in" statement does not return the rows. Please help. Thanks.

Most likely this query:

SELECT email FROM lists_master

Will return a null value for one of the rows.

If so, then IN will always evaluate to either TRUE (it is in there) or UNKNOWN. So either it is NOT(TRUE) or it is NOT (UNKNOWN) which results in FALSE or UNKNOWN respectively:

create table lists_master
( --demo only, should have PKEY
email varchar(200) NULL
)

insert into lists_master
values (NULL)

insert into lists_master
values ('email@.address.com')
go

create table itsj_pivot
( --demo only, should have PKEY
email varchar(200) NOT NULL
)
insert into itsj_pivot
values ('email@.address.com')
insert into itsj_pivot
values ('anotheremail@.address.com')
go
SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master)

No rows

SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master where email is not null)

1 row:

email
-
anotheremail@.address.com

The best way to write this is:

SELECT *
FROM ITSJ_Pivot
WHERE not exists (SELECT email
FROM lists_master
WHERE lists_master.email = ITSJ_Pivot.email)

In this case the NULL isn't a problem because EXISTS looks for the existance of a joined row, so the NULL condition is handled down in the WHERE of the subquery rather than in the comparison operator IN (working with NOT() logic and NULL comparisons can be really annoying.

|||Better to do a not exists. For one, it is generally quicker as you are creating a join between the two tables. For further explanation, see my blog below:|||Both NOT IN and NOT EXISTS are flattened to joins. In fact, in lot of cases you will get the same execution plan. It is just that the semantics for the constructs are different and how the checks are performed. Using EXISTS for existence/non-existence check is always the best approach.|||

SELECT p.* FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NULL

I prefer the join syntax. For me it is more clear.

|||That worked. Thanks for the help.|||

The problem with the join syntax is a couple of things:

1. You have both columns in the output:

SELECT * FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NULL

Change to select p.* (or expand the columns, which is best practice) and this isn't a concernt.

2. What about the opposite question, show me all of the ITSJ_Pivot rows that do have an email address? Now you have to include distinct to get unique results:

SELECT DISTINCT p.* FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NOT NULL

You can use a join, but what if you don't need to data in the lists_master table in the results? This can be more costly to execute (especially if indexes cannot be chosen to make the operation easy to execute)

3. The most important thing to me is that the JOIN syntax doesn't ask the question in a clear manner. IN is the clearest:

Give me ITSJ_Pivot rows where the email address is in the lists_master table

though it only works when you have no nulls and you are working with one column. EXISTS expresses what you are looking for:

Give me ITSJ_Pivot rows where there exists a email address match in the lists_master table.

This is even more important when you are not just working with two tables. The exists condition works if you have a 30 table join or just 2. The join introduces extraneous data or NULLs that can be confusing.

"Not In" results set error

I've got two tables with email addresses. I'm trying to return the result set from one when the email address is not contained in the other. Here's my select statement

SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master)
Returns 0 rows

Here are the results from the individual select statements for each table:

select NUSOID from lists_master where email ='jon@.lackeydesign.com'
Returns 0 rows

select NUOSID from itsj_pivot where email ='jon@.lackeydesign.com'
1-8174

How can this be? The email is in the ITSJ_Pivot table and not in Lists_Master but the "not in" statement does not return the rows. Please help. Thanks.

Most likely this query:

SELECT email FROM lists_master

Will return a null value for one of the rows.

If so, then IN will always evaluate to either TRUE (it is in there) or UNKNOWN. So either it is NOT(TRUE) or it is NOT (UNKNOWN) which results in FALSE or UNKNOWN respectively:

create table lists_master
( --demo only, should have PKEY
email varchar(200) NULL
)

insert into lists_master
values (NULL)

insert into lists_master
values ('email@.address.com')
go

create table itsj_pivot
( --demo only, should have PKEY
email varchar(200) NOT NULL
)
insert into itsj_pivot
values ('email@.address.com')
insert into itsj_pivot
values ('anotheremail@.address.com')
go
SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master)

No rows

SELECT * FROM ITSJ_Pivot WHERE Email not IN (SELECT email FROM lists_master where email is not null)

1 row:

email
-
anotheremail@.address.com

The best way to write this is:

SELECT *
FROM ITSJ_Pivot
WHERE not exists (SELECT email
FROM lists_master
WHERE lists_master.email = ITSJ_Pivot.email)

In this case the NULL isn't a problem because EXISTS looks for the existance of a joined row, so the NULL condition is handled down in the WHERE of the subquery rather than in the comparison operator IN (working with NOT() logic and NULL comparisons can be really annoying.

|||Better to do a not exists. For one, it is generally quicker as you are creating a join between the two tables. For further explanation, see my blog below:|||Both NOT IN and NOT EXISTS are flattened to joins. In fact, in lot of cases you will get the same execution plan. It is just that the semantics for the constructs are different and how the checks are performed. Using EXISTS for existence/non-existence check is always the best approach.|||

SELECT p.* FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NULL

I prefer the join syntax. For me it is more clear.

|||That worked. Thanks for the help.|||

The problem with the join syntax is a couple of things:

1. You have both columns in the output:

SELECT * FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NULL

Change to select p.* (or expand the columns, which is best practice) and this isn't a concernt.

2. What about the opposite question, show me all of the ITSJ_Pivot rows that do have an email address? Now you have to include distinct to get unique results:

SELECT DISTINCT p.* FROM ITSJ_Pivot p
LEFT JOIN lists_master m ON p.email = m.email
WHERE m.NUSOID IS NOT NULL

You can use a join, but what if you don't need to data in the lists_master table in the results? This can be more costly to execute (especially if indexes cannot be chosen to make the operation easy to execute)

3. The most important thing to me is that the JOIN syntax doesn't ask the question in a clear manner. IN is the clearest:

Give me ITSJ_Pivot rows where the email address is in the lists_master table

though it only works when you have no nulls and you are working with one column. EXISTS expresses what you are looking for:

Give me ITSJ_Pivot rows where there exists a email address match in the lists_master table.

This is even more important when you are not just working with two tables. The exists condition works if you have a 30 table join or just 2. The join introduces extraneous data or NULLs that can be confusing.