On SSMS, ODBC linked server queries show unicode text data

In SSMS I'm connected to an Intersystems Cache database using ODBC driver and linked server When I fetch data using a SQL query like

SELECT Text FROM OPENQUERY([ODBC_CACHE_DB],'SELECT TOP 100 Text FROM cls.Actions')

IN SSMS it gives results but it gives ? for arabic characters like

"18:29:00 [Mohamad] ????? ??? ?? ??? ??? ?????? ????? ? 18:30:30 [Customer] Hi Sirius is jai"

how could get arabic texts ?

note: I can read and write arabic text with using nvarchar data type

Had a similar issue. My setup was a linked server setup between MSSQL 2012 cluster and Intersystems Cache 2009.x using MS OLE ODBC provider.

My observations below:

• Convert/Cast on the column with nvarchar datatype did not work -- as in it shows the ???? (This is on SSMS)
• When using 3rd Party DB management tools such as Database.net and WinSQL, I was able to see the correct characters.
• Playing around with the ODBC driver's Unicode SQL Types function only intermittently helped show the correct characters.

The solution:

• Enable Unicode SQL Types function on the ODBC driver
• Make changes to the test sql query that is being executed on the Intersystems Cache db. If you keep executing the same query, the output is cached for sometime (not sure how long exactly).

In my case, the sql server cluster was not under my control and took a few days to play around with the different variations.