CODEWITHSUNDEEP
sql-serverexcellinked-server
Adrian Chrostowski
Adrian Chrostowski

Reputation: 434

Microsoft.ACE.OLEDB.12.0 text truncation errors when select from Excel

I've googled the entire universe but no success. When I run query I get the following errors:

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)' returned truncated data for column '[Microsoft.ACE.OLEDB.12.0].Short Description'. The actual data length is 540 and truncated data length is 510.

Actual data lenght varies from little over 510 to over 1700. I have changed the TypeGuessRow setup to '0' in registry, I have ordered the excel rows by mentioned column length and still bad. Here is my query:

insert into SRC_REMEDY_RAW
    select 
        *
    from openrowset     ('Microsoft.ACE.OLEDB.12.0',
                        'Excel 12.0;Database=D:\ReleaseTeam_db\data\data_src.xlsx;HDR=YES;IMEX=1',
                        'select * from [Murex - SW Business Application$]')
    where [Ticket ID] is not null and [Problem Owner] is not null
    order by len(cast([Solution Description] as nvarchar(MAX))) desc, len(cast([Short Description] as nvarchar(MAX))) desc;

I'm using SQL Server 2014 Express.

Upvotes: 2

Views: 2550

Answers (1)

Adrian Chrostowski
Adrian Chrostowski

Reputation: 434

After months of googling and reading finally I found a solution to this issue. Turned out that changing TypeGuessRow default value to zero works, but I did edit a wrong registry key. The correct key for Excel 12.0 is as follows:

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

After changing this entry the provider works fine without the truncation error.

Upvotes: 2

Related Questions