Select Top chokes when rows not available

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Top chokes when rows not available

Author

Topic

dixon
Starting Member

2 Posts

Posted - 2004-09-16 : 19:25:58

I am using a dynamic stored procedure to return a recordset to an asp page. To limit traffic (and because it is just too much information) I am using a "Select Top 250.." to restrict the number of records returned.

My problem comes when there are less than 250 rows to return. In the case where the problem first became apparent, I only had 10 rows of information available. When running this in query analyzer I have waited as long as 10 minutes with nothing returned.

On the asp page itself I had the following error returned:

Microsoft Cursor Engine error '80004005'

Data provider or other service returned an E_FAIL status

I believe I need some sort of conditional statement to correct this issue such as an "If Exists", but I have not been able to make it work. Any suggestions would be greatly appreciated.

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-16 : 19:54:34

Are you using a cursor? A query using TOP should not run any slower if there a re fewer than the number of records specified in the top clause. Can you post the query you are experiencing this on, and perhaps some DDL for the queried table(s)?

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

dixon
Starting Member

2 Posts

Posted - 2004-09-18 : 13:08:11

I am not using a cursor.
Some additional information:
> In the above case, where there were 10 records to return, I would seen 7 records returned before receiving the Microsoft Cursor Engine error.
> If I set the query to return the top 10 or even 11 records, everything would come back fine. It I set it to return as many as 15 it would choke again.

In the end I set a count on the number of records found and reset a variable for the "Select Top" to be equal to the number of records found if less than the default 250.

I won't bother you with the query unless you really want to see it, but I would like to thank you for your assistance.

Kristen
Test

22859 Posts

Posted - 2004-09-19 : 02:00:17

Can't see why this would be the case. FWIW we routinely do SELECT TOP nnn and don't have any problems for the application - so I reckon it must be something old in the ADO/OLEDB/ODBC drivers, or the cursor-type you are using in the application is in some way causing confusion (or possibly the way you "grab" the recordset from ADO).

Kristen

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-20 : 13:08:43

Kristen,

He said it happens in QA as well. Sounds fishy to me. Not sure what it could be.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 13:59:56

Missed the bit about QA, sorry.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:07:45

Nope...it ain't SQL...something else is going on...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' UNION ALL
SELECT '0'
GO


SELECT TOP 250 * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 14:16:31

Some ANDI or backwards-compatibility flag perhaps?

Does it do it with SET ROWCOUNT 250 as well as SELECT TOP 250 ??

Just my rambling thoughts

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources