Our web application stores UTF-8 encoded data in VARCHAR fields. Recently, we have provided our customers access to this data via ODBC using DataDirect's OpenAccess ODBC driver. This is achieved using DataDirect's OpenAccess SDK, writing a C# .Net class to interface with the service. We only allow customers to perform SELECT queries. We also limit results to 100K rows at this time.
This solution really works great, except querying fields with some of this encoded data appeared as gibberish to the users, understandably. In the next release of our service, I would like to offer users the ability to query using unencoded strings, and subsequently see the unencoded results.
I have solved this by UTF-8 encoding the incoming query, then returning the unencoded results by flagging VARCHAR fields was WVARCHAR. This is actually working really well. It does mean, though, that every VARCHAR column comes back as a WVARCHAR despite the presence (or lack of) Unicode characters.
Many of our customers at this time have adopted the method of creating a linked server in SSMS on their own SQL Server instance, and it is my preferred method of connecting. Since our service limits results to 100K rows, I encourage everyone to use OPENQUERY to perform queries. It seems, though, that there is something I'm missing in the configuration of my Linked Server in SSMS. When string functions (LEFT, RIGHT, SUBSTRING for example) are performed against these WVARCHAR columns, SSMS returns the following error:
OLE DB provider "MSDASQL" for linked server "LOCAL" returned message "Requested conversion is not supported.". Msg 7341, Level 16, State 2, Line 1 Cannot get the current row value of column "[MSDASQL].ColName" from OLE DB provider "MSDASQL" for linked server "LOCAL".
This would be returned for a query such as this:
SELECT *
FROM OPENQUERY([LOCAL], '
SELECT LEFT(FirstName, 2) AS ColName
FROM dbo.User
')
If I were to remove the LEFT function from this query, the FirstName column would be returned, properly decoded, without error.
This problem does not affect queries in, say, MS Excel for example. And on the surface the strings seem to be properly affected by their respective functions as I debug my way through the .Net class which interfaces to the DataDirect product. I've attempted to change all the Server Options on the Linked Server properties, but I've not had any luck finding the right combination. I'm just looking for the right tree to bark up here. Is it my treatment of the results, changing them to WVARCHAR? Or is it some attribute of my SSMS linked server that I need to change that I'm missing?
