SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Conversion of parameter/column from data type VARCHAR1 to ASCII failed

While one of SQL developers was working on a database query which connects and queries remote data source, a SAP HANA database using SQL Server Linked Server connection, he has experienced following error:

OLE DB provider "STREAM" for linked server "(null)" returned message "[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10427 Conversion of parameter/column (2) from data type VARCHAR1 to ASCII failed".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

For solution of "Conversion of parameter/column from data type VARCHAR1 to ASCII failed" errors, follow the steps:
Identify the LinkedServer where SQL programmers experience the error.
On SQL Server Management Studio, when you are connected to the SQL Server instance find the LinkedServer under "Server Objects > Linked Servers" nodes.
Right click on Linked Server node and display Properties window
Identify the ODBC data source name on Linked Server Properties window General tab.

SQL Server Linked Server ODBC data source

Launch ODBC Data Source Administrator application.
On "System DSN" tab, select the data source that is used by the SQL Server Linked Server connection.
Press "Configure" button and then "Settings" button for managing "Advanced ODBC Connection Property Setup" options.

Press "Add" button to add a new ODBC connection property CHAR_AS_UTF8 with value "true" as seen in following screenshot.

add advanced connection property for SAP HANA ODBC connection

Default value for "CHAR_AS_UTF8" connection property is "false"
If "CHAR_AS_UTF8" connection property is used then connection returns CHAR data in UTF-8 format.

For software developers connecting their applications to SAP HANA databases using ODBC connection, a full list of connection properties and their usage can be found at SAP HANA Client Interface Programming Reference documentation.

Then execute the same SQL query using Linked Server to access remote SAP HANA database to test if error is resolved by recent changes.

declare @SQLHANA varchar(max) = 'SELECT .... '

Now the SQL query is executed and returns data successfully as seen in following screenshot. The character causing SQL error is possibly the marked characters with red.

conversion from data type VARCHAR1 to ASCII

I hope adding the ODBC connection property "CHAR_AS_UTF8" solves "Conversion of parameter/column from data type VARCHAR1 to ASCII failed" errors.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.