The Publisher Failed To Allocate A New Set Of Identity Ranges For The Subscription After Another Publication Deletion
Jan 17, 2007
Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
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 (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
The following is the report from the SQL Server Mobile Subscription wizzard, Any Ideas?
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
A call to SQL Server Reconciler failed. Try to resynchronize. HRESULT 0x80004005 (29006)
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 HRESULT 0x80045647 (0)
Invalid parameter @subid specified for sys.sp_MSmerge_log_idrange_alloc_on_distributor. HRESULT 0x0000523F (0)
The operation could not be completed.
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Initially i thought it might be that some of the articles had primary keys that were of type nvarchar rather then Int thus resulting in no identity range being able to be assigned to those articles.
Test 1: I tried removing all articles that had nvarchar primary keys and left only one table that had an identity Int primary key colum. I then ran the snapshot agent. I then run through the Subscription Wizzard again and the error was the same.
Test 2: Then reading the error message again i tried those tables that didnt use identity columns and the wizzard completed successfully. Any idea what would be wrong with my articles that have identity columns. The article properties for the identity columns use the Identity Range Management defaults.
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
I have configured transactional publication where some tables have a simple row filter. And I made a supscription to this publication. When I make snapshot and replication initialization, all rows are updated correctly and passed to subscriber. But when I change some column at a row included by filter at publisher, it is never updated on the subscriber. Other tables and that same table when I remove filter is updated correctly.
What can be problem?
I am using SQL Server 2005 Standard for publisher, and SQL Express 2005 for subscriber, both with SP2.
Hi, We have an existing merged replication schema that works well. One of the tables is named audit. Currently this is a bidirectional transfer. However, we want a new audit table that will transfer data from the subscriber to the publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.
Because of problems trying to alter databases used for replication my software would need to find out if a database is a publisher or was repliated (using T-SQL). Is this possible?
in the clustering lab I was setting up transact replication - created the publication ok, but when I setup the subscription, wasn't sure what account it needed to use :/ tried various formats of Administrator and Password;1 etc - but no dice. what account I need to be using?
I have a program running on Pocket PCs that synchronizes with a desktop SQL database using SQL Mobile. My problem is that I can't delete old subscriptions from the publication; now the publication has 25 subscriptions and won't accept any more (limit of SQL Workgroup edition). I can delete non-SQL Mobile subcriptions just fine.
Running sp_replmonitorhelpsubscription provides this information:
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.
I have two SQL 2000 Server running on Windows 2003 Standard Edtion. I am using merge option to replicate database as Transactional option is excluding some tables. Both server are not in Active Directory and I am trying to replicate over the internet. I got following error message while using Merge option:
Error Message:
The subscription to publication 'XXX' is invalid.
Error details:
The subscription to publication 'XXX' is invalid. (Source: Merge Replication Provider (Agent); Error number: -2147201019) --------------------------------------------------------------------------------------------------------------- The remote server is not defined as a subscription server. (Source: x.x.x.x (Data source); Error number: 14010) ---------------------------------------------------------------------------------------------------------------
Any help regarding the same will be appricated. Thanks.
Can push and pull subscriptions be configured on the publisher/distributer server?
We have server A running SQL Server 2000 configured as Publisher/Distributor and also a Push subscription (transactional replication) to Server B configured as Subscriber.
The idea was to have Server B as backup server if Server A goes down. Users will continue to update data in server B till server A is up.
Once server A is up, I want to synchronise the up-to-minuit date from Server B to Server A in the form of Transactional replication configured as "Pull Subscription" from Server A.
I keep getting this following error when trying to set-up an updateable subscription on SQL 2005 (latest service packs). Server is set for mixed mode auth. At the final stage of the new subscription wizard I get: Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard) The user is not associated with a trusted SQL Server connection Login failed for user ''. The user is not associated with a trusted SQL Server connection Error Number: 18456, Severity: 14, State: 1,Line Number: 1
Can anyone recommend a possible way of debugging this? Is it some issue with RPC security?
I am using sql 2005 replication in my application. But how can i log the transactions that are happening in the server and client databases please help me...
We got an error "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE"
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) We had 24CPU intell cores and 64 gb RAM on server. Sql service starts with enabled AWE + LPM and "-g512" option.
Why we still use such relic configuration it is not my fault. i'm investigate log and find that memorystatus "Optimization Queue" section has really huge values.
Optimization Queue Overall Memory = 1321132032Target Memory = 1158799360 Last Notification = GROW Timeout = 6 Early Termination Factor = 5Small Gateway Configured Units = 32 Available Units = 26 Acquires = 6 Waiters = 0 Threshold Factor = 250000 Threshold = 250000Medium Gateway Configured Units = 8 Available Units = 7 Acquires = 1 Waiters = 0 Threshold Factor = 12 Threshold = 16094435Big Gateway Configured Units = 1 Available Units = 1 Acquires = 0 Waiters = 0 Threshold Factor = 8 Threshold = 144849920
[code]...
What forced bPOOL to allocate pages in MTL? How to determine that queries? Same about SQL OS.As workaround i increase -g startup option to "-g2048", and "max server memory" was decreased to 54Gb.
Hi all, I'm a rather newbie, not only to this forum but also to sql server having a question to the following issue: Is it possible to drop/remove/delete the "orphan" of a merge subscription on one instance of sql server without having the (former) distributor/publisher (on other instance) available? The background is: I had a small replication infrastructure with two instances (on two machines), one the publisher and distributor, the other the subscriber. Now it happened that the publisher/distributor machine was completely set up new without having the replication dropped in advance, what remained on the subscriber is now a database with all the merge/replication tables and the guid columns in the user tables. Moreover this, an entry remained in the subscription saying that a subscription with the former publisher exists...
Can I remove these "orphans" without having to setup the instance again?
From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.
How can I achieve these operations programmatically?
In particular, how do I initialize a single merge subscription from the publisher?
I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time
I have a large number of merge subscribers and want to reinitialize all except one
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
I have an SSRS report with parameters for Created On Start and Created On End. Users run this manually and choose the date range to display records for.
I would like to set up two different subscriptions
1) to send weekly on Monday morning for "last weeks" records and 2) to send monthly to send "last month's" records.
I've a merge rep running with 4 subscribers for a while. Now I wanted to add another subscriber and when I open the publisher in the pull subscription assistant dialog, there are no publications in it. (even not if i switch to "allow anonymous sub") But every- thing seems to be alright wirh the publication itself! It even doesn't work to select the publication in a pull subs. from an other db on the same server! Any ideas?
I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??
I want to set up monitoring to make sure I am aware if something for some reason does not replicate
- I have a transactional replication between a publisher and a few suscribers, - The servers are SQL 2003. - I have Identities columns in some of the tables - I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it: 1) avoid that the marge agent assign a new identity range when a server goes down 2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
- I have a transactional replication between a publisher and a few suscribers, - The servers are SQL 2003. - I have Identities columns in some of the tables - I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it: 1) avoid that the marge agent assign a new identity range when a server goes down 2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
- I have a transactional replication between a publisher and a few suscribers, - The servers are SQL 2003. - I have Identities columns in some of the tables - I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it: 1) avoid that the marge agent assign a new identity range when a server goes down 2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
I have a database table with a simple identity column. As a starting point... when I run the following queries... this is what I get:
SELECT Max(AliasID) FROM Account_Managers 1300006
DBCC CHECKIDENT ('Account_Managers') Checking identity information: current identity value '1300006', current column value '1300006'.
Now... I set up merge replication, with the plan of allowing SQL Server to manage my identity ranges for me. I set up my ranges to be 100000 on the publication, 100000 on the subscriber, with a threshold of 80.
I would EXPECT to see the constraint ([AliasID]>(1300006) AND [AliasID]<=(1400006)) on the publisher and ([AliasID]>(1400006) AND [AliasID]<=(1500006)) on the subscriber.
However... what I do get is:
([AliasID]>(1300006) AND [AliasID]<=(1400006) OR [AliasID]>(1400006) AND [AliasID]<=(1500006)) on the publisher and
([AliasID]>(1500006) AND [AliasID]<=(1600006) OR [AliasID]>(1600006) AND [AliasID]<=(1700006))
Can anyone tell me why I get the OR... which essentially makes my identity ranges twice as large as they should be?
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient. Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
I have a SQL Server 2005 Compact Edition database that synchronizes with a SQL Server 2005 Standard database via merge replication.
The problem I have is when I cancel a synchronization, the database is left in such a state that I cannot add any new records. I dug around a bit and have found that in the INFORMATION_SCHEMA.COLUMNS view, AUTOINC_MAX has been incorrectly set to the same value as AUTOINC_MIN for the primary keys, which explains the 'out of range' error I get in my program when running off such a database.
In contrast, a non-corrupted database has AUTOINC_MAX set to AUTOINC_MIN + 999, presumably because of my subscriber identity range of 1000.
If this is the only thing that has gone wrong, all I need to do is programmatically set the AUTOINC_MAX values to AUTOINC_MIN + 999, however I can't find out where to do this (and INFORMATION_SCHEMA.COLUMNS is a view so it can't be updated obviously.)
I have also heard that 'compacting' the database may help, but I don't know how to do this either!
I have several email data driven subscriptions setup. They have been working fine for the past couple of weeks. We have recently gone through some layoffs. Now the subscriptions are starting to fail.
I am assuming the smtp server is not allowing invalid email address to go. But if I have one invalid email address why would the entire job fail? How can SSRS see if each email address is valid at the time of processing?
Is there a setting to allow invalid email address to not fail the job? They only solution that I could think of is to create distribution email groups. This would be rendered on the smtp server and not in Reporting Service Service.
Does anyone have any good solutions they would like to share?
Log file:
at ReportingServicesCDOInterop.MessageClass.Send() at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification) ReportingServicesService!library!10!12/26/2007-10:43:15:: Data Driven Notification for activation id ac381bd5-7a04-4e8e-b5cf-510a3833c4f0 was saved. ReportingServicesService!library!10!12/26/2007-10:43:15:: Status: The e-mail address of one or more recipients is not valid. ReportingServicesService!notification!10!12/26/2007-10:43:15:: Notification 65af159a-b2c6-4667-905b-b2342898a28f completed. Success: False, Status: The e-mail address of one or more recipients is not valid., DeliveryExtension: Report Server Email, Report: Tech Lead Program - District Summary, Attempt 0 ReportingServicesService!dbpolling!10!12/26/2007-10:43:15:: i INFO: NotificationPolling finished processing item 65af159a-b2c6-4667-905b-b2342898a28f ReportingServicesService!library!4!12/26/2007-10:43:16:: i INFO: Call to RenderFirst( '/Marketing/TTL/Tech Lead/Tech Lead Program - YTD Technician Summary' ) ReportingServicesService!dbpolling!c!12/26/2007-10:43:18:: i INFO: NotificationPolling processing 1 more items. 2 Total items in internal queue. ReportingServicesService!dbpolling!11!12/26/2007-10:43:18:: i INFO: NotificationPolling processing item a6731001-092e-4a48-841e-bd22640956d7 ReportingServicesService!processing!12!12/26/2007-10:43:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 1., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 1. ReportingServicesService!library!11!12/26/2007-10:43:19:: i INFO: Call to RenderFirst( '/Marketing/TTL/Tech Lead/Tech Lead Program - Customer Detail Report' ) ReportingServicesService!library!11!12/26/2007-10:43:22:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties. ReportingServicesService!emailextension!11!12/26/2007-10:43:22:: Error sending mail, CDO error -2147220977, will not resend ReportingServicesService!emailextension!11!12/26/2007-10:43:22:: Error sending email. System.Runtime.InteropServices.COMException (0x8004020F): The server rejected one or more recipient addresses. The server response was: 550 5.7.1 Unable to relay for xxxxx@searshc.com
