Error inserting in Microsoft Access 2002-2003 "long Text" field SQL Server 2019 linked Server Microsoft Access Database Engine 2010 x64
I am attempting to migrate a number of stored procedures, which insert to Microsoft Access 2002-2003 format database files, as linked servers.
The process is currently working fine on the following configuration
Windows Server 2008 R2 Standard
SQL Server 2014
Microsoft Access Database Engine 2010 x64 - v14.0.7015.1000
The updated configuration is as follows
Windows Server 2019 Datacenter
SQL Server 2019
Microsoft Access Database Engine 2010 x64 - v14.0.7015.1000
The Microsoft.ACE.OLEDB.12.0 has registered fine on the newer server configuration, and is available as a linked server "provider"
The SP inserts to a number of tables, with varying data types, and all are successful, with the exception of fields where the SQL Server field is of type nvarchar and the Access mdb field is of type "Long Text", where I get the error.
Msg 7344, Level 16, State 1, Line 182
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[Access mdb Linked Server Name]" could not INSERT INTO table "[Access mdb as Linked Server]...[Table Name]" because of column "[Column Name]". The column had a bad status.
The Long text fields in the Access mdb are reported/displayed as varchar data type, in the linked server configuration.
I have tried writing simple, short, strings to the field, CASTing as varchar data type, but still get the same error.
I've done lots of googling, but can find no examples of others experiencing similar issues.
I suspect it is a compatibility issue, between the SQL server version and access database engine but, again, have been unable to find any useful resource.
Has anyone experienced a similar issue, and found a resolution?
Or if you can direct me to a suitable resource where I can find more info.
I managed to resolve this, with changes to the configuration of the OLEDB provider on the SQL server.
It was necessary to enable both: Dynamic Parameter and Allow inprocess
in the provider Options
- Using Dapper one-to-many (spliton) with generics
- How to join two SELECT queries
- Join two SELECT queries having same column names but different row values in both tables
- How can you represent inheritance in a database?
- String_agg for SQL Server before 2017
- How can I remove accents on a string?
- SQL - order by list order
- Convert varchar list to int in Sql Server
- "č" converted to "c" when querying through SQL Server 2008
- SQL Server 2016, Invalid object name 'STRING_SPLIT'
- SQL: Data Transpose
- How do I create a unique key that is case sensitive?
- ODBC Data Sources; can't add MySQL Driver
- SQL Server - how to query JSON string which has numeric value as keys
- BizTalk 2020 WCF-Sql Polling Receive Location no Response
- Pyodbc: Login Timeout Error
- Unable to upload rdl SSRS 2016
- Database's backup location that created by Azure Data Studio on Mac M1
- Entity Framework: The instance of entity type '<T>' cannot be tracked because another instance with the key value '{Id: x}' is already being tracked
- Check if a connection is in a transaction
- Can I have multiple rows of tabs in SQL Server Management Studio
- SQL Server: Make all UPPER case to Proper Case/Title Case
- How to disable transactions for individual migrations in Flyway on SQL Server
- Natural (human alpha-numeric) sort in Microsoft SQL 2005
- SQL how to do a LIKE search on each value of a declared variable
- Roll Up with column changes
- How to open a huge .sql file
- ExecuteReader TimeOut solved by changing the name of the stored procedure
- How do I get list of all tables in a database using TSQL?
- SQL Database in restoring mode