I beat my head against my desk for several hours dealing with this issue and didn’t find any very good documentation out there on this issue. When attempting to run a stored procedure on 64bit Windows Server 2008 R2 and in MS SQL Server 2005, which was attempting to read from a table and insert into an Excel spreadsheet using OPENROWSET, I was receiving the following errror:

cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”

Typically, this error usually means the file listed in your openrowset function call:

1. Does not exist

2. Does not have the appropriate permissions.

3. Is open by another program and locked.

In my situation, that was not the case. In turns out that Microsoft actually stopped supporting Microsoft JET on 64 bit windows. That’s obviously a big problem because many applications still use this. I realize it’s an OLD solution, but it’s still very commonly used.

Luckily, MS just released MS Office 2010 BETA and drivers for that. You can download it from:

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en#filelist

Once you do that, keep in mind you will NOT be able to use the same driver. You have to change to the ACE driver. Below is a sample of the OPENROWSET connection string.

OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ’Excel 12.0;Database=C:\documents\samples\addressbook.xls;’, ’SELECT firstname, lastname, phonenumber FROM [Sheet1$]‘)

• Previous Entry: Integrating timezoned legacy dates into UTC based Rails application