DTS Excel data exceeds 65536 rows

[ssingh]

I have a sql 2005 table with around 135000 rows which need to be exported to a Excel file (office 2007).
Excel has limitation of 65536 rows.
Please suggest if we can split data and then export in two files or two tabs etc.

Also, if someone can suggest - we can write query

SELECT TOP 65536* FROM tbl_Name

What to write for the remaining rows?

Thanks

[rmiao]

Can filter by column in where clause or add order by in select.

[aaron]

First,You can try add id(use row_number() function) for the recordes,then you can use id filter.

[aaron]

Else,you can choose use xp_cmdshall.
First, open xp_cmdshall, execute following clause
EXEC sp_configure 'show advanced options', 1
GO
　　-- RECONFIGURE
　　RECONFIGURE

　　GO

　　-- start xp_cmdshell

　　EXEC sp_configure 'xp_cmdshell', 0

　　GO

　　--RECONFIGURE
　　RECONFIGURE

　　GO
and then give a example
EXEC master..xp_cmdshell 'bcp "SELECT column1,column2,column3 from ( SELECT row_number() over(order by getdate()) as ''id'', column1,column2,column3 from databasename..tablename ) t where id between 1 and 65536" queryout d:\authors.xls -c -S"servername" -U"sa" -P"password"'