Hello everyone...  I'm having a problem with merge replication.  My publisher and subscriber are SQL 2008 SP1, and my distributor is SQL 2008 R2.  Currently, there is no access to the subscriber data aside from the replication agents, so there are no data changes going on there, data is only being modified on the Publisher.  My plan was to get everything setup and do some internal testing before releasing the subscriber for use.
I am getting the following errors (from the merge agent job log):
"The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit"
and
"Error converting data type nvarchar to numeric."
I have an alert setup on the replication agent failure, and the email says this...  "Replication-Replication Merge Subsystem: agent PubSQL-Customer-tables-mer-SubSQL-2 failed. The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does no"
I started getting these errors a few days after implementing merge replication, so I removed the articles from the publication.  I did start a trace and then start the merge agent so the error would show up, but my trace doesn't show an exception (not sure why).  I think I have found the issue in the trace, I see the following command:

declare @p4 smallint
set @p4=0
declare @p5 numeric(38,0)
set @p5=468269
declare @p6 numeric(38,0)
set @p6=473269
declare @p7 numeric(38,0)
set @p7=473269
declare @p8 numeric(38,0)
set @p8=478269
exec sys.sp_MSallocate_new_identity_range '07576F87-BA91-4863-BFE3-89228285F4A3','B241AAA2-1EAB-4AA5-B702-4966FE0A408E',2,@p4 output,@p5 output,@p6 output,@p7 output,@p8 output,N'tables-merge',N'SubSQL',N'Customer'
select @p4, @p5, @p6, @p7, @p8

Then, right after, this is executed:

declare @p4 smallint
set @p4=0
declare @p5 nvarchar(40)
set @p5=N'468269                                  '
declare @p6 nvarchar(40)
set @p6=N'473269                                  '
declare @p7 nvarchar(40)
set @p7=N'473269                                  '
declare @p8 nvarchar(40)
set @p8=N'478269                                  '
exec sys.sp_MSallocate_new_identity_range '07576F87-BA91-4863-BFE3-89228285F4A3','B241AAA2-1EAB-4AA5-B702-4966FE0A408E',2,@p4 output,@p5 output,@p6 output,@p7 output,@p8 output,N'tables-merge',N'SubSQL',N'Customer'
select @p4, @p5, @p6, @p7, @p8

The parameters to sys.sp_MSallocate_new_identity_range in the second command are not supposed to be sent as nvarchar, which would explain why I am getting the "Error converting data type nvarchar to numeric." message.
Why are these parameters being sent as nvarchar?  Is this causing the other error message to be raised?
Here is the script I used to add the article to the publication:

use [Customer]
exec sp_addmergearticle @publication = N'tables-merge',
	@force_reinit_subscription = 1, @column_tracking = N'true', @subset_filterclause = null,
	@description = null, @creation_script = null, @pre_creation_cmd = N'none', @type = N'table',
	@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, @force_invalidate_snapshot = 1,
	@schema_option = 0x000000014C034FD1,
	@source_owner = N'dbo', @destination_owner = N'dbo',
	@article = N'dbo.GroupActivity', @source_object = N'GroupActivity',
	@identityrangemanagementoption = N'auto', @pub_identity_range = 5000, @identity_range = 5000, @threshold = 90
go

How can I fix this?
Any help would be greatly appreciated.  Thanks in advance.
Jarret

I'm experiencing the same error:

The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit...

If you figure this out please share with us.  It makes no sense to me because my subscribers have plenty of identity values left in their ranges for their articles with identity columns.  I'm very confused by this.

Hello Brandon,

I've figured out my problem.  I have a database level trigger that does some logging on DDL changes, and when it was time to update the identity range, my trigger was being fired.  Well, the merge agent account didn't have access to the database that the DB trigger logs to, so it was failing.  I fixed this, and it started working again.

I mentioned in my first post that my Profiler trace didn't pick up the errors when the merge agent was running...  This is because the error comes through the 'User Error Message' event, once I added that to my trace, I was able to determine the root cause of my issue.

Hope this helps.

Jarret

• Marked as answer by JarretModerator Tuesday, June 15, 2010 2:13 PM

Hello Jarret,

It's been a while since you solved this issue, but i'll give a try:

We're currently experiencing the same issue in production.

We noticed you found out what was wrong, but did not explained.

How did you solve this ? 

Thank you for you answer!

Hi we are having the same problem.

The trace shows that the before the User Error "Error converting data type nvarchar to numeric.", the  command was:

declare @p4 smallint
set @p4=0
declare @p5 nvarchar(40)
set @p5=N'131000013                               '
declare @p6 nvarchar(40)
set @p6=N'131100013                               '
declare @p7 nvarchar(40)
set @p7=N'131100013                               '
declare @p8 nvarchar(40)
set @p8=N'131200013                               '
exec sys.sp_MSallocate_new_identity_range '4BE96D61-F04A-4E40-AB6D-4260A7894674','E04EC438-8DE9-4F97-844B-305B1EF23728',2,@p4 output,@p5 output,@p6 output,@p7 output,@p8 output,N'IPoint_Pub',N'POE-02\SQLEXPRESS',N'IPoint'
select @p4, @p5, @p6, @p7, @p8

Althought, this command works fine running it from the SQL EM.

Any thoughts are more than welcome!