2

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express).

So for the following code snippet:

Declare @strSQL as varchar(200)
declare @file as varchar(200) 

SET @file='C:\temp\file.xlsx'
SET @strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'
SET @strSQL=@strSQL+N'SELECT * FROM ##mytemptable'
PRINT @strSQL
Exec (@strSQL)

EXPANDED SQL STATEMENT

SELECT * 
INTO #mytemptable 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\file.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');

SELECT * FROM ##mytemptable

I get this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

  • 1
    Try running FileMon when the SQL is executed. This will tell you if you have required permissions or not as described blogs.msdn.com/b/spike/archive/2008/07/23/… – Kin Shah Aug 5 '13 at 13:45
  • you appear to be writing to #mytemptable then you try to select from ##mytemptable. I guess you want ##mytemptable in both places – Steve Aug 5 '13 at 16:06
  • 1
    Is there a reason why you're using OPENROWSET instead of a more appropriate tool like BCP, BULK INSERT, or SSIS? – Mike Fal Aug 5 '13 at 17:57
2

Excel is 32-bit, if you are using 64-bit version of Windows it could be causing this problem. I have 32 bit drivers installed and get the same error as you. In order to install the office 64 bit drivers would have to uninstall the 32 bit drivers first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.