Retrieve pages of rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Retrieve pages of rows

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2008-10-06 : 15:23:34

I need to retrieve a certain number of rows from a large table in a repetitive way to generate a xml string to send it thru internet.

Let's say,
retrieve the first 100 records then send them
then retrieve the second 100 records send them, and so on until eof

I'm considering in use a do-loop, counting the records using ado methods (movenext) but I would like to use Select statement

Thanks

JG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:24:58

Are you using SQL Server 2000 or SQL Server 2005 or later?

E 12°55'05.63"
N 56°04'39.26"

jggtz
Starting Member

32 Posts

Posted - 2008-10-06 : 15:53:17

SQL Server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:55:54

You can use the new OUTPUT operator and usse code similar to this.

WHILE @@ROWCOUNT > 0
BEGIN
delete from @stage

UPDATE TOP(100) @Table
SET Col1 = 1
OUTPUT inserted.Pk, inserted.Col2
INTO @Stage

select * from @stage for xml path('')
END

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-06 : 16:07:54

quote:
Originally posted by jggtz

I need to retrieve a certain number of rows from a large table in a repetitive way to generate a xml string to send it thru internet.

Let's say,
retrieve the first 100 records then send them
then retrieve the second 100 records send them, and so on until eof

I'm considering in use a do-loop, counting the records using ado methods (movenext) but I would like to use Select statement

Thanks

JG

Through the internet ? ADO ?

Do you have a frontend of some sort ? If thats the case, i feel its better implemented on your frontend.


Select solution =

case Frontend
when 'Classic ASP' then 'Use paging'
case 'asp.net 2.0' then 'use ADO.NET and data sets' 
case 'disparate systems' then 'Use XML Webservices'
else 'Use Peso solution'
end,
.....

jggtz
Starting Member

32 Posts

Posted - 2008-10-06 : 16:11:39

Thanks

Classic ASP is my case

JG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-06 : 16:38:09

If am guessing correctly, your solution is a website on a windows server running MSSQL2005/Classic ASP ?

What then is the XML for ? and what are you looking to achieve ?

paging over a web page ?

jggtz
Starting Member

32 Posts

Posted - 2008-10-06 : 21:46:27

Several salesmen with laptops will have a vb6 (required) frontend app
They will capture every sale
At end of day (or whenever the salesmen want) they will send all the captured sales to the server (at main office) using ftp
Every time they start the app, the customers, items, prices, offers, etc. will be imported from the sql server by calling an asp that reads the respective table from the server, generate a xml string that travel thru an internet connection, when recieved a procedure will update the local tables

The app will be connected to server only when send and recieve

There are few customers so in just one call I can bring them all
but there are many products and I have problems to bring them so I made a do-loop to bring only a few of them in every iteration
I did use ado-paging methods and its resolved
I just wanted to check if the select statement have a feature that helps me to bring a certain number of products at a time
Thanks
JG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources