SQL 2012 :: How To Resolve Data Inconsistent Errors In Merge Replication
Aug 12, 2015How to resolve the data inconsistent errors in merge replication ?
View 0 Replies
ADVERTISEMENT
Errors With Combined Use Of Transactional And Merge Replication - SQL2005
Oct 13, 2006I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
How To Resolve DBCC Errors?
Sep 1, 2002Hi there:
My database has DBCC errors and I am trying to find out the ways to solve it. Any help from you will be highly apprecitated. The error says:
Table Corrupt: Object ID 909325622, index ID 6, page ID (1:864966). The PageId in the page header = (12336:1599554370).Server: Msg 2535, Level 16, State 1, Line 1
Table Corrupt: Page (1:864987) is allocated to object ID 61243273, index ID 3, not to object ID 909390389, index ID 19712 found in page header.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Thanking you,
Eva
DBCC CHECKDB Inconsistent Errors
Mar 28, 2007I'm in the process of trying to cutover SLQ 2000 database activity to a newer used server running Windows 2003. All the latest Win & SQL Service Packs through late Feb 2007 have been installed. The server has a Promise Fast Track S150 TX2plus SCSI raid with 2 80 gb drives. I have been using at as database development system for 3 months without any issues.
This morning I restored the latest full backup and 2 intervening transaction log backups. I am the only user on the system today. I ran DBDCC CHECKDB WITH TABLERESULTS 5 times over a 45 minute period and received 5 different results!
Run 1 - No Error
Run 2 - Following Errors:
2511162Table error: Object ID 754101727, Index ID 0. Keys out of order on page (1:631938), slots 40 and 41.repair_rebuild
8990101CHECKDB found 0 allocation errors and 1 consistency errors in table 'DatabaseEventLog' (object ID 754101727).
8928161Object ID 1218103380, index ID 255: Page (1:373888) could not be processed. See other errors for details.repair_allow_data_loss
8990101CHECKDB found 0 allocation errors and 1 consistency errors in table 'WeatherTransaction' (object ID 1218103380).
Run 3 - Following Errors:
2511162Table error: Object ID 754101727, Index ID 0. Keys out of order on page (1:631938), slots 40 and 41.repair_rebuild
8990101CHECKDB found 0 allocation errors and 1 consistency errors in table 'DatabaseEventLog' (object ID 754101727).
8928161Object ID 1218103380, index ID 255: Page (1:373888) could not be processed. See other errors for details.repair_allow_data_loss
8990101CHECKDB found 0 allocation errors and 1 consistency errors in table 'WeatherTransaction' (object ID 1218103380).
Run 4 - Following Errors:
2511162Table error: Object ID 754101727, Index ID 0. Keys out of order on page (1:631938), slots 40 and 41.repair_rebuild
8990101CHECKDB found 0 allocation errors and 1 consistency errors in table 'DatabaseEventLog' (object ID 754101727).
8928161Object ID 1218103380, index ID 255: Page (1:373888) could not be processed. See other errors for details.repair_allow_data_loss
894116102Table error: Object ID 1218103380, index ID 255, page (1:373888). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 8, offset 0x2 is invalid.repair_allow_data_loss
894216101Table error: Object ID 1218103380, index ID 255, page (1:373888). Test (sorted[i].offset >= max) failed. Slot 0, offset 0x8 overlaps with the prior row.repair_allow_data_loss
8990101CHECKDB found 0 allocation errors and 3 consistency errors in table 'WeatherTransaction' (object ID 1218103380).
Run 5 - No Error!
I ran several of the obligatory CHKDSK and had no errors:
c:>chkdsk
The type of the file system is NTFS.
WARNING! F parameter not specified.
Running CHKDSK in read-only mode.
CHKDSK is verifying files (stage 1 of 3)...
File verification completed.
CHKDSK is verifying indexes (stage 2 of 3)...
Index verification completed.
CHKDSK is verifying security descriptors (stage 3 of 3)...
Security descriptor verification completed.
78116030 KB total disk space.
45291176 KB in 33231 files.
10496 KB in 3135 indexes.
0 KB in bad sectors.
121086 KB in use by the system.
65536 KB occupied by the log file.
32693272 KB available on disk.
4096 bytes in each allocation unit.
19529007 total allocation units on disk.
8173318 allocation units available on disk.
Are these DBDCC CHECKDB WTIH TABLERESULTS differences normal or is this a bug or hardware issue? Any advice you can offer would be appreciated.
Thanks! Brad
SQL 2012 :: Server Merge Replication
Oct 20, 2014I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "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"
My question is:
Is there anyway solve 2 probem? :
1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
2. "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"
SQL 2012 :: Merge Replication Environment
May 10, 2015We are working in a Merge replication environment where we have SQL Server 2005, 11 publications and 2 subscribers.We used to get lot of incidents from the Application owner for blockings, recently we faced a situation where the lead blocker is in sleeping state and the session was used by the merge agent.Checked the query that the session was running, it was sys.sp_MSenumgenerations90;1.
View 1 Replies View RelatedSQL 2012 :: Merge Replication Not Replicating All Changes?
Oct 7, 2015We have a publisher sending data to two remote subscribers. Each of these sites is updating a different field in a particular table with its site name and the current date stamp. This data should then sync to each database to show how up to date the last data change was. This lets us keep an eye on whether sync is good or not.
The problem I've got is one subscriber isn't copying its row to the other servers anymore. It gets the row updates from the other sites in the same table but its own updates to this field aren't getting sent across. Nothing shows up in conflict manager for it and nor should it as no other subscriber should be updating this field. If I validate the subscription the field when then get synced but again no updates after the validation will transfer. The other problem which may be related or indicating another issue is the data transfer rate shown in replication monitor is less than 0.1 rows/sec. Reinitializing isn't an option.
SQL 2012 :: Security For Web Synchronization (Merge Replication)?
Feb 8, 2015I am trying out merge replication and using web synchronization.However, I am worried that I am missing something because the way it is set up, it strikes me as a bit too insecure.
According to the best practices and security articles on Technet, I am given to understand that:
The SQL Replication Listener (read: the application pool account that will be running the replisapi.dll) has to be the db_owner to both distribution and publisher and be on the PAL list. Windows authenication should be used. That means the merge agents wouldn't need to know the password for those logins.
The basic authenication can be used (with SSL) to authenicate into a Windows user account to then connect to the replisapi.dll.
Here's the rub - I assumed that all I needed was a basic no-rights user account to be then given the execute permission on the replisapi.dll & read permissions to kick off the process. When I browse to the replisapi.dll and authenicate using the no-rights user, I get the expected "SQL Server WebSync ISAPI" message. But when I then run the merge agent, it fails saying that login to the distribution failed for the no-rights user. If I use the application pool's account, then I am able to run merge agent successfully.
But that means I am now looking at storing the password to the application pool account on client. I might have had missed a crucial step to ensure that the logins to the distribution & publication databases are done using the application pool account, not the user authenticated via IIS basic authentication?
SQL 2012 :: Merge Replication - Identity Columns
Nov 3, 2015I was reading this:
"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."
I don't understand...
If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?
Suppose I have a this table:
1 Name1
2 Name2
3 Name3
Column 1 is identity field and column 2 the name of employees.
If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?
What about if the identify fields is a primary key?
Inconsistent Errors Using Bulk Insert With A Format File
May 16, 2006As part of a c# program, utilizing .Net 2.0, I am calling a sproc via a SqlCommand to bulk load data from flat files to a various tables in a SQL Server 2005 database. We are using format files to do this, as all of the incoming flat files are fixed length. The sproc simply calls a T-SQL BULK INSERT statement, accepting the file name, format file name and the database table as input paramaters. As expected, this works most of the time, but periodically (to often for a production environment), the insert fails. The particular file to fail is essentially random and when I rerun the process, the insert completes successfully.
A sample of the error messages returned is as follows (@sql is the string executed):
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt".
Starting spRAS_BulkInsertData.
@sql = BULK INSERT Raser.dbo.EMODT3_Work FROM '\RASDMNTTRAS_ROOTAmeriHealthworkpdclmsemodt3.20060511.0915.txt.DATA' WITH (FORMATFILE = '\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt');
The format file for this particular example is as follows (I apologize for the length):
8.0
62
1 SQLCHAR 0 1 "" 1 Record_Type SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "" 2 Vendor_Number SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "" 3 Extract_Subscriber_Number SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "" 4 Extract_Member_Number SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 2 "" 5 Claim_Nbr_Branch_Code SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 8 "" 6 Claim_Nbr_Batch_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "" 7 Claim_Nbr_Batch_Sequence_Nbr SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "" 8 Claim_Nbr_Sequence_Number SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 3 "" 9 LINE_NUMBER SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "" 10 Patient_Sex_Code SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 3 "" 11 Patient_Age SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 4 "" 12 G_L_Posting_Tables_Code SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "" 13 G_L_Posting_Tbls_Code_Desc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 2 "" 14 Fund_TYPE SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "" 15 Stop_Loss_Or_Step_Down_Code SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 2 "" 16 Stop_Loss_Fund SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 50 "" 17 Stop_Loss_Fund_Desc SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "" 18 Post_Date SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "" 19 Rebundling_Status_Indicator SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "" 20 Co_Payment_Grouper SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "" 21 Co_Payment_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 8 "" 22 Co_Payment_Accumulator SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "" 23 Co_Payment_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 8 "" 24 Co_Insurance_Grouper SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "" 25 Co_Insurance_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 8 "" 26 Co_Insurance_Accumulator SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 50 "" 27 CI_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 8 "" 28 Coverage_Grouper SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 50 "" 29 Coverage_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 8 "" 30 Coverage_Accumulator SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 50 "" 31 Coverage_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 8 "" 32 Deductible_Grouper SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "" 33 Deductible_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 8 "" 34 Deductible_Accumulator SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 50 "" 35 Deductible_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 8 "" 36 Unit_Grouper SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 50 "" 37 Unit_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 8 "" 38 Unit_Accumulator SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 50 "" 39 Unit_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 8 "" 40 Out_Of_Pocket_Grouper SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 50 "" 41 Out_Of_Pocket_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 8 "" 42 Out_Of_Pocket_Accumulator SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 50 "" 43 Out_Of_Pocket_Acc_Desc SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 3 "" 44 Service_Edit_Code SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 50 "" 45 Service_Edit_Code_Desc SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 8 "" 46 System_Date_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 8 "" 47 Last_Change_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 10 "" 48 Medicare_Termination_Reason_Code SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 10 "" 49 User_ID_MEDMAS SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 10 "" 50 User_ID_Last_Modified SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 8 "" 51 Adjudication_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 9 "" 52 Adjudication_Time SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 10 "" 53 Adjudication_User_ID SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 9 "" 54 A_P_Batch_Number SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 7 "" 55 A_P_Sequence SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 3 "" 56 CPA_Batch_Number SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "" 57 CPA_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 1 "" 58 Manual_Authorization_Flag SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 50 "" 59 Fund_Description SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 1 "" 60 DRG_Inclusion_Indicator SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 1 "" 61 Future_Expansion SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 2 "
" 62 Company_Number SQL_Latin1_General_CP1_CI_AS
Has anyboy run across this before, or have any ideas as to what might be happening?
Thanks in advance.
SQL 2012 :: Update Query Is Slow With Merge Replication?
Dec 3, 2014I have a database with enabling merge replication.
Then the problem is update query is taking more time.But when I disable the merge triggers then it'll update quickly.
SQL 2012 :: Error In Conflicts Tables In Merge Replication
Feb 13, 2015There is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
publication 436456F0-F5AD-E411-80CF-5CF3FC1D2D76
subscriptionD824D120-23AD-E411-80E3-00155D0E1001
conflict tables 689C6A61-5359-4BB5-BECD-B03F5F94D79A
SQL 2012 :: Initializing Subscription With Web Synchronization (merge Replication)
May 9, 2015I'm trying out a set up as following:
SQL Server 2008 standard as the publisher
SQL Server 2012 express as the subscriber
and I tend to use web synchronization; there is no domain trust.
After following the instructions at MSDN for configuring web synchronization, I have an error that I can't get past - after creating the initial snapshot on the publisher, I try to run replmerg.exe at the subscriber and I always get this error:
"The subscription to publication 'TestReplication' has expired or does not exist."
If I refresh the publisher's "Local Publications" and look within the "TestReplication", it does show that the subscriber is a known subscriber. Likewise, if I refresh subscriber's "Local Subscription", it has an entry for TestReplication publication.
I already verified that the user used by Replisapi.dll has the read permission to the snapshot folder, is a member of PAL, is db_owner of the publishing database and distribution database. I am using self-signed certificate for this test and I have already installed the certificate at the subscriber machine so that HTTPS is trusted. I can run diagnosis from subscriber so I know subscriber can reach and logs are being left at the publisher's IIS.
SQL 2012 :: Complex Filter For Server Merge Replication
Jul 26, 2015We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity,
ParentId int,
GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child
And the filter would look like this:
WHERE GroupId = 200
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
Data Sync Or Merge Replication
Mar 28, 2008thanks 4 ur rpl..well i have a simple doubt if u can guide me it will b gr8 help..well i am developing an application of location based services, in which i am going to store my data on SQLserver 2005 and will fetch the data from it when the user logs in to the user's PDA device so that a user can work offline on the data when there is no connection and when he gets the connection he can synchronize the data with SQLserver 2005. so now i m confuse whether i should use the merge replication or ado.net synchronization framework or RDA?what is the difference between merge replication and synchronization and which is better?
How Is The Data Secured Over The Air In Merge Replication ?
Jan 4, 2007
Dear ppl,
I have a question for you. One of our client has asked us how is the data secured over the air during Replication?
I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.
We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and they all synchronise with a single Publisher SQL Server 2005 using GPRS connection. We haven't got any SSL certificates installed on the server.
Now how can i make use of TLS in my application to secure my data over the air (using TLS)?
Regards
Nabeel Farid
Data Synchronization Through Merge Replication
Apr 1, 2008I am designing a mobile application.I am having database in sql server 2005 and i want to synchronize this database with sql server ce database on pda.I am using merge replication method for synchronization.Please give step by step procedure for data synchronization using merge replication between sql server 2005 and sql server ce 2.0,also let me know whether sql server enterprise manager is needed or will it work without it,if needed give me the link from where to download it as it is not available in sql server 2005.
Reply soon.
Thanks in advance.
Data Loss With Merge Replication?
Jun 27, 2006We had merge replication setup between 2 tables, Table A and Table B using SQL 2000. This was working 100%. The users asked to disable updated/deletes to both these tables if data existed on 2 other tables. Table AA and Table BB. We implemented it as follows:
1) Created Insert/Update/Delete triggers for Table A & B. It basically check for Table A is there a record in Table AA, if it exists, raise an error and don€™t commit.
2) Removed all foreign constraints from Table AA and BB
3) Added Table AA and BB to the current replication.
Then all hell broke loose, we got conflicts all other the place saying that Table AA cannot be updated because records does not exist in Table X. To our surprise we found triggers generated by Erwin in 1998 €“ that check for €œforeign contsraints€? and removed them immediately.
We continued to get conflicts but could see from the error messages it was generated by the triggers in point 1. We added the NOT FOR REPLICATION clause and everything has been running smoothly or so we thought€¦..
After 2 months we got a call that data is missing. It€™s random data and the only explanation I have is that replication caused that. My biggest reason for saying this is tracking the application audit trail I€™ve found that all the data missing was added during the period we had all the conflicts.
I need a solid explanation for this and can anyone confirm that this is possible?
Loading Data At Both Ends Of A Merge Replication?
Feb 9, 2007Hi gang,
We have a database that's using merge replication between two servers, and we need to insert a lot (about 1GB) of data into it.
The servers, however, are separated by a 192k WAN connection, so it's impractical to rely on the merge replication to send the data across to the subscriber.
Is there a way to insert the data at both ends? I can get the data out there on a DVD or a laptop easily enough. Can I load the data into both copies of the database and tell the merge agent that it's not to be replicated?
Thanks,
Matt
Merge Replication Reinitialization Deleted All Data
May 3, 2007Hello,
When I reinitialized a subscription from SQL2005 db (publisher) to a SQL 2000 DB (Subscriber) and had the Upload changes before reinitialization turned on, the data from the SQL 2000 db didn't get uploaded. This resulted in a loss of 2 days worth of data.
Does anyone have any idea if there's anywhere that data is kept. (unfortunately a series of errors caused our backups to not be running on the 2000 db either).
Thank You
Bob
Merge Replication Downloading Old Data To The Suscriber
Oct 16, 2006I'm having an error with merge replication my suscriber are downloading old data, all this start happen after I install SP4.
let explaint this with more details ,
if a made an update to a row in a table from the publisher , the agent history show that 1 change have downloaded to the suscriber, a put an audit in the suscriber and yes one update was downloaded but the row still the same, look like the suscriber downloaded old data instead of the curent change that i made.
If i delete the row, the rows gets delete at the suscriber, if a insert the row the row get insterted ok.
The problem only happen with rows inserted at the suscriber, that later on get update at the publisher. My suscriber has MSDE 2000 SP3 an my Server has SQL Enterprice 2000 SP4.
Any help would be apreciate. thxs.
Merge Replication - One Way Data Flow - Help Needed...
Apr 24, 2007Hi,
We have configured one-way merge replication in our topology. That is data flows from Subscriber to Publisher only. We have a publisher and a subscriber. There are 3 publications in this category and each publication has a subscription. We use SQL Server 2005 SP1 in both the servers. The retention period is the 14 days (default). After this period, I get the following error in the subscription in Replication Monitor. The Error message is
Error messages:
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 read the post http://forums.microsoft.com/MSDN/ShowPost.aspxPostID=372790&SiteID=1
which said that this error might be solved in the SP2. We have not yet applied SP2, but even after applying SP2, will this error be solved for One Way Merge Replication since the data from publisher will not go to the subscriber always in this type of topology??.
Kindly get back to us regarding this as soon as possible. Thanks in advance.
Regards,
Swapna.B.
Merge Replication Between Same Schema Databases But Different Data
Jan 25, 2007Hi all.
I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later time and replication was paused, but users continout to insert data indepentedly at the two servers.)
I need to get them up and running.
I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.
When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .
I so far managed to have merge replication running on two identical databases (schema and data).
Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes. I set the identity seed and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at puplisher and subscriber.) Is that ok? is that the way to do it?
Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.
Thanks allot for any help
Version.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Transfer Data Between SQL CE AND SQL SERVER Without Merge Replication
Jan 25, 2008I've got a problem:
I want to transfer data between an sql server 2005 and my pocket pc. So i've installed a sql server CE on it.
I know that i can use the merge replication but, i don't have any IIS. So, i've got to transmit data though email that have got a file attached. I Know how to email from sql server 2005. When the pocket receive that email, i don't know how to insert the data in sql ce from outlook compact. Doi have tu use SSIS , in that case how can i run the package?
thanks
Replication :: Merge Data Cannot Sync To Subscriber
Jun 29, 2015I have the merge replication (Push)Â for SQL Server 2008.The right-click option for Start and Stop Synchronizing in subscriptions has been disabled. How can I stop the merge replication and start again ?
This error happened 1 week after I created the merge replication. The merge data cannot sync to subscriber and only solution I can use is drop and re-create the merge replication again but error would be re-occur a week later. The merge replication work fine before and start from middle on May 2015, but it keep happen right now.
SQL 2012 :: How To Merge Data Into One Row
Jun 24, 2015I have a query that pulled data in the following format:
First_Name Last_Name Drug Reason
Jim Smith Aspirin
Jim Smith Headache
Here's what I would like for it to display:
Jim Smith Aspirin Headache
Our fields and are weird. I had to use a case statement to get drugs and reason.
Would putting a max on them put everything on one row?
SQL 2012 :: Merge Data Into One Row?
Nov 3, 2015I am currently writing a query to show the quote number and its brand names inside that quote which should be separated with "/" if there are different brands in a quote. see below.
QuoteDetail Table:
QuoteNum | Brand
10047 | NISSAN
[Code]....
find the correct query. I tried some functions which I found in the internet but it didn't work.
In Merge Replication How To Insert A Data??? Program Is Getting Stuck
Apr 19, 2008Hi all,
there are 2 datagrids i have taken,
1st for displaying data before inserting the value into table
2nd for displaying data after inserting the value into table
just see the screeenshot which i have taken
actually the problem is, we are not getting any type of errors even though I have try/catch blocks.
I have colored that particular statement in RED color, where it is getting stuck, while executing, if we sit 40-45 minutes then also the execution pointer is not going on next step.
please look into this, and if u have any idea/person by which/whom we can solve this then please tell us.......
-chaukse rahul
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Data.SqlServerCe;
using System.Data.Common;
namespace ProjectSQLMobile2
{
public partial class Form1 : Form
{
private DataSet dsMemberList;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://169.254.25.129/WebSQLMobile/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"sa12345";
repl.Publication = @"PubSQLMobile";
repl.Subscriber = @"SubSQLMobile";
repl.SubscriberConnectionString = @"Data Source=TestLast.sdf;Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(@"TestLast.sdf"))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
/*
*
* To Display Data Into DataGrid1 Before Insert
*
*/
SqlCeConnection cn = new SqlCeConnection(@"Data Source=TestLast.sdf");
SqlCeDataAdapter daMemberList = new SqlCeDataAdapter("SELECT MemberID, MemberName FROM MembershipData", cn);
if (dsMemberList == null)
{
dsMemberList = new DataSet();
}
try
{
dsMemberList.Clear();
daMemberList.Fill(dsMemberList, "MembershipData");
dataGrid1.DataSource = dsMemberList.Tables["MembershipData"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void Merge()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://169.254.25.129/WebSQLMobile/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"sa12345";
repl.Publication = @"PubSQLMobile";
repl.Subscriber = @"SubSQLMobile";
repl.SubscriberConnectionString = @"Data Source=TestLast.sdf;Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(@"TestLast.sdf"))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
/*
*
* To Display Data Into DataGrid2 After Insert
*
*/
SqlCeConnection cn = new SqlCeConnection(@"Data Source=TestLast.sdf");
SqlCeDataAdapter daMemberList = new SqlCeDataAdapter("SELECT MemberID, MemberName FROM MembershipData", cn);
if (dsMemberList == null)
{
dsMemberList = new DataSet();
}
try
{
dsMemberList.Clear();
daMemberList.Fill(dsMemberList, "MembershipData");
dataGrid2.DataSource = dsMemberList.Tables["MembershipData"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void buttonInsertData_Click(object sender, EventArgs e)
{
//add directly into ce database sample:
SqlCeConnection cn = new SqlCeConnection(@"Data Source=""TestLast.sdf""");
string SQL = "INSERT INTO MembershipData(MemberName) VALUES('" + textBox1.Text + "')";
cn.Open();
SqlCeCommand cmd = new SqlCeCommand(SQL, cn);
cmd.CommandType = CommandType.Text;
try
{
/*
* This statement is taking too much time to be executed * what should we do??????? */ cmd.ExecuteNonQuery(); }
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
cn.Close();
}
Merge();
}
}
}
please help,
ur help will be appriciates
NOTE:- I am getting output in 1st datagrid, but in 2nd dtagrid there is no output
SQL Mobile Fails To Send Data To IIS - Merge Replication
Nov 22, 2006This is just too simple. I must be doing something wrong. I have tried everything, redoing the SQLMobile merge publication, setting permissions, and need help ASAP.
The basic test with the http://localhost/sqlmobile/sqlcesa30.dll returns the message:
SQL Server Mobile Server Agent 3.0
I have tried the merge replication with both my local SQL Server 2005 installation and a remote development box. Both instances return the same error.
I am able create and replicate a database using the sample application:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
However, another simple applications fails to synchronize when simply using the following code block:
Dim repl As New SqlCeReplication()
repl.InternetUrl = "http://xens/sqlmobile/sqlcesa30.dll"
repl.Publisher = "xena"
repl.PublisherDatabase = "SQLMobile"
repl.PublisherSecurityMode = SecurityType.NTAuthentication
repl.Publication = "SQLMobile"
repl.Subscriber = "sqlmobile"
repl.SubscriberConnectionString = _
"Data Source='" + filename + "';Password='';" _
& "Max Database Size='128';Default Lock Escalation ='100';"
repl.AddSubscription(AddOption.CreateDatabase)
repl.Synchronize()
Here is the error message:
Error Code: 80072EE7
Message: A request to send data to the computer running IIS has failed. For more information, see HRESULT.
Minor Err. 28037
Source Microsoft SQL Server 2005 Mobile Edition
--------------------------------------------------------------------------------------
Here is the full code sample:
Imports System.Data.SqlServerCe
Imports System.Text
Public Class Form1
Private engine As SqlCeEngine
Private repl As New SqlCeReplication()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
engine = New SqlCeEngine()
engine.LocalConnectionString = "Data Source=""sqlmobile.sdf"";Max Database Size=128;Default Lock Escalation =100;"
engine.CreateDatabase() <- First method creates database
engine.Dispose()
engine = Nothing
repl.InternetUrl = "http://xena/sqlmobile/sqlcesa30.dll"
repl.Publisher = "xenaSQL2005"
repl.PublisherDatabase = "SQLMobile"
repl.PublisherSecurityMode = SecurityType.NTAuthentication
repl.Publication = "SQLMobile"
repl.Subscriber = "SQLMobile"
repl.SubscriberConnectionString = "Data Source=""sqlmobile.sdf"";Max Database Size=128;Default Lock Escalation =100;"
Try
'repl.AddSubscription(AddOption.CreateDatabase) <- this method fails also
repl.AddSubscription(AddOption.ExistingDatabase)
repl.Synchronize() <-- Code fails here with the error message shown.
Catch err As SqlCeException
ShowErrors(err)
'MessageBox.Show(err.ToString)
End Try
End Sub
Public Sub ShowErrors(ByVal e As SqlCeException)
Dim errorCollection As SqlCeErrorCollection = e.Errors
Dim bld As New StringBuilder
Dim inner As Exception = e.InnerException
Dim err As SqlCeError
If Not inner Is Nothing Then
MessageBox.Show(("Inner exception: " & inner.ToString()))
End If
' Enumerate each error to a message box.
For Each err In errorCollection
bld.Append((ControlChars.Cr & " Error Code: " & err.HResult.ToString("X")))
bld.Append((ControlChars.Cr & " Message : " & err.Message))
bld.Append((ControlChars.Cr & " Minor Err.: " & err.NativeError))
bld.Append((ControlChars.Cr & " Source : " & err.Source))
' Retrieve the error parameter numbers for each error.
Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If 0 <> numPar Then
bld.Append((ControlChars.Cr & " Num. Par. : " & numPar))
End If
Next numPar
' Retrieve the error parameters for each error.
Dim errPar As String
For Each errPar In err.ErrorParameters
If [String].Empty <> errPar Then
bld.Append((ControlChars.Cr & " Err. Par. : " & errPar))
End If
Next errPar
MessageBox.Show(bld.ToString())
bld.Remove(0, bld.Length)
Next err
End Sub
End Class
Thanks in advance....
How To Speedup Data From Subscriber To Publisher On Merge Replication?
Mar 9, 2007How to speedup data from subscriber to Publisher on Merge replication?
Is there any we can speed up data from Subscriber to Publisher (uploading).
Subscriber downloading data from publisher running more than 20 hours after drop and recreate subscriber.
Could you please post reply.
Regards
Ponnu
Urgent - Change Data Type In Merge Replication Setup
Jul 7, 2004Hi
I am new to Sql server and had just finished the MErge Replication setup on one of the PRoduction server. Today I got the request to change one of the Datatype of one the Published Article. Please help as what are the correct step to make it happen. Is there any production downtime required and if yes , then how much.
Thanks in Advance
Sanjay
How To Access Or Modify Local Subscriptions Data In Merge Replication
Apr 3, 2007Hello,
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
Thank you
Salah
SQL 2012 :: How To Resolve IP Conflict Error While Server Clustering
Dec 8, 2014I am practicing on vmware 2 node failover cluster for doing Sql failover clustering understanding.I did proper Ad Dc setup and configured san storage through iscsi software that I downloaded from net.I did target and initiator configuration for san storage on same Domain controller vm.Name of target is Target1 and has two initialtor for node1 and node2.It has two virtual disk assigned Quorumdisk.vhd and sqldisk.vhd.ON vmnode1 I have initialzed and formatted my both disk with proper volume and label.
Node which are participating in cluster are node1 and node2.while my windows clustering went fine,while Sql server installation on node 1,i got error IP Address conflict occured.
my DC has IP address:192.168.1.10
my Node1 is 192.168.1.20
node2:192.168.1.30
Cluster IP address is 192.168.1.35
What has cause this situation.What configuration is needed.How do I troubleshoot this problem as now I can not install my sql for clustering as also gave me Invalid network name error.Is my storage configuration not proper or IP address is in correct?Do I need to put my virtual disk in Clustered shared volume?