Try to export data to excel from sql server using OPENROWSET issue

Question

User264732274 posted

i issue below script and got error Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

EXEC SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=c:\CSV\testing.xlsx;', 
'SELECT * FROM [Sheet1$]') 
select top 10 * from Emp2

tell me how to fix it. i need to export data to excel file in UNC path. please guide me how to achieve it.

again i try below script too but this time getting different error Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

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=c:\CSV\testing.xlsx;','SELECT ID,Name FROM [Sheet1$]')
   select top 10 * from Emp2

please guide me how to export data to excel file which will be generated in UNC path.

thanks

Answer 1

User-183374066 posted

Hi Sudip,

Download Microsoft OLE DB Provider first and then try (It is required to import/export from excel)

See following discussion as well

http://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked

You can use import export tool as well

http://searchsqlserver.techtarget.com/feature/The-SQL-Server-Import-and-Export-Wizard-how-to-guide

https://msdn.microsoft.com/en-us/library/ms140052.aspx

Answer 2

User264732274 posted

i download and install MS OLEDB provider but the issue is permission because i am trying to dump data over UNC path which create the issue.

share any good solution to fix it.

Answer 3

User-2057865890 posted

Hi sudip_inn,

Use the following T-SQL code:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

reference: https://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm 

Best Regards,

Chris