locked
Try to export data to excel from sql server using OPENROWSET issue RRS feed

  • 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

    Friday, November 11, 2016 11:25 AM

All replies