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)".

marc_s
  • 8,613
  • 6
  • 43
  • 51
Utpal Mattoo
  • 121
  • 2
  • 1
    Try running **FileMon** when the SQL is executed. This will tell you if you have required permissions or not as described http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx – Kin Shah Aug 05 '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 05 '13 at 16:06
  • 1
    Is there a reason why you're using `OPENROWSET` instead of a more appropriate tool like [BCP](http://technet.microsoft.com/en-us/library/ms162802.aspx), [`BULK INSERT`](http://technet.microsoft.com/en-us/library/ms188365.aspx), or [SSIS](http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx)? – Mike Fal Aug 05 '13 at 17:57

1 Answers1

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.

nam
  • 21
  • 1