Export to Excel DTS Fails too Many rows?

[rxsid]

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 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!

 

[JayKusch]

SET RowCount may be what you are looking for. Check on it w/in Books Online.

Thanks

J. Kusch

 

[KatanaCS]

rxsid -

Your limitation isn't in MSSQL, it's in Microsoft Excel. MS Excel only supports up to 65,536 rows; this means there is NO way it will accept all that data. You'll need to use some other program to store the output, or break it into 2-3 Excel files.

Regards,
KatanaCS

 

[rxsid]

Katana -

Yeah...I know it's a limitation with Excel. However, MS SQL is also limited in that there is no relatively easy way to extract rows from the middle, which seems rather odd to me (guess that's the way SyBASE set it up). With MySQL, one can use a simple one line query with a "SELECT * FROM table LIMIT $row_to_start_from, $num_row_to_limit_return" to get middle rows.

I wound up just dumping the whole table, opening it in textpad, and extracting out ~50,000 row chunks.