Merge Replication and Geography types Error converting data type varchar to geography

If your table has Geography data types, then with Merge Replication you will get the following error:

Error messages:
The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)
Get help: http://help/MSSQL_REPL-2147199398
Error converting data type varchar to geography. (Source: MSSQLServer, Error number: 8114)
Get help: http://help/8114
The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)
Get help: http://help/MSSQL_REPL-2147200999

 The fix to this is as follows:

 

sp_changemergearticle 'publicationName','tableArticle','schema_option','0x000000000C034FD1',1,1

go

sp_changemergearticle 'publicationName','tableArticle','stream_blob_columns','false',1,1

go

Replace publicationName and tableArticle as required. You will need to generate a new snapshot and apply.

 

Looking at what these options mean, it's not entirely clear why they correct the issue with Geography types. They concentrate on streaming blobs, so can only assume that internally Geography is dealt with the same as a blob. Interesting...

 

Thank you Microsoft for this solution btw - I would never have guessed it !