Retrieving Snowflake data using ODBC DSN based Linked Server connection not rendering UNICODE properly in MS-SSMS

MuraliDeena 0

User's image

I am trying to read Snowflake data in MS-SSMS using ODBC DSN based Linked Server connection. Non-Latin characters are showing up as box or arrow symbols rather than what it is. I could insert / view non-Latin chars in simple SQL insert statements properly. Why DSN based data retrieval of unicode is not working here. What is the work-around?

Erland Sommarskog 107.2K Reputation points

2024-01-31T22:42:55.8766667+00:00
To start, I don't use Snowflake, and do not have access to Snowflake for testing. If I understand the screenshot correctly, it seems that Snowflakes names the column from the value of the strings - and these column names come through correctly. Interesting, as they say. Here are some troubleshooting steps to try:

SELECT * INTO #tmp FROM OPENQUERY(SNOWFLAKE_PROD, 'SELECT ...') EXEC tempdb..sp_help '#tmp'

What is the data type of the columns? What is the collation?

SELECT alphabeta, convert(varbinary, alpbabeta) FROM OPENQUERY (SNOWFLAKE_PROD, 'SELECT ''αβγδ'' AS alphabeta)

Does the Greek characters fare any better than the CJK characters? And what's in that binary string? What is the output of these queries:

SELECT @@version SELECT serverproperty('Collation'), databasepropertyex(db_name(), 'Collation')
MuraliDeena 0 Reputation points

2024-02-01T04:09:23.7466667+00:00

Hi @Erland Sommarskog , I appreciate your detailed steps to troubleshoot.

SQL1: The datatype is VARCHAR rather than NVARCHAR. Hence, I tried type CASTing in SQL Server side to hold UNICODE, but no luck, same result. Please note: In Snowflake, VARCHAR and all other string data types store Unicode UTF-8 characters by default.

SQL2: Greek behaves same way as CJK characters do.

SQL3: My understanding on Collation is to influence the rules that sort and compare data. Nothing to do with codepage, storing unicode values.

However, let me try with other collation relevant to CJK characters and redo SQL step1. I will keep this thread posted. Thanks again for your efforts on this.
Erland Sommarskog 107.2K Reputation points

2024-02-01T07:39:11.6666667+00:00

casting to nvarchar on the SQL Server side is not going to help. I was curious about the raw data type.

But the Greek example tells me enough. All characters come back 0x1A, a control character. That's really bad news.

In desperation, you could try a UTF-8 collation in SQL Server, but I suspect that this may be a configuration issue with the ODBC driver.

2 answers

LiHongMSFT-4306 25,651 Reputation points

2024-01-31T07:13:39.9333333+00:00

Hi @

Try to enable Unicode SQL Types function on the ODBC driver.See this similar thread: On SSMS, ODBC linked server queries show unicode text data. Best regards, Cosmog Hong

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.
Please sign in to rate this answer.
Deenadayalan, Murali (Cognizant) 0 Reputation points

2024-01-31T08:39:17.64+00:00

Hi @LiHongMSFT-4306 - Thanks for your response with most relevant solution. Unfortunately, I don't see "Unicode SQL Types" in the Snowflake ODBC DSN configuration properties. It takes a very few inputs like connection related values. I think "SQL Types function" configuration is specific to a different database & not Snowflake If I am not wrong. Thanks again.
Sign in to comment
Erland Sommarskog 107.2K Reputation points

2024-02-01T21:36:22.7866667+00:00
Here is one thing that you can try (although I'm not overly optimistic):

EXEC sp_serveroption SNOWFLAKE_PROD, 'use remote collation', true EXEC sp_serveroption SNOWFLAKE_PROD, 'Latin1_General_100_CI_AS_SC_UTF8'

As I mentioned, I don't have access to Snowflake, but I did some experiments with MySQL which also requires MSDASQL + ODBC. When I tried some Greek characters, they came through just fine.

To note, though, is that there are two versions of the MySQL Driver: There is the ANSI driver and the Unicode driver. My Greek characters worked with the Unicode driver. With the ANSI driver I got all question marks. And that is not at all surprising: that's the normal fallback character when no other good fallback is available. But you got 0x1A, which is a non-standard choice for a fallback.

I don't know if there is a similar issue with the Snowflake ODBC driver.

I'm also a little curious what happens if you try the work räksmörgås. This word has three Swedish characters that are in the code page of your collation. Are they also distorted?
Please sign in to rate this answer.
MuraliDeena 0 Reputation points

2024-02-02T12:50:00.51+00:00

I think we are getting little closer. All same box chars start appearing decoded (I mean some equivalent representation). I am proceeding with different options seeing a little light at the end of the tunnel :) . I will keep posted once this gets resolved. I appreciate your help on this. Cheers.

Note: Simple select without converting to ::TEXT is failing some other error "Msg 7347, Level 16, State 1, Line 11 OLE DB provider 'MSDASQL' for linked server 'SNOWFLAKE_PROD' returned data that does not match expected data length for column '[MSDASQL].'RÄKSMÖRGÅS''. The (maximum) expected data length is 10, while the returned data length is 13. "

Erland Sommarskog 107.2K Reputation points

2024-02-02T21:56:02.79+00:00

So what changes did you do here? I see that you change the database collation. Did you also run my sp_serveroption commands?

The output you get is what happens when UTF-8 is taken to be ISO-8859-1 (or Latin-1), which is the code page for you server collation.

And, it seems that when you have a linked server which is something else than SQL Server, the collation of the data returned will be the server collation. But you can override this with the collation name server option and tell SQL Server which is the remote collation. The import part here is that SQL Server get the information about UTF-8.

The error message about the length seems to be a disagreement about what varchar(10) means. Snowflake reports varchar(10), where 10 means 10 characters, but in SQL Server varchar(10) means 10 bytes.

Deenadayalan, Murali (Cognizant) 0 Reputation points

2024-02-09T12:37:42.0433333+00:00

Hi Erland Sommarskog, tried the collation with the help of DBA. It worked well for Chinese characters. Thanks. But on the other hand, the SQLs that I wrote started failing due to length issues (double bytes) which we understand. In addition to that, there is a limitation in LinkedServer OpenQuery SQL not to exceed 8kb. I am trying to find alternates without impacting other programs run in server. Apologies, I could not respond this earlier on this.

Erland Sommarskog 107.2K Reputation points

2024-02-09T20:18:21.5+00:00

For my own interest can you clarify what worked? Do I get it right that it was these two:

EXEC sp_serveroption SNOWFLAKE_PROD, 'use remote collation', true EXEC sp_serveroption SNOWFLAKE_PROD, 'Latin1_General_100_CI_AS_SC_UTF8'

AND the database collation had to be UTF8 collation? Or could you use Latin1_General_CI_AS as the database collations once you had set the server options?

Yes, the limitations of 8000 bytes and be bit of pain. You could try:

EXEC(@sqlstring) AT SNOWFLAKE_PROD

A nice property of this solution is that it permits parameters, which OPENQUERY does not.

For EXEC AT to work, you to set one more server option:

EXEC sp_serveroption 'SNOWFLAKE_PROD', 'rpc out', true
Sign in to comment

Share via

Retrieving Snowflake data using ODBC DSN based Linked Server connection not rendering UNICODE properly in MS-SSMS

2 answers