"select rows @start through @end" how to do it?

Thought I'd post the whole problem I'm trying to solve -- seems like it's
common enough that there must be a "normal" way to do this.
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
So I thought I'd do it with a UDF, but I thought I'd see if there was a good
one already written for this.
Thanks!Jesse wrote:
> Thought I'd post the whole problem I'm trying to solve -- seems like
> it's common enough that there must be a "normal" way to do this.
> I want to say "select the top 2000 rows, starting at row 15000, from
> myTable order by foo". I know I could use SELECT TOP 17000... to
> limit the number of rows returned, but I'm still pulling a ton more
> data than I need.
> So I thought I'd do it with a UDF, but I thought I'd see if there was
> a good one already written for this.
> Thanks!
Select Top 2000
From dbo.MyTable
Where MyCol >= 15000
Order By Foo
Unless you have some column in the table that is numbered, there is no such
thing as "row 15000". Rows are not positional. In SQL 2005, you can assign
row numbers to the columns in the query using hte ROW_NUMBER() function.
David Gugick
Quest Software|||Thanks, David --
You're right, if the query isn't sorted. But if you specify an ORDER BY
clause, then there is a definite Nth row. (If the data changes, the Nth row
may change as well, but for my purposes, that's OK.)
In your example, if you can stop at the 2,000th row returned from an ordered
query (SELECT TOP 2000), it follows that you could skip over the first N of
them.
Jesse

> Select Top 2000
> From dbo.MyTable
> Where MyCol >= 15000
> Order By Foo
> Unless you have some column in the table that is numbered, there is no
> such thing as "row 15000". Rows are not positional. In SQL 2005, you can
> assign row numbers to the columns in the query using hte ROW_NUMBER()
> function.
>
> --
> David Gugick
> Quest Software
>

"New transaction is not allowed because there are other threads running in the session." -

That is a SqlException I got at a...

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

Anyone an idea what THAT means? How do I cause it? How can I work around it?

I think you probably have an open connection somewhere. See if you can start the transaction after you close the connection.|||

I have about 25 open connections. Because that is - quess what - an ASP.NET application.

Once connection per page.

And every page has - one thread. No multi threading at all.

Plus it is legal for a thread to have multiple open connections.

|||

Have you enabled Multiple Active Resultsets (MARS)?

For connections with MARS enabled, manual transactions started via TSQL BEGIN TRAN must end (COMMIT or ROLLBACK) within the same scope that started the transaction

If your issue meets the the aboving conditions, you can try this work aroud:

To begin tansactions that are not required to be scoped under
MARS-enabled connections, use the client driver API constructs to begin and end transactions. If existing code relies on BEGIN TRAN operations without
corresponding COMMIT/ROLLBACK, MARS can be turned off until the application can be modified to use the client driver transaction APIs

|||Nope, no MARS. Actually that was (not is) old SQL Server 2000 code from .NET 1.1 that was traansported over. Worked flawlessly under .NET 1.1, and looks flawless to me still.