Few weeks before one of my client had an issue with replicating geometry data type in sql server 2008 r2 version.
If you try to set up very simple merge replication between two instances and try to synchronize geometry data type, Replication fails with the below error:
If you try to set up very simple merge replication between two instances and try to synchronize geometry data type, Replication fails with the below error:
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
Get help: http://help/MSSQL_REPL-2147199398
Error converting data type varchar to geometry. (Source: MSSQLServer, Error number: 8114)
Get help: http://help/8114
Get help: http://help/8114
I spent quit a time resolving this issue but at the end i approached Microsoft sql engineers. They confirm this as a bug in the current version and gave me the workaround.
Below is the reply from Mirosoft and applying this fix resolved the above issue:
Error and Symptom:
------------------------------
We got the below detailed error message in the profiler trace.
exec sp_MSadd_repl_error 3109,0,5,N'USER-PC\SQLEXPSP1',N'8114',N'Error converting data type varchar to geometry.',50,1,N'
SQL Merge Agent encountered an error.
This error occurs on the subscriber side (SQLEXPSP1), while the merge agent is applying the changes to the subscription. The data type in the published article is geometry, but it seems to be converted to "varchar" by some unknown reason. And then, conversion error occurs when the merge agent wants to convert the "varchar" back to "geometry" to match the schema on the subscriber.
Reproduce and Analysis:
-----------------------------------------
1. Repro: I spent quite some time to reproduce the same error in my local labs. This error can be always reproduced in SQL Server 2008 and 2008 R2. Below is the error that is captured from the subscriber profiler trace. (You could also capture a similar trace if you run profiler against the SQLEXPSP1 instance.)
exec [dbo].[MSmerge_ins_sp_8F98318B5A5D4AD92A55CAA41E884BDE]
'7E976B77-94C4-DF11-9867-000FFEE4266F',
2,
0x881E2A55A4CA01000000FF,
0x00,
2005,
'', ----this is the null char
'7E976B77-94C4-DF11-9867-000FFEE4266F',
0,
0x00,
90
2. Cause: This SP failed with error "Error converting data type varchar to geometry." The error occurs because this procedure get a null char for the 6th input parameter, which couldn't be converted to a geometry value. By definition, this SP expects a geometry data here.
3. Conclusion: I involved our production team for further clarification. And they confirmed this is a known issue in current builds of SQL Server 2008 SP1 and 2008R2. This is intended to be fixed in next Service Pack release.
Workaround:
------------------------
To work around this issue, we can use the below modified script to manually create the articles with two modified parameters.
exec sp_addmergearticle @publication = N'GeoMetryPub', @article = N'SpatialTable', @source_owner = N'dbo', @source_object = N'SpatialTable', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0
The @schema_option was 0x000000010C034FD1, and we changed it to 0x000000000C034FD1.
The @stream_blob_columns was N'true' and we changed it to N'false'.
For more information about the sp_addmergearticle script, we can refer to
http://msdn.microsoft.com/en-us/library/ms174329.aspx
http://msdn.microsoft.com/en-us/library/ms174329.aspx
Note: You may need export the articles' creation script before dropping it. Then modify the schema_option and stream_blob_columns parameters, execute it. Then re-sync the replication. It should work after the modification.
Enjoy :) :) and thanks to Microsoft for their excellent support here.
--Puneet
thanks mate..I have fixed up the link
ReplyDeletePuneet, do you know if this has been fixed in any of the 2008 R2 service packs that have been released? Great article also, thanks!
ReplyDeleteNo, As per my knowledge, they have not fixd this in 2008 r 2 service pack 1.
ReplyDeleteWow! I've been looking for an answer like this for days! I automated the whole process here, as I have many spatial tables:
ReplyDeleteDECLARE
@Name VARCHAR(128),
@sql varchar(5000)
DECLARE gandgCursor CURSOR
FOR
SELECT [name]
FROM [Fish].[dbo].[sysmergearticles]
ORDER BY
Name
OPEN gandgCursor
FETCH NEXT FROM gandgCursor
INTO @Name
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql =
'
exec sp_changemergearticle
@publication = N''FISHREP'',
@article = N'''+@Name+''',
@property = N''schema_option'',
@value=N''0x000000000C034FD1'',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
exec sp_changemergearticle
@publication = N''FISHREP'',
@article = N'''+@Name+''',
@property = N''stream_blob_columns'',
@value=N''false'',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
'
PRINT 'Executing Statement - '+ @sql
EXEC (@sql)
FETCH NEXT FROM gandgCursor
INTO @Name
END
CLOSE gandgCursor
DEALLOCATE gandgCurso
Thanks for this! I'm surprised it was so hard to come across this page. Most google results point to the same forum thread which refers to having the same versions on the publisher & subscriber. This is the only thing that solved my problem.
ReplyDeleteI am glad this resolution helped you tom :)
DeleteMany thanks! As with tom I feel so very fortunate to have come across this article after so much searching. Great job tracking this down!
ReplyDelete