I have a asp.net whereby user can dynamically create a sql statement by choosing the columns and tables names. I will then use a stored procedure to construct and execute the sql statement and return the results as a report form on asp.net page.

However, if the query is to be too big i would like to execute it on the ms sql server and return the query output as an EXCEL file. Can anyone give me a push in the right direction? I understand that DTS and bcp can do it but i'm totally clueless about what they are. Are they any articles i can read regarding using them to export data as an excel file. I do not know the query before hand and hence i cannot create a excel file with the right columns and use DTS Designer to output to it. Also, I will also not know the location of the file to be output to, as they will be input by the user.

Also, another concern is the max 65k rows excel support in 1 spreadsheet. How do i get DTS /bcp to "cut" the query results at say 40000 rows interval and insert them into the same excel file but different worksheets?