Member Avatar for Maideen

Hi

I encounter the above error message while executing import excel data into sql data table.
It worked fine in Mssql 2005 x86 and MS Office 2007 x86.
But currently we upgraded the system to MSSql 2012 x64 and MS Office 2010 x86.
I downloaded the accessdatabaseEngine.exe for MSoffice 2010 x86 and installed
Still It presist the same error.

Pls advice me.

Maideen

  • 3 Contributors
  • 2 Replies
  • 221 Views
  • 4 Weeks Discussion Span
  • Latest Post Latest Post by Hamza Saleem

Dani AI

Generated

Short, practical checklist that goes beyond the tips already posted by and . The root cause is usually that the ACE OLE DB provider either isn’t visible to the process doing the import or SQL Server/SISS doesn’t have the provider configured correctly. The steps below are focused, reproducible checks and a couple of safer alternatives if you can’t get the provider to behave on the server.

First, verify SQL Server can see and use the provider and that ad-hoc queries are allowed. Run these in a privileged SSMS window:

-- allow ad-hoc OPENROWSET/OPENDATASOURCE
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- enable provider options needed for distributed queries
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

-- list registered OLE DB providers
EXEC master.dbo.xp_enum_oledb_providers;

Those T-SQL checks set the provider flags SQL Server expects and let you confirm the provider shows up under Server Objects -> Linked Servers -> Providers. (learn.microsoft.com)

If you’re using SSIS or the Import/Export wizard, check the runtime bitness and where the package runs. SSIS runs 64-bit by default; Visual Studio has a project Debugging property Run64BitRuntime, and SQL Agent job steps have a “Use 32-bit runtime” option. You can also run the 32‑bit dtexec directly from the Program Files (x86) path to force 32-bit execution. When AllowInProcess is enabled, make sure the SQL Server service account has read/write access to the service profile Temp folder (for example, C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp) — missing permissions often produce initialization failures. (learn.microsoft.com)

If the provider path keeps failing on a server, consider removing the provider dependency entirely: convert the Excel to CSV or parse .xlsx files in application code (Open XML SDK or a library such as EPPlus) and then bulk-import the cleaned data into SQL Server. Server-side use of Office connectivity has known limitations (especially in hosted environments), so an application-side parse + BULK INSERT/SqlBulkCopy is usually more robust. Example (Open XML SDK):

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false)) { /* read sheets */ }

This approach avoids provider/bitness issues on the DB host. (learn.microsoft.com)

Caution: changing provider flags or enabling ad-hoc queries affects surface area/security; do these steps only with appropriate permissions and after testing on a non-production copy of the server. If you post the exact T-SQL you’re running and whether the import is being run from SSMS, SSIS, or a scheduled job, a targeted next step can be suggested.

Recommended Answers

All 2 Replies

Member Avatar for Taywin

After googling it, I have found 2 posts that may relate to your problem (how to solve the 'not' being registered) -- here and here. Hope this would give you some ideas.

Member Avatar for Hamza Saleem

Its because your project is running in 32-bit mode where as windows is 64-bit. You change the mode from the options.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.