Freigeben über

Replication: If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set

  • Artikel

Problem:

If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set during the application of the SNAPSHOT (only).

After the snapshot has been applied, if you execute the command to verify the property of the sql_variant column:

SELECT SQL_VARIANT_PROPERTY(column_name,'BaseType'), * FROM table_name

You will see that on the Publisher it shows as (example) DATE while on the subscriber its NVARCHAR

Cause:

 Its an issue that invovles both the snapshot agent and SNAC10.

 Solution/Workaround

 THE WORKAROUND THAT WAS POSTED HERE BEFORE HAS BEEN REMOVED AS IT HAD OTHER IMPLICATIONS AND IS NOT RECOMMENDED

 Microsoft is still looking into this however the solution to this issue is to verify if sql_variant is the right datatype to use or if there is anything way (within the app code) to work around the basetype difference.