3007 - SQL Server UNIQUEIDENTIFIER values

When replicating SQL Server UNIQUEIDENTIFIER values to a target database, the default data type mapping may not produce the expected result.

SQL Server supports a special type named UNIQUEIDENTIFIER, which can be used to store "Globally Unique Identifiers" (GUIDs). This type is based on the BINARY(16) SQL Server type. A UNIQUEIDENTIFIER value is usually represented as a GUID identifier, with the following hexadecimal format:

XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX (where X can be 0-9, A-F)

These tokens respectively represent an integer (4 bytes), a short (2 bytes), a short (2 bytes) and a byte array (2 + 6 = 8 bytes).

The .NET language has an equivalent type for it (Guid), defined as:

typedef struct _GUID

{ 

   DWORD Data1; 

   WORD Data2; 

   WORD Data3; 

   BYTE Data4[8];

} GUID;

When replicating this data to a target database, normally Syniti DR/DBMoto uses a binary representation as a standard data type mapping to the target. For instance, when using Oracle, the target type is a raw(16) which is a binary array with a size of 16 bytes. The problem is that using this conversion, Data1, Data2 and Data3, being integer types, get their byte order reversed, as they are subject to endian ordering when choosing how to display them. Data4 instead, being already a byte array, remains the same. So for instance, the value:

11223344-5566-7788-9900-aabbccddeeff

Will be replicated as:

44, 33, 22, 11, 66, 55, 88, 77, 99, 00, AA, BB, CC, DD, EE, FF

Note that the order of the first four bytes is reversed. Also bytes 4 and 5 are swapped and bytes 6 and 7 are swapped. But the final 8 bytes are in the same order they're represented as in the string.

This format, even if technically valid and aligned to the source value, can be seen as an incorrect conversion and may not be the expected result of the replication. In order to convert this format to a more similar value, there are two options:

1. Use hexadecimal strings in CHAR or VARCHAR columns to represent this data type. In this case, uniqueidentifier values will be converted and displayed exactly as they are displayed on source. Use a size of 36 or higher to fit all the data.
2. Use some kind of mapping conversion function to replace the order of the bytes. One example could be the following:

C# code:

public static byte[16] FlipEndian(Guid guid)
    {

        var newBytes = new byte[16];
        var oldBytes = guid.ToByteArray();
        for (var i = 8; i < 16; i++)
            newBytes[i] = oldBytes[i];
        newBytes[3] = oldBytes[0];
        newBytes[2] = oldBytes[1];
        newBytes[1] = oldBytes[2];
        newBytes[0] = oldBytes[3];
        newBytes[5] = oldBytes[4];
        newBytes[4] = oldBytes[5];
        newBytes[6] = oldBytes[7];
        newBytes[7] = oldBytes[6];
        return newBytes;
    }

VB.NET code:

Public Shared Function FlipEndian(guid As Guid) As Byte(15)
        Dim newBytes = New Byte(15) {}
        Dim oldBytes = guid.ToByteArray()
        For i As var = 8 To 15
            newBytes(i) = oldBytes(i)
        Next
        newBytes(3) = oldBytes(0)
        newBytes(2) = oldBytes(1)
        newBytes(1) = oldBytes(2)
        newBytes(0) = oldBytes(3)
        newBytes(5) = oldBytes(4)
        newBytes(4) = oldBytes(5)
        newBytes(6) = oldBytes(7)
        newBytes(7) = oldBytes(6)
        Return newBytes
    End Function