Search code examples
sql-serverexcelms-accessssmsexport-to-excel

How to export huge data from sql server into excel file

I want to export huge data(about 5 millions rows with 2 columns) from sql server management studio into excel file, but the maximum size of excel file is around 1400000 rows

i tried import and export wizard but it does not work perfectly it returns 60000 records then the error occur:

enter image description here

when i choose the destination file, i should use one of excel versions if i choose 'Microsoft Excel 2007' the message appear(the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.(System.Date)) so i chose 'Microsoft excel 97-2003'

i want to export data to excel or access.

any suggestion?

Solution

  • "i want to export data to excel or access."

    Therefore import it to Access. 2007 has an overall 2GB Table size limitation, but no row limitation (that I can remember).

    Excel ain't gonna work for you. 2003 (and other's when running in 2003 compatability mode) have a limitation of 65,535 rows, 2007+ 1,048,576 rows.