Sep
3
2008
I must admit, I have slowly been migrating into the 64-bit world, one machine at a time with great apprehension, yet minimal to near nil problems. I have been very satisfied with things until today...
I was creating a new stored procedure which opened an Excel file and inserted the data into a temp table using OPENROWSET and the Microsoft.Jet.OLEDB.4.0 driver. Well, guess what... Microsoft didn't release a 64-bit version of the Microsoft.Jet.OLEDB.4.0 driver...
ErrorMessage: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered. ErrorNumber: 7403 ErrorLine: 1 ErrorSeverity: 16 ErrorState: 1
After digging around the web for a few minutes I became quickly aware of the fact that the nonexistence of this drive was reality... Straight from a Microsoft forum post from a MS moderator (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221492&SiteID=1);
64-bit SQL Server cannot use Jet, there is no 64-bit OLEDB provider for Jet. Currently there is no plan to write one either, but this could change based on demand.
Now, this is not good news for me at this stage of the game... I have a development environment which is 64 bit... Saving grace, this machine is the odd ball in the company... Yeah, bleeding edge guy wants to be cool and run the latest and greatest... Hmmmffff...
Anyway, there are a few workarounds, one of which is to use a BULK INSERT command, others include using SSIS and compiling it to run at 32-bit... I haven't tried either one yet, but an curious what the benchmarks would be between the options and the OPENROWSET method. I know the OPENROWSET is probably the least attractive...
Anyway, what are your thoughts? Has anyone masterminded and successfully implemented a clean workaround for this shortcoming of the 64-bit environment?
References:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221492&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494063&SiteID=1
http://www.eggheadcafe.com/software/aspnet/32205147/server64bit-openrowset-c.aspx