Exporting hundreds of thousands of records with ColdFusion

Question

Using ColdFusion 9.0.1, I need to export hundreds of thousands of database records to Excel XLSX or CSV (XLSX is preferred). This must be done on demand. So far I've tried using cfspreadsheet but it chokes when exporting more than a couple thousand rows in the XLSX format. However, exporting to XLS works fine (of course there is a ~65,000 row limit).

What are my options to export so many records? Theoretically the users could need to export as many as one million records. I'm also using SQL Server 2008 R2 -- is there a way to somehow export the records to a file there and then send the file through CF to the user? What options do I have? Thanks.

Answer 1

Since you are using SQL Server 2008, you could take advantage of SQL Server Reporting Services (SSRS) and create a report that can be called via web service (or HTTP GET/POST) by ColdFusion. SSRS has the capability to export reports as Excel as well. You'll need to read up on SSRS to make this work, but it's fairly easy to do.