May 18, 2026 at 9:33 pm
After upgrading 2 Merge Replicated databases to SQL 2022 and re-establishing the Merge Replication on the new environment, the synchronization is giving me the error message: "Error converting data type nvarchar to numeric. (Source: MSSQLServer, Error number: 8114)". The initial create of the Publication/Subscription failed silently because of some unusual network and cross domain restrictions, but we were able to fix it, but we fixed it without doing a full delete and recreate of the Merge Replication.
The SQL error log message is: "Replication-Replication Merge Subsystem: agent Publisher-P01-UserAppDB-UserAppDB-Subscriber-P02-5 failed. Error converting data type nvarchar to numeric."
In a trace I see: exec sys.sp_MSadd_merge_history90 @p1 output,5,6,N'Error converting data type nvarchar to numeric.',1,0,1,0,3,N'',0,0,0,0,0,0,0,0,326763,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,'1EF4CB2E-6498-4836-A600-0581DD789519',0
This was a side by side upgrade, and the Publication article properties say to drop the existing articles, so there shouldn't be any discrepancies between the tables. I've also run scripts to compare the articles between the Publisher and the Subscriber, and there are no datatype discrepancies.
My best "guess" is that this is actually an error internal to the sp_MSadd_merge_history90 stored procedure caused by artifacts from the initial setup and the solution will be to drop and recreate the replication, possibly restarting the 2 servers in between to ensure there are no artifacts hanging about.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 19, 2026 at 10:15 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 20, 2026 at 8:35 pm
After a restart of the Subscriber, this error cleared up. It looks like something was cached that was causing this, and the restart flushed it.
I'm still no wiser on what specifically was the cause.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 25, 2026 at 4:19 am
I've found the root case of this problem. We have a weekly process that analyses the databases for sensitive data - Data Classification, a few minutes after it starts we start seeing the "Error converting data type nvarchar to numeric" error. This is a problem that only occurs on the Subscriber in the Merge Replication pair.
There's a Microsoft function "ADD SENSITIVITY CLASSIFICATION" that "Adds metadata about the sensitivity classification to one or more database columns." When you have Merge Replication, there's an MS delete batch stored proc running continuously that blocks the ADD SENSITIVITY CLASSIFICATION function, which in turn blocks other MSMerge Repl stored procs. Within a few minutes of the MS Merge Repl stored procs being blocked on the Subscriber we start seeing the "Error converting data type nvarchar to numeric" error. As soon as I killed the SPID running the ADD SENSITIVITY CLASSIFICATION function, the error cleared up and the data merge went through.
The short term solution is to not run the ADD SENSITIVITY CLASSIFICATION on the Subscriber. This isn't an issue for the Merge Replication database as it is being checked on the Publisher side, but there are other databases on the Subscriber that aren't being replicated, and these need to be checked for DATA SENSITIVITY CLASSIFICATION.
Anyone from Microsoft out there wanting to pick this up as a bug?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply