DTS Export to Excel Too many Recs?

Hi All,

I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

I am able to successfully export around +- 65,000 rows, but the operation fails after that.

I need to be able to get all the rows out, so using TOP obviously doesn't work.

Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

Thanks!You need to have a key field(s) that you can sort on. So lets say you have a unique id like excel_id numbered from 1 to 120000:

select top 65000 ... from table order by excel_id
select top 55000 ... from table order by excel_id desc|||If you want an inner subset like 65000-90000 then you would use something like:

select top 25000 ... from (select top 55000 from table order by field desc) order by field|||If you do have a key then you can use KEY BETWEEN 65001 and 90000, etc. ...and if you were using MySQL we wouldn't have even bothered to look at the question...In short, - DON'T USE IT!|||Are you using a standard DTS data pump to export the data? Or are you using something else?

If you are using a data pump then why not take care of the problem programaticly? Select all the records and keep an internal counter so you know when you reach 65000 and when you do change your target to a new spreadsheet zero the counter and continue on...

Just a thought,...|||There are plenty of other ways to do this as well, it just depends on the requirements for your export...|||With a programmatic solution, your performance will suffer. Allow the database to do its job.|||Thanks for all the replies/suggestions!

There is no key field.
I couldn't find a solution, so I just dumped the data, opened it in textpad, cut out three chunks (~35,000 rows) and imported them into 3 diff .xls files.

Interesting enough for those in the anti MySQL crowd ... chances are, this very forum almost certainly uses MySQL. I say this for two reasons. 1. PHP server side scripting. 2. This is a vBulletin forum.
Not to mention the growing interest in open source products vs proprietary!

Cheers!