ODBC Proxy table to MS SQL/Server table with Uniqueidentifier gives Endian flip

[Former Member]

When I define a proxy table from SQL Anywhere (12) through ODBC to an MS SQL/Server table that contains a uniqueidentifier, the value of the UUID is returned to ISQL with the bytes flipped around. Specifically, the Endianness difference from SQL/Server to SQL Anywhere is ignored.

Various articles around the net describe the different UUID representations, but getting different results from SQL/Server's "SELECT someuuidcolumn FROM sometable" and the same query through an SQL Anywhere proxy table query seems pretty much wrong - e.g., '00013c07-e7f5-4033-bdbf-b34d6391f445' vs '073c0100-f5e7-3340-bdbf-b34d6391f445'. In particular, having a '4' at the start of the third group of digits identifies a UUID as a "V4" GUID and really shouldn't be arbitrarily flipped around, regardless of how the UUID is stored in the database. Can someone say if it's an ODBC issue, but even then, shouldn't SQL Anywhere deal with it? (I believe the problem occurs with SQL Anywhere 16 too, but I haven't tried it myself.)

[Former Member]

Thank you for reporting this issue. I agree that there is something more that SQL Anywhere can do to properly handle GUIDs from SQL Server. We will investigate further and provide a fix.

[Breck_Carter]

There are three ways to do any one thing: the right way, the wrong way and the Transact SQL way 🙂

[Former Member]

From my research it would appear you can see this if your MS GUID is being bound as a binary value.

Whereas Microsoft stores their GUIDs as little-endian binary values, the standard for UUIDs is Most Signifiant Byte first (aka big-endian) or as describe in the RFCs for this "network byte order". See a reference to RFC 4122 for the sections on "4.1.2. Layout and Byte Order", and the many references to the terms "network byte order" and "endian".

SQL Anywhere implements the UUID statndard internally. This should not be a problem and should map correctly when interchanging data with Microsoft databases if the data types being used are proper UUID and not raw binary values.

So this is most likely failing if the proxy table is defined with a raw binary(n) instead of a UUID. You should be able to verify if this is your issue by issuing, in DBISQL, the statement

DESCRIBE TABLE <your proxy="" table="">

and noting if the column in question is of type binary(16, say) or type UUID.

The solution should be as simple as explictly defining the proxy as having the correct type. Let us know if this helps.

[Former Member]

Thanks for looking into this Nick, I very much appreciate it, however I don't think it's the problem. Using DESCRIBE TABLE from DBISQL and Sybase Central ("Columns" tab) as well as viewing the "Columns" attribute in MS SQL Server Management Studio, these columns are "uniqueidentifier" across the board.

FYI my versions are a little dated - SQL Anywhere 12 (12.0.1.3967) and SQL Server 2008 R2, all running on Windows 7 Enterprise ("MS Windows Version 6.1 (Build 7601: Service Pack 1)") so perhaps some updating is in order. We have SQL Anywhere 16 on one system, so I'll likely try it there and possibly build a small example case too.