How to Solve 'Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0' Error in SQL Server?

Troubleshooting "Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0" for linked server null

In the provided T-SQL query, you encounter an error stating that you cannot create an instance of the OLE DB provider "Microsoft.Jet.OLEDB.4.0" for the linked server. Let's address this issue.

Solution:

1. Ensure Administrator Permissions:
   Confirm that you are logged in as an administrator on your local PC. The TEMP folders are created under C:WindowsTEMP and the user must have write access to those directories.

2. Configure Ad Hoc Distributed Queries:
   Execute the following T-SQL commands to enable Ad Hoc Distributed Queries:

   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
   GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
   GO

3. Install Microsoft.ACE.OLEDB Provider (SQL Server 2012 and 2014):
   For newer versions of SQL Server, such as SQL Server 2012 and 2014, you need to install the Microsoft.ACE.OLEDB.12.0 provider.

   • Download the provider from: https://www.microsoft.com/en-us/download/details.aspx?id=13255
   • Install the provider on your SQL Server.

4. Configure Microsoft.ACE.OLEDB Properties:
   After installing the Microsoft.ACE.OLEDB provider, execute the following T-SQL commands to configure its properties:

   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

5. Update the Linked Server:
   Modify the linked server definition to use the updated connection string:

   INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                          'Excel 8.0;Database=G:\Test.xls;',
                          'SELECT * FROM [Sheet1$]')
   SELECT *
   FROM   dbo.products

   Ensure that the file path and Excel version specified in the connection string are correct.

By following these steps, you should be able to resolve the "Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0" error and successfully import data from Excel into your database.

The above is the detailed content of How to Solve 'Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0' Error in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!