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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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



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 .... '
exec (@SQLHANA) AT HANAB1P

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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems