Getting a custom sized page from a sorted result set

Hi!
I've tried numerous solutions to this classic problem. I know how to do a
pretty scalable solution, but I'd like to hear some more ideas.
What I do now is something like this:
1. Declare a cursor inside an exec statement since order by doesn't support
variables
(I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
code)
2. Create a temporary table identical to the result set
3. Declare variables for all fields
4. Fetch from cursor [pagesize] times from [start] while inserting into
variables and then temporary table
5. Select from the temp table
6. Return total count of the result set for the pager mechanism
The main challenges I've had is how to fetch the correct page. In SQL Server
2005 I thought my troubles were gone since we got the new TOP(@.parameter)
feature, but when it comes to sorting it's still impossible.
Anyway, the demands are
1. Fast browsing of 5000+ rows big result sets
2. Sorting by any column
3. User defined page size
One alternative I'm considering is use a query returning the entire result
set, and then using a reader instead in .net, but the amount of data fetched
will still be too much when you reach the last page. I'm not sure though if
the sql injection removal code, the exec statement and the cursor will use
just as much resources... Should measure it someday, but if anyone allready
did it, I'd appreciate a link. :P
Anyone got a better solution than the one on top?
(Some MySQL fans I know mock me because they've got LIMIT, I want to hit
back.. ;) )
Lars-ErikIf I understand correctly, you want to retrieve N rows, starting from some
point in the result set.
You can solve part of the problem with set rowcount. You can use variables
so it can be passed to the stored procedure.
for example:
create procedure usp_GetPage
(
@.Number int
)
as
set rowcount @.number
select col1, col2
from table1
-- This will return @.Number rows from the table.
Paging is, offcourse more complicated but perhaps this helps a bit? The rest
is entirely up to the way you want to implement ordering and what would be
the criteria for defining pages (starting point and such)
MC
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>|||http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||Thanks guys :)
Both relevant info! Didn't know about the @.rowcount setting, and the
measures on the faq page was pretty interresting.
L-E
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>

Getting a count of items

I'm sure there is an easy solution to this, but I always end up
resorting to using cursors, which I know is bad. Maybe somebody can
help me bridge the gap on this one
Assume I have a table like:
Customers
CustomerName nvarchar(50)
State nvarchar(50)
Assume I'm looking for a count of customers in each state, like:
WA 50
ID 24
VA 36
In my current solution, I select all of the distinct states into a
temporary table, use cursors to iterate over each one and do a
count(distinct CustomerName) for each state.
There must be a more efficient way. Any suggestions?SELECT State, COUNT(CustomerName)
FROM Customers GROUP BY State
Why you need COUNT(DISTINCT)? Does your table not have a key? Is
CustomerName a person's name or a company's name?
<TravisLPruitt@.gmail.com> wrote in message
news:1141771218.305470.177870@.i39g2000cwa.googlegroups.com...
> I'm sure there is an easy solution to this, but I always end up
> resorting to using cursors, which I know is bad. Maybe somebody can
> help me bridge the gap on this one
> Assume I have a table like:
> Customers
> CustomerName nvarchar(50)
> State nvarchar(50)
> Assume I'm looking for a count of customers in each state, like:
> WA 50
> ID 24
> VA 36
> In my current solution, I select all of the distinct states into a
> temporary table, use cursors to iterate over each one and do a
> count(distinct CustomerName) for each state.
> There must be a more efficient way. Any suggestions?
>|||No, I do have a key. I was just simplifying it for the example up here.
But I think your suggestion is exactly what I needed. Thanks for the
tip!

get unspecified error when browsing data of CUBE in Analysis servise

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.

You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

get unspecified error when browsing data of CUBE in Analysis servise

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

get unspecified error when browsing data of CUBE

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

get unspecified error when browsing data of CUBE

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

Get PDF from SQL

Hi guys. I've seen a lot posted about this on various sites and can't come up with a working solution. I've got a PDF saved as a byte array in a SQL image field. I figured out how to save/load with images, and I'm pretty sure it's saving the PDF corectly, but I can't get it to load at all.

Here's what I've got:

byte[] image = (byte[])Com.ExecuteScalar();

stream.Write (image, 0, image.Length);

Response.ContentType = "application/pdf";

Response.OutputStream.Write(image,0,image.Length);

This returns a blank page with some spaces. Now that I think about it, maybe its not saving right then.. Any insight? It'd be greatly appreciated.

Try this:

byte[] image = (byte[])Com.ExecuteScalar();

Response.ClearContent();

Response.ClearHeaders();

Response.ContentType ="application/pdf";

Response.BinaryWrite(image);

Response.Flush();

Response.Close();

|||Hi, thanks. I still got a blank page though. Should that be working, in other words might it not be saving correctly? Is there any way to check? It saves images fine using the same method. Does anyone know any other methods off hand to display it? Thanks for your time.|||

Try saving your byte[] to disk and opening it in Acrobat to see if you are getting valid PDF data out. That code should work i took it straight out of a page that i use.

Martin

|||Make sure it's a separate page that dumps the PDF. If you are doing something silly like setting a session variable that tells a .ASPX page whether to dump the .PDF file, or display a form, you'll confuse the browser. The browser has a tendancy to remember the content type of a specific URL, if the URL remains the same, it expects the content type to remain the same as well.|||

Looks similar to mine:

Dim cmdAsNew SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn)

cmd.Parameters.Add(New SqlParameter("@.ID", SqlDbType.Int))

cmd.Parameters("@.ID").Value = Request.QueryString("ID")

' cmd.Parameters("@.ID").Value = 3

Dim drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader("Content-type", dr("MimeType"))

Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")

Dim buffer()AsByte = dr("Data")

Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()