Sudden error, Open Rowset 2

[cisscott]

MS SQL Server 2000
CR 9.0 sp3
CE 9.0 sp2

Suddenly I have begun getting this error in CE:

OPEN ROWSET

CE doesnt give much more detail than that, When I open the report in CR I get the following:

Error: OPEN ROWSET

Query engine error: '21000:[microsoft][ODBC SQL Server Driver][SQL Server] Subquery returned more than one value. This is not permitted when the subquery follows =,!=,<,<=,>,>=, or when the subquery is used as an expression.


THen I click OK and the next error comes up...

FAILED TO OPEN A ROWSET:
Details:01003:[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: null value is eliminated by an aggregate or other SET operation.


........
These errors beganout of the blue a week or so ago. The research I have done indicates that CE 9.0 had an issue with this which was solved by SP2, so I patched CE to SP2 over the weekend. However the error still appears. THe report that generates the errors runs fine with some parameters, but gives this error everytime for others. Perhaps a datbase field was changed the last time I patched our retail software, but I'm not sure how to go about finding the root cause. If I can find it, I can fix it. Thanks.

 

[synapsevampire]

It sounds like you've used an Add COmmand or a SQL Expression, and within the query there's a statement which is doing something like:

select blah
from table
where table.value =
(select table2.value2
from table2
where <condition>)

And the table2 subquery is now returning 2 rows, whereas in the past it did not.

This is likely due to some new row(s) in a table. You might be able to correct it using a MAX() or a DISTINCT, although a MAX or DISTINCT might return more than one row as well, so it may not be a perfect solution long term.

The error comes from the query in SQL Server. Try copying and pasting the SQL intio Query Analyzer, it's going to return the same error.

-k

 

[cisscott]

Yes, synapse, this report is based on a stored procedure which is using lots of "select from dbo.table where condition = x".

 

[cisscott]

Well, I'm not sure that the old join statements are causing the problem, but I'm not finding anything else it could be either. I checked all the selects to be sure a column wasn't changed or renamed... here is the SP, maybe theres something wrong that I can't see:

CREATE PROCEDURE [dbo].[groupbooking] 
@Begin_Date varchar(10), @End_Date varchar(10),
 @Run char(4) AS
select distinct g.company, g.address1, g.address2, g.city, g.state, g.zip, 
  (select p.number from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'D') AS 'Phone:', 
  (select p.number from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'F') AS 'Fax:',
  o.attention, es.orderid, e.date, e.time, e.run, oe.seats, max(es.price) as 'rate', 
  (select count(es.seat) from live.dbo.eventseat es where es.orderid = o.orderid and es.buyer = '4Z') AS '# of comps',
  charindex('%', o.attention,1) as '%index' , right(left(o.attention, charindex('%', o.attention, 1)+1),1) as '# buses', 
  o.type, o.note1, o.note2, o.note3

from live.dbo.[order] o, 
live.dbo.eventseat es, 
live.dbo.event2 e, 
live.dbo.guest g, 
live.dbo.orderevent oe

where o.orderid *= es.orderid and 
o.orderid = oe.orderid and 
g.guestid = o.guestid and 
oe.eventid = e.eventid and 
g.company <>'' and
e.run = @Run and 

  convert(char(4), datepart(Yy, e.date)) + "-" + right("0" + convert(varchar(2), datepart(Mm, e.date)),2) + "-" + right("0" + convert(varchar(2), datepart(Dd, e.date)),2) >= @Begin_Date and 
  convert(char(4), datepart(Yy, e.date)) + "-" + right("0" + convert(varchar(2), datepart(Mm, e.date)),2) + "-" + right("0" + convert(varchar(2), datepart(Dd, e.date)),2) <= @End_Date and 

g.guestid <> 141324 and 
g.guestid <> 141212 and 
g.guestid <> 73 and 
g.guestid <> 278657 and 
g.guestid <> 2905

group by g.company, g.address1, g.address2, g.city, g.state, g.zip, g.guestid, o.attention, es.orderid, e.date, e.time, e.run, oe.seats, o.orderid, o.type, o.note1, o.note2, o.note3

order by g.company
GO

 

[vidru]

The likely culprit(s):
(select p.number from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'D') AS 'Phone:',
(select p.number from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'F') AS 'Fax:'

I would try (as k suggested) using either MIN or MAX to ensure you're only getting a single value:
(select MAX(p.number) from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'D') AS 'Phone:',
(select MAX(p.number) from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'F') AS 'Fax:'

-dave

 

[cisscott]

Sweet !.. Dave, and K.. you guys were right. I added the max to the select for the phone and fax numbers and it seems to have cleared up. However, I'm not sure I understand WHY it cleared up, or what clued you in to it being a problem with that particular pair of fields. I'm all ears, if you would care to elaborate. Thanks !

 

[LikePork]

I'll jump in to explain:

(select p.number from live.dbo.phone p where p.guestid = g.guestid AND p.type = 'F') AS 'Fax:'

can return more than one value, it says get all p.numbers from live.dbo.phone p where ...
adding max ensures that only 1 value is returned, hopefully the one that you want!

Query engine error: '21000:[microsoft][ODBC SQL Server Driver][SQL Server] Subquery returned more than one value. This is not permitted when the subquery follows =,!=,<,<=,>,>=, or when the subquery is used as an expression

This Error explaination gives you the hint, I think "or when the subquery is used as an expression" refers to what you were trying to do.

 

[cisscott]

Thanks for the reply Likepork. I guess I understand that part pretty well, but I meant the part about the MAX statement. Why would it not be DISTINCT, instead? DISTINCT seems to screw the SP up, but from reading the syntax in SQL server books online, the function of DISTINCT seems to fit what i need, although in reality, MAX does the job. Also what clue did I miss that pointed to the phone number field as the culprit? Thanks !

 

[vidru]

The first error you were getting, "Subquery returned more than one value" was the key, so it was assumed that there was at least one subquery in the procedure.

If you're using a subquery that's not in an EXISTS clause, in can only return a single value. If you take a look at your data, you've got at least one guestid in the 'phone' table with more than one type 'D' or type 'F' entry. Using MAX assures that you only get one value (the 'largest' one if you're dealing with numbers, the highest alpha value if string data).

DISTINCT is a completely different animal. It will give you each unique value (including NULLs, unless excluded in a WHERE clause). Consider the following table:
[tt]FieldID FieldValue
------- ----------
1 NULL
2 Bob
3 Bob
[/tt]

If you do a SELECT DISTINCT FieldValue from Table, the result would be:[tt]
NULL
Bob[/tt]

Books Online (aka SQL Server help) is an excellent resource for T-SQL help, not to mention Forum183.

-dave