Experienced the same problem when moving from sql server 32 bit to sql server 64 bit.
Stored procedures that have run fine as Jet 4.0 also run fine in 64 bit ACE 12.0. Well that is until some unknown executions of the stored procedures throws up the nasty messages which you have documented before this post.
Microsoft was kind enough to enlighten me in my paid technical support call that using it in stored procedures was not supported by them.
You have some stored procedures in SQL Server 2008 that used to run without issue. You have upgraded SQL Server to the 64 bit version and you are now seeing the following error message: "The OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure."
This is an unsupported scenario.
We were able to provide you with the following information:
Here is the article talking about supported scenarios.
Here is the information I want to call out.
In the Overview section number 4 talks system services using ACE not being supported. My understanding is that linked servers fit this scenario exactly.
In the additional information section, bullet number 2, we give a recommendation on a supported scenario involving SQL Server. You can use the import wizard or SSIS. In order to use SSIS the jobs need to run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive.
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
The Access Database Engine 2010 Redistributable is not intended:
As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).
As a replacement for the Jet OLEDB Provider in server-side applications.
As a general word processing, spreadsheet or database management system -To be used as a way to create files. (You can use Microsoft Office or Office automation to create the files that Microsoft Office supports.)
To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.
The Office System Drivers are only supported under certain scenarios, including:
Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
If SSIS is not an option, you can try a bulk insert since you are using text and CSV files. I have added articles on doing that below. If you need help with the articles, please reply all to this email and my co-worker Dennis will be able to assist you in getting in touch with the SQL Team who can assist with that.
BULK INSERT (Transact-SQL)
Based on this being an unsupported scenario we went ahead and non-decremented the case.
Given the lack of current support by Microsoft in this area and not feeling comfy with them supporting it in the future I am currently moving ahead with this path:
In areas where I can control the interfaces and quality of data, I have moved onto BCP to provide a solution. This will be an enormous undertaking given the volume of modifications/testing needed.
In areas where the interface and quality of data I do not have control of, I will move these items back to a 32 bit environment.