I changed the datatype for a field called "LookupGUID" from uniqueidentifier (GUID) to Text so it could hold a comma separated list of "GUID"s. The script generated by SQLCompare to migrate the data looked something like:
INSERT INTO [dbo].[tmp_rg_xx_RptListFld]([RptListFldGUID], ..., [LookupGUID])
SELECT [RptListFldGUID], ..., [LookupGUID]
FROM [dbo].[RptListFld]
This generated a syntax error ("uniqueidentifier is incompatible with text") on the type conversion. This is of course a quirk of the (n)text type. Checking in Books Online revealed that only the (n)(var)char types can be casted implictly to (n)text. However, most types (uniqueidentifier, date, number types) can be cast (even implicitly) to (n)(var)char. This means that adding Cast()s to (n)(var)char in the SELECT clause would make data migration possible for these datatypes.
In my example I changed the SQLCompare script to use
Cast([LookupGUID] as Varchar(50))
and this made the synchronisation possible.
INSERT INTO [dbo].[tmp_rg_xx_RptListFld]([RptListFldGUID], ..., [LookupGUID])
SELECT [RptListFldGUID], ..., [LookupGUID]
FROM [dbo].[RptListFld]
This generated a syntax error ("uniqueidentifier is incompatible with text") on the type conversion. This is of course a quirk of the (n)text type. Checking in Books Online revealed that only the (n)(var)char types can be casted implictly to (n)text. However, most types (uniqueidentifier, date, number types) can be cast (even implicitly) to (n)(var)char. This means that adding Cast()s to (n)(var)char in the SELECT clause would make data migration possible for these datatypes.
In my example I changed the SQLCompare script to use
Cast([LookupGUID] as Varchar(50))
and this made the synchronisation possible.