I need to import Excel (xls - 2003-2007) data into a Sql Server 2008 database. Both the Excel file and the database live on a Windows Server 2003
R2 sp2 64 bit machine. I’m fine with it running in 32 bit mode but it all needs to happen on the same server. Also, I would like it to run in T-Sql but as a fallback we can do this in SSIS.
My T-Sql code which runs fine on a 32 bit machine is as follows:
SELECT * Into #TempTable From OpenRowSet('Microsoft.Jet.Oledb.4.0','Excel 8.0;Database=\\Servername\Folder\filename.xls;HDR=NO;IMEX=1', 'select
* from [Main$]')
On the 64 bit server: