2
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;
Database=D:\TestDB\First.xls;;HDR=YES', 
'SELECT * FROM [Sheet1$]') 
SELECT * from MasterData

And this error comes

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified 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)".

Please help

gmsantos
  • 1,412
  • 22
  • 30
user3586114
  • 27
  • 5
  • You need the appropriate OLE DB driver installed on the SQL server for this type of `OPENROWSET` query. Take a look at this: http://stackoverflow.com/questions/24130697/reading-ms-excel-file-from-sql-server-2005/24150349#24150349 – Dave Mason Jun 13 '14 at 15:47
  • yes i have try Microsoft.ACE.OLEDB.12.0 but again server error comes Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". – user3586114 Jun 13 '14 at 17:28
  • That's a different error than the one from your original post. The error in the comment above no longer indicates an OLE DB error. I see `access denied`. You'll have to do some more digging...Is the spreadsheet currently in use/opened by someone else? What about the Windows login that is used to run the main SQL service? Does that user have permission to open the file? – Dave Mason Jun 13 '14 at 17:38
  • You may also want to check the file attributes of the spreadsheet. Is it read-only? If you open the file with Excel, is there any password protection? etc. – Dave Mason Jun 13 '14 at 17:42
  • No there is no password. – user3586114 Jun 13 '14 at 18:11
  • 2
    i have solve my problem with run as administrator thanks all – user3586114 Jun 14 '14 at 06:27
  • Check out this post: http://stackoverflow.com/questions/7720621/excel-import-to-sql-server-using-distributed-queries?rq=1 – FutbolFan Apr 17 '15 at 18:31

0 Answers0