Replication Problem After Running For 24 Hours With Action Msg Could Not Query Row Metadata At The 'Subscriber'
Sep 9, 2006
We are currently using a Merged replication and Push subscriber to replicate the databse from DB1 to DB2 every hours. The replication process successed for first 20 hours but we found that the replication process cannot be completed after 20 hours with the following error codes:
Action Code Last Action Msg
4 The process could not query row metadata at the 'Subscriber'
363 The process could not deliver insert(s) at the 'Subscriber'.
I am doing replication through C#.net after creating the publication in server and subscription in client. But i want to know how can we view the transactions that are happening to the both databases through the C#.net code...If anybody knows please help me....
Hi Can somebody suggest optimization on this query. ITs running for hours,I have checked on mutiple Servers but dont know where the problem is.It starts when i add the not exists piece if the query. Pls help
SELECT distinct isnull(P.id,'') PersonID , s.status , isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') FirstName , isnull(replace(Ltrim(Rtrim(P.MiddleName)),',',''), '') MiddleName , isnull(replace(Ltrim(Rtrim(P.lastname)),',',''),'' ) LastName , isnull(replace(Ltrim(Rtrim(P.Suffix)),',',''),'') Suffix , isnull(replace(Ltrim(Rtrim(p.company)),',',''),'') Company , isnull(replace(Ltrim(Rtrim(PA.AddressLine1)),',',' '),'') AddressLine1 , isnull(replace(Ltrim(Rtrim(PA.AddressLine2)),',',' '),'') AddressLine2 , isnull(replace(Ltrim(Rtrim(PA.City)),',',''),'') City , State = isnull(replace(Ltrim(Rtrim(PA.State)),',',''),'') , ZipCode = ISNULL(CASEWHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 3 THEN ('00' + (PA.Zipcode)) WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 4 THEN ('0' + (PA.Zipcode)) WHEN SUBSTRING(LTRIM(RTRIM(PA.Zipcode)),6,1) = '-' THEN ISNULL(PA.Zipcode,'') WHEN SUBSTRING(PA.Zipcode,6,1) = ' ' THEN ISNULL(PA.Zipcode,'') ELSE substring(PA.Zipcode,1,5)+'-'+ substring(PA.Zipcode,6,4) END ,'') , b.code , CommitteeTermID = '' , RevenueCategoryid = '' FROM vwpersons p with (nolock) join vwsubscriptions s on p.id = s.recipientid join vwcompanies c WITH (NOLOCK) on c.id = p.companyid join vwbusinesscodes b WITH (NOLOCK) on b.id = c.primarybusinesscode join vwRevenueCategorys rc WITH (NOLOCK)on rc.id = c.RevenueCategoryid join vwPersonPrefAddr pa WITH (NOLOCK) on p.id = pa.id WHERE NOT EXISTS (SELECT s1.recipientid FROM vwsubscriptions S1 WHERE S1.SHIPTOID =S.SHIPTOID AND S.PRODUCTID = S1.PRODUCTID AND S1.STATUSID IN (1,2,3,11,12)) and p.id in (select recordid from vwTopicCodeLinks tcl Where tcl.entityid = 457 And tcl.topiccodeid = 332 And tcl.value = 'Yes') and ((p.PreferredAddress = 'Business Address' and p.BadBusinessAddress = 0) or (p.PreferredAddress = 'PO Box Address' and p.BadPOBoxAddress = 0) or(p.PreferredAddress = 'Home Address' and p.BadHomeAddress = 0)) and b.code in ( 'M2','V2','V5','W1','W2','W3','W4' ,'W5','W6','W7','W8','W9','WZ','X2','X3','X4') and p.status <> 5 and S.statusID IN (4,5,6,7,8,9,10,13) and S.productid in (23) group by b.code ,p.id , p.firstname,p.lastname,p.MiddleName,p.suffix, p.company,pa.addressline1,pa.addressline2,pa.city, pa.state,pa.zipcode, s.status
I went to View Synchronization status and see that the sychronization has been running for two hours already. Is that normal? I thought it would just find the one change I made and update that one change?
hi, my Backup job, took a backup at 3.00am , backup went well, but this job is still running , now the time is 10.00am. I tried to kill this job but it is still running. Why my process still running after taking a backup also. Can anyone suggest me, how i have to resolve this.
The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.
UPDATE Recipients SET UndeliverableTime = getdate() FROM Recipients INNER JOIN Domains ON (Recipients.DomainID = Domains.ID) INNER JOIN Undeliverables ON ( Recipients.UserName + '@' + Domains.Domain = Undeliverables.EmailAddress)
Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?
Both tables (Recipients and Undeliverables) have approximately 80 million records
I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.
I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?
IÂ setup merge replication about 3 weeks ago between a few databases all running SQL 2008 SP1:
DB1 - This database is the publisher and distributor. DB2 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1. DB3 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1. DB4 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1. DB5 - This database is a read-only subscriber (-EXCHANGETYPE 2) to DB1. DB6 - This database is a read-only subscriber (-EXCHANGETYPE 2) to DB1.
Everything was running fine, then suddenly one at a time the replication jobs for DB2, DB3, and DB4 all started reporting the error:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
I was able to manually upload data from DB2, DB3, and DB4 to DB1 then reinitialize the subscriptions. However, I don't understand why only those three subscriptions failed. I have other merge replication jobs between the same SQL instances that are still running fine. The only unique thing I can determine about the failed subscriptions is that they are write-only. I really don't want to make them read/write, as it would cause a lot of unnecessary network traffic to flow between them.
Based on what I've been able to gather, the subscriptions were marked as expired which caused this problem. If I change subscriptions to never expire to get around this problem, is that going to cause my distribution DB to grow unchecked? Are there other ways to solve this problem?
At a client site we just had a customer away on holidays, came back and went to replicate and received the error. I understand why this would happen after the default 14 days, and the obvious fix to prevent it from happening in the future is to extend the period, however I am hoping someone can offer me assistance on the best way to correct the issue now
Error is:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload).
The problem is we cannot lose the data that has been entered on this machine since the last replication. Writing scripts to manually save and pump this data will take days if not weeks (complex database). Is there any way to issue a command to cause a manual upload of the new information that is at the subscriber. Then I could just delete and recreate replication?
Creating a burn down chart using a running total of cumulative hours with the following formula:
CumulativeHoursLeft:=CALCULATE ( Â Â Â SUM('Projects'[Budget hours]) - SUM ( 'hours'[Hours] ), Â Â Â FILTER ( Â Â Â Â Â Â Â ALL ( 'hours'[Date] ), Â Â Â Â Â Â Â 'hours'[Date] <= MAX ('hours'[Date]) Â Â Â ) )
Works great except that in a Line Chart using [Date] as the Axis and CumulativeHoursLeft as the value, I get these spikes on days for which the employee reported no hours. I do know what exactly the measure is doing in this instance and I do not get this in a table, those dates simply do not appear. I have tried both Categories and Continuous for the Line Chart. I have also tried filtering where [Date] is not blank.how to get rid of the spikes?
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________ mmc.exe has generated errors and will by closed by Windows. You will need to restart the program.
An error log is being created. __________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers. Publisher:MSSQL 2000, SP3 Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when synchronizing with the message "This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image column or different Service Pack Versions - definitely not possible in this case! The only strange detail is that the SQL Server Versionnumber of this MSDE shown via EM is not the same as the number shown in controlpanel->software. By the way, is it ok that here are two lines for the MSDE? SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope... Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623 Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0 Verbindung mit Abonnent 'MyServerAT074' Connecting to Abonnent 'MyServerAT074.MyDB' MyServerAT074.MyDB: {call sp_MSgetversion } Percent Complete: 0 Verbindung mit Verteiler 'MyServer1s' Connecting to Verteiler 'MyServer1s.' MyServer1s.: {call sp_MSgetversion } MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') } MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServer1s' MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServerAT074' MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB', N'MyDB', 9, N'MyDB')} Percent Complete: 0 Initialisiert MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')} Percent Complete: 1 Verbindung mit Verleger 'MyServer1s' Connecting to Verleger 'MyServer1s.MyDB' Connecting to Verleger 'MyServer1s.MyDB' MyServer1s.MyDB: {call sp_MSgetversion } Percent Complete: 3 Ruft Publikationsinformationen ab Percent Complete: 4 Ruft Abonnementinformationen ab Connecting to Abonnent 'MyServerAT074.MyDB' Disconnecting from Abonnent 'MyServerAT074' Percent Complete: 4 Uploadet Datenänderungen zum Verleger Percent Complete: 5 Ruft die Liste der Löschungen von MyServer1s.MyDB ab Percent Complete: 5 Verarbeitet Artikel 'First'
...... SNIP .... Percent Complete: 5 Verarbeitet Artikel 'LastTable' Percent Complete: 0 Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen. Percent Complete: 0 Category:SQLSERVER Source: MyServerAT074 Number: 0 Message: Attributverletzung eingeschränkter Datentypen Attributverletzung eingeschränkter Datentypen Disconnecting from Abonnent 'MyServerAT074' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verteiler 'MyServer1s'
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.
I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3.
I don't know what i am doing wrong.
Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails
I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.
All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases
I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...
if anyone could answer it would be really appreciated
Hi, I am trying to setup bi-directional transactional replication between 2 SQL Servers, I add the subscription on both the servers using the code below.
I have defined 'AnyServer' on both the servers using cliconfg (Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),
After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."
It seems as if loop detection is failing if I keep the same subscriber name on both sides.
It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).
My manager does not want to go through the process of moving 40 GB of data to the subscriber database over the network as would occur during the initialization phase of replication (exact method of replication has yet to be determined). The subscriber db is for Web Access and will be read-only. He wants to back up a db and use tapes to restore the db on the subscriber db server, and then activate replication. I have told him that you have to allow SQL Server to create the objects on the subscriber side and you have to allow SQL Server to control the data flow, that manually creating the objects on the subscriber side will not work. He says that I am incorrect.
Who is right? I haven't been able to find an answer from the Microsoft site and news groups.
I am trying to setup mirrored subcriber in Transactional Replication in SQL 2005. I am not able to do so as it don't work after failover.However I am able to setup mirrored publisher with distribution property "PublisherFailoverPartner" for snapshot and log reader agent profiles and it works even after failover.
However I need to know, do we have something called "Subscriberfailoverproperty" or any other method to set up mirrored subscriber in Transactional Replication in SQL 2005.
I have a central server (CS1) with database CDB and a branch server (BR1) Inside the branch server, there are two database DB1 and DB2. CDB contains data for DB1 and DB2
I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1) I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)
Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..
How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.
Using SQL CE 3.1 merge replication. Working fine, but want to make subscription read only. In other words, master/slave pattern. How is this accomplished?
Hi. I have setup an SQL Server 2005 Merge Replication. Now I need to display the status of the replication programatically from the subscriber side, I have checked the documentation which mentions MergeSubscription & MergeSubscriberMonitor but I couldn't know how to use them!! Any Help ? Thanks
I know that SQL Server Express 2005 can subscribet to SQL Server 2000 publications. However, it seems that SQL server 2000 cannot perform a push to SQL Express.
Can anyone refer me to any online help or documentation on how a proper replication can be done between SQL 2005 Exress and SQL 2000??
I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
Using Merge replication between SQL Server 2000 and SQL Server CE, is there any way that row deletes could occur on the subscriber without a reinitialize or explicit delete of row on publisher.
More specifically, if there is a row filter that returns a days worth of data with each days pull, for example, and the filter looked like select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE()) would there be some implicit delete at subscriber each day because data sent changed?
My research indicates this does not happen, but I have a colleague who thinks differently.
I have setup merge replication between SQL Server 2005 and SQL Server Express. The setup uses web synchronization. (And the setup uses dynamic filter HOST_NAME)
On some of the client machines, replication fails with the following error (when initializing the subscription):
from subscriber merge agent verbose log: A dynamic snapshot will be applied from 'D:DOCUME~1JBAUMG~1.CORLOCALS~1TempDB101-EQA-SCL_BQDB_BQ_PUB_BQDB'
Validating dynamic snapshot 2007-04-1819:39:45.434 OLE DB Subscriber 'FRG-BAUMGARTENJSQLEXPRESS': sys.sp_MSregisterdynsnapseqno @snapshot_session_token=N'\XXX.XX.XX.XXSnapshotuncDB101-EQA-SCL_BQDB_BQ_PUB20070417180079dynsnap', @dynsnapseqno='5599F67E-A1A9-4573-A14F-9851F6FE4B51'
(Note: XXX.XX.XX.XX is a proper IP of DB, I have just masked it in the post)
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file.When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
I enabled verbose logging for the snapshot agent for the subscriber, but there was no error there. It is obvious from the log info above that the error occurs only after downloading the snapshot to the client and during applying it to the subscriber by the merge agent.
After I got this error, I tried running the subscriber program again and it successfully replicated on the third attempt. But this behavior is not consistent and it fails for most of the time on some of the machines
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p> SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
