Error inserting in Microsoft Access 2002-2003 "long Text" field SQL Server 2019 linked Server Microsoft Access Database Engine 2010 x64

Question

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.

Answer 1

This sounds like an Access problem to me.

To wit, SQL Server does not really know that it is talking to Access. It talks to an OLE DB provider for the data source. Exactly how it does that depends on what capabilities the OLE DB provider exposes.

As you seem to have the same OLE DB provider in both setups, one like to think that it should work in both cases. But it could be that SQL Server talks to the OLE DB provider in a different way which provokes this error. But to troubleshoot it, you would need to be on the Access side of this.

(I'm an SQL Server guy, with no experience of Access.)

Answer 2

Hi @Hill, Matt

The LONGTEXT field is always stored in the Unicode representation format.
If the datatype name TEXT is used without specifying an optional length, such as TEXT(25), the LONGTEXT field is created. This allows the CREATE TABLE statement to be written so that the data type is consistent with Microsoft SQL Server.
Access has a maximum varchar (text) type of 255, so if a text type field is greater than 255, it is best to define it as a comment type (in Access) or text type (in SQL Server).

Best regards,
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

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