Good Day,
After running Merge Snapshot Subscription Job No BCP files are created
for the data tables:
here is my code for the Merge Articles:
Dim lbResult As Boolean
Dim lobjMArt As MergeArticle2
Dim lbHasIdentity As Boolean
Set lobjMArt = New MergeArticle2
lobjMArt.Name = asTableName
lobjMArt.SourceObjectName = asTableName
lobjMArt.SourceObjectOwner = "dbo"
UpdateLog "CreationScriptOptions Default Value is: " &
lobjMArt.CreationScriptOptions
If abHasIdentity Then
lobjMArt.AutoIdentityRange = True
lobjMArt.PublisherIdentityRangeSize = 1000000
lobjMArt.SubscriberIdentityRangeSize = 1000000
lobjMArt.IdentityRangeThreshold = 85
End If
pobjSQLDMOMerge.MergeArticles.Add lobjMArt
Set lobjMArt = Nothing
lbResult = True
The Job History Shows:
bulk copying snapshot data for article [TableName] (0 rows).
This is shown for all tables. 90 % of my tables contain records. For
Example the Patient Table has 675 records.
What am i Missing?
Thanks In Advance.
Brian
The agent just ran and created the bcp files.
The agent is scheduled to run @. 12:00 am. Why did it work through
scheduled execution and not through code.
Am I missing something from this code:
Dim lbResult As Boolean
Dim lsErrSource As String
Dim lsJobID As String
Dim liX As Long
Dim lobjDis As SQLDMO.Distributor2
Dim lobjDisPubs As SQLDMO.DistributionPublishers
Dim lobjDisPub As SQLDMO.DistributionPublication2
Dim lobjJobs As SQLDMO.Jobs
Dim lobjJob As SQLDMO.Job
Dim loJobHis As SQLDMO.JobHistoryFilter
Dim loQR As SQLDMO.QueryResults2
Dim liY As Integer 'Rows
UpdateLog "Refresh Snapshot Function"
'===Load an Default Vars
lsErrSource = "clsPublisher.RefreshSnapShot"
'===Parse the Connection String:
If ParseConnectionString(asConnect) Then
If ConnectToServer() Then
'pobjSQLServer now equals the Sever object.
'We need to get a copy of the Publication DB.
Set lobjDis = pobjSQLServer.Replication.Distributor
Set lobjDisPubs = lobjDis.DistributionPublishers
'Set lobjDisPub =
lobjDis.DistributionPublishers(psPubServer).Distri butionPublications.Item("Promise:Promise")
Set lobjDisPub =
lobjDisPubs(psPubServer).DistributionPublications. Item(1)
UpdateLog ("DisPub Name: " &
lobjDisPubs(psPubServer).DistributionPublications. Item(1).Name)
lsJobID = lobjDisPub.SnapshotJobID
psJobName = lobjDisPub.SnapshotAgent
Set lobjDisPub = Nothing
Set lobjDisPubs = Nothing
Set lobjDis = Nothing
Set lobjJobs = pobjSQLServer.JobServer.Jobs
'Get the Job to Start it.
UpdateLog ("Job Info: ID:" & lsJobID & " Name: " &
psJobName)
Call SaveSetting("Horizon Healthware Inc", "Promise",
"ReplicationJobName", psJobName)
Set lobjJob = lobjJobs.Item(psJobName)
UpdateLog ("Job: Starting Job.")
lobjJob.Invoke
liX = 0
lobjJob.Refresh
Do While lobjJob.CurrentRunStatus <>
SQLDMOJobExecution_Idle
liX = liX + 1
If liX = 200000 Then
lobjJob.Refresh
DoEvents
liX = 0
End If
Loop
' loJobHis.JobName = asJobName
' loJobHis.OldestFirst = False
'
' Set loQR =
pobjSQLServer.JobServer.EnumJobHistory(loJobHis)
'
' liX = 1
'
'
' For liX = 1 To loQR.Columns
' 'UpdateLog ("Job History ColumnName :" &
loQR.ColumnName(liX) & " Value:" & loQR.GetColumnString(1, liX))
'
' Next
Set loQR = Nothing
Set loJobHis = Nothing
Set lobjJob = Nothing
Set lobjJobs = Nothing
Call DisconnectFromServer
lbResult = True
Else
Err.Raise hhwErrorNum.lErrUnableToConnectToServer,
lsErrSource, ptErrorDesc.sErrParsingConnectionString
End If
Else
lbResult = False
Err.Raise hhwErrorNum.lErrParsingConnectionString, lsErrSource,
ptErrorDesc.sErrUnableToConnectToServer
End If
Thanks
Brian
sql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
merge replications causes insert timeout
Hi All,
i need help. i have a merge replication running every 10 minutes. the replication is for 1 database consisting around 50 tables and all 50 tables are marked as article to be published. let's say the publisher is server A and the subscriber is server B. for explanation purpose, let's pick 1 table from the database, let's say table X
in server A, there are applications running that insert and update table X. in server B, there are applications running that select and delete table X. table X at average, has tens of thousands rows.
now comes the problem, every 10 minutes whenever the sql merge agent is running, sometimes whenan application insert a row to table X in server A, it returns an error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". when i stop the replication (delete the subscriber), the row insertion to table X in server A seems fine. then i come to a doubtful conclusion, that the timeout error happened because when the insert statement executes, the replication locks the table for merging.
could somebody verify this? if my conclusion is true, then how am i suppose to do this? (see bold text above). really2 appreciate the help
Yes, merge agent can fail due to deadlocking. WHat you need to ensure is that your application is taking the minimum locks necessary. This is no different than two applications accessing/modifying the same data simultaneously, consider merge replication a second application.
Review your queries, insert/update/delete statement and see how transactions are being used, what locking behavior, queries are written optimally, etc.
Merge Replication? Aaaarghhh!
Can anyone tell me what should be contained in MSrepl_identity_range tables
on both the subscriber and the publisher? Both my tables contain completly
different data - pub has 83 rows and my single sub has only 3.
When are these tables populated and how? Can I populate them manually from a
system SP?
I refer to my previous post where Hilary Cotter thought there might be an
issue with these tables.
When executing : exec sp_MSfetchidentityrange N'CommentType', 0
I get the following error:
Server: Msg 21195, Level 16, State 1, Procedure
sp_MSfetchAdjustidentityrange, Line 92
A valid identity range is not available. Check the data type of the identity
column.
Thanks in advance...
Chris,
this is a bit more complicated than it seems...
I have had cause to manually change the identity range on a subscriber - I'm
not recommending it but it did lead to a better understanding of the
mechanism involved!
If you are using automatic range management this'll be taken care of when
you synchronize (run the merge agent). However, if it is not possible for
you to connect to the publisher, you could manually update
MSrepl_identity_range on the subscriber. This table is used to check if the
subscriber has used up its range or reached the threshold. The new range you
set would be obtained from MSrepl_identity_range on the distributor, which
is the master table and is used to generate new values. The values in this
table (MSrepl_identity_range on the distributor) would need to be changed to
avoid a future potential conflict. Finally, the check constraints on the
subscriber would need updating accordingly.
As an aside, note that there are some anomalies with automatic range
management: the first range is twice the requested size and the actual range
of values enforced by the check constraint is always one less than the size
selected - SQL Server 2005 managed identities for merge replication has been
redesigned to be more consistent.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul. Thanks
I ended up removing replication from the DB and reinstating. I'm now having
a problem with creating the publication from a generated script! See later
post.
Thanks anyway for you help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239GGiaZrFHA.2996@.tk2msftngp13.phx.gbl...
> Chris,
> this is a bit more complicated than it seems...
> I have had cause to manually change the identity range on a subscriber -
> I'm not recommending it but it did lead to a better understanding of the
> mechanism involved!
> If you are using automatic range management this'll be taken care of when
> you synchronize (run the merge agent). However, if it is not possible for
> you to connect to the publisher, you could manually update
> MSrepl_identity_range on the subscriber. This table is used to check if
> the subscriber has used up its range or reached the threshold. The new
> range you set would be obtained from MSrepl_identity_range on the
> distributor, which is the master table and is used to generate new values.
> The values in this table (MSrepl_identity_range on the distributor) would
> need to be changed to avoid a future potential conflict. Finally, the
> check constraints on the subscriber would need updating accordingly.
> As an aside, note that there are some anomalies with automatic range
> management: the first range is twice the requested size and the actual
> range of values enforced by the check constraint is always one less than
> the size selected - SQL Server 2005 managed identities for merge
> replication has been redesigned to be more consistent.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
on both the subscriber and the publisher? Both my tables contain completly
different data - pub has 83 rows and my single sub has only 3.
When are these tables populated and how? Can I populate them manually from a
system SP?
I refer to my previous post where Hilary Cotter thought there might be an
issue with these tables.
When executing : exec sp_MSfetchidentityrange N'CommentType', 0
I get the following error:
Server: Msg 21195, Level 16, State 1, Procedure
sp_MSfetchAdjustidentityrange, Line 92
A valid identity range is not available. Check the data type of the identity
column.
Thanks in advance...
Chris,
this is a bit more complicated than it seems...
I have had cause to manually change the identity range on a subscriber - I'm
not recommending it but it did lead to a better understanding of the
mechanism involved!
If you are using automatic range management this'll be taken care of when
you synchronize (run the merge agent). However, if it is not possible for
you to connect to the publisher, you could manually update
MSrepl_identity_range on the subscriber. This table is used to check if the
subscriber has used up its range or reached the threshold. The new range you
set would be obtained from MSrepl_identity_range on the distributor, which
is the master table and is used to generate new values. The values in this
table (MSrepl_identity_range on the distributor) would need to be changed to
avoid a future potential conflict. Finally, the check constraints on the
subscriber would need updating accordingly.
As an aside, note that there are some anomalies with automatic range
management: the first range is twice the requested size and the actual range
of values enforced by the check constraint is always one less than the size
selected - SQL Server 2005 managed identities for merge replication has been
redesigned to be more consistent.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul. Thanks
I ended up removing replication from the DB and reinstating. I'm now having
a problem with creating the publication from a generated script! See later
post.
Thanks anyway for you help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239GGiaZrFHA.2996@.tk2msftngp13.phx.gbl...
> Chris,
> this is a bit more complicated than it seems...
> I have had cause to manually change the identity range on a subscriber -
> I'm not recommending it but it did lead to a better understanding of the
> mechanism involved!
> If you are using automatic range management this'll be taken care of when
> you synchronize (run the merge agent). However, if it is not possible for
> you to connect to the publisher, you could manually update
> MSrepl_identity_range on the subscriber. This table is used to check if
> the subscriber has used up its range or reached the threshold. The new
> range you set would be obtained from MSrepl_identity_range on the
> distributor, which is the master table and is used to generate new values.
> The values in this table (MSrepl_identity_range on the distributor) would
> need to be changed to avoid a future potential conflict. Finally, the
> check constraints on the subscriber would need updating accordingly.
> As an aside, note that there are some anomalies with automatic range
> management: the first range is twice the requested size and the actual
> range of values enforced by the check constraint is always one less than
> the size selected - SQL Server 2005 managed identities for merge
> replication has been redesigned to be more consistent.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Merge replication?
We have a SQL Server db in Atlanta. Rows are added to tables in that
database by a website in Atlanta. We have a number of people who use
that data in Dallas and they need to be able to modify the data. This
scheme works okay except when the Internet connection between the two
sites goes down.
So we are looking to replicate the database in Dallas. From the little
I've read, Merge replication would probably be our best bet. However,
I've never worked with replication so I'm unsure. I'd like some
resources either online or books that would help me understand the
process better and help me in implementing such a scheme. Thanks!
-John
Merge might work, bi-directional transactional is another option.
In general if you go offline frequently, your chances for generating
conflicts increase. Merge has conflict tracking mechanisms, where if a
conflict occurs it is logged to conflict tables, and the publisher or
subscriber (your choice) wins. You can roll back this conflict. With
bi-directional transactional replication your distribution agent will fail
and you will have to manually fix the problem. Depending on your data flow
this can involve a single row or thousands.
Bi-directional transactional replication is not resilient to schema changes.
Merge replication is to a degree. Transactional replication is also easier
to troubleshoot. Merge will add a tracking columns to each table you are
replicating. Bi-directional transactional replication does not.
Most dba's would pick merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:n5hus0tfqmr4sgudepget0o5ebhaer8nel@.4ax.com...
> We have a SQL Server db in Atlanta. Rows are added to tables in that
> database by a website in Atlanta. We have a number of people who use
> that data in Dallas and they need to be able to modify the data. This
> scheme works okay except when the Internet connection between the two
> sites goes down.
> So we are looking to replicate the database in Dallas. From the little
> I've read, Merge replication would probably be our best bet. However,
> I've never worked with replication so I'm unsure. I'd like some
> resources either online or books that would help me understand the
> process better and help me in implementing such a scheme. Thanks!
>
> -John
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
>Merge might work, bi-directional transactional is another option.
>In general if you go offline frequently, your chances for generating
>conflicts increase. Merge has conflict tracking mechanisms, where if a
>conflict occurs it is logged to conflict tables, and the publisher or
>subscriber (your choice) wins. You can roll back this conflict. With
>bi-directional transactional replication your distribution agent will fail
>and you will have to manually fix the problem. Depending on your data flow
>this can involve a single row or thousands.
>Bi-directional transactional replication is not resilient to schema changes.
>Merge replication is to a degree. Transactional replication is also easier
>to troubleshoot. Merge will add a tracking columns to each table you are
>replicating. Bi-directional transactional replication does not.
>Most dba's would pick merge replication.
We do not go offline frequently, except the past 2 weeks which has
made people scream. I don't think that we will have too many conflicts
because the one location mostly adds records and the other modifies
existing records. Is there a book that you would recommend?
-John
|||I'd probably use bi-directional transactional replication, as long as you
are going to be modifying the schema much.
It is more difficult to set up, and if you are a high volume database you
will have to watch your transaction log growth.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:um90t0de0n4rp3dp2fsu86t988sdhnm3i2@.4ax.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
fail[vbcol=seagreen]
flow[vbcol=seagreen]
changes.[vbcol=seagreen]
easier
> We do not go offline frequently, except the past 2 weeks which has
> made people scream. I don't think that we will have too many conflicts
> because the one location mostly adds records and the other modifies
> existing records. Is there a book that you would recommend?
> -John
>
database by a website in Atlanta. We have a number of people who use
that data in Dallas and they need to be able to modify the data. This
scheme works okay except when the Internet connection between the two
sites goes down.
So we are looking to replicate the database in Dallas. From the little
I've read, Merge replication would probably be our best bet. However,
I've never worked with replication so I'm unsure. I'd like some
resources either online or books that would help me understand the
process better and help me in implementing such a scheme. Thanks!
-John
Merge might work, bi-directional transactional is another option.
In general if you go offline frequently, your chances for generating
conflicts increase. Merge has conflict tracking mechanisms, where if a
conflict occurs it is logged to conflict tables, and the publisher or
subscriber (your choice) wins. You can roll back this conflict. With
bi-directional transactional replication your distribution agent will fail
and you will have to manually fix the problem. Depending on your data flow
this can involve a single row or thousands.
Bi-directional transactional replication is not resilient to schema changes.
Merge replication is to a degree. Transactional replication is also easier
to troubleshoot. Merge will add a tracking columns to each table you are
replicating. Bi-directional transactional replication does not.
Most dba's would pick merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:n5hus0tfqmr4sgudepget0o5ebhaer8nel@.4ax.com...
> We have a SQL Server db in Atlanta. Rows are added to tables in that
> database by a website in Atlanta. We have a number of people who use
> that data in Dallas and they need to be able to modify the data. This
> scheme works okay except when the Internet connection between the two
> sites goes down.
> So we are looking to replicate the database in Dallas. From the little
> I've read, Merge replication would probably be our best bet. However,
> I've never worked with replication so I'm unsure. I'd like some
> resources either online or books that would help me understand the
> process better and help me in implementing such a scheme. Thanks!
>
> -John
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
>Merge might work, bi-directional transactional is another option.
>In general if you go offline frequently, your chances for generating
>conflicts increase. Merge has conflict tracking mechanisms, where if a
>conflict occurs it is logged to conflict tables, and the publisher or
>subscriber (your choice) wins. You can roll back this conflict. With
>bi-directional transactional replication your distribution agent will fail
>and you will have to manually fix the problem. Depending on your data flow
>this can involve a single row or thousands.
>Bi-directional transactional replication is not resilient to schema changes.
>Merge replication is to a degree. Transactional replication is also easier
>to troubleshoot. Merge will add a tracking columns to each table you are
>replicating. Bi-directional transactional replication does not.
>Most dba's would pick merge replication.
We do not go offline frequently, except the past 2 weeks which has
made people scream. I don't think that we will have too many conflicts
because the one location mostly adds records and the other modifies
existing records. Is there a book that you would recommend?
-John
|||I'd probably use bi-directional transactional replication, as long as you
are going to be modifying the schema much.
It is more difficult to set up, and if you are a high volume database you
will have to watch your transaction log growth.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:um90t0de0n4rp3dp2fsu86t988sdhnm3i2@.4ax.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
fail[vbcol=seagreen]
flow[vbcol=seagreen]
changes.[vbcol=seagreen]
easier
> We do not go offline frequently, except the past 2 weeks which has
> made people scream. I don't think that we will have too many conflicts
> because the one location mostly adds records and the other modifies
> existing records. Is there a book that you would recommend?
> -John
>
Merge Replication: transaction log growing too fast
The merge replication running continuously between two servers (sql server
7.0) on a 100MB database with 300 tables. The transaction log size grows to
more than 100MB every hour, even though there are no data changes. Is this
normal?
BTW, I am using 'PUSH to Subscriber' and both Publisher and Subscriber's
transaction log has same issue.
|||Madan,
something very fishy here - can you run profiler to see what is happening
behind the scenes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||--from transaction log using log explorer--
Table Insert Delete
Modify
dbo.MSmerge_genhistory 10600 10600 0
dbo.sysmergearticles 0 0
2120
Even thought there were no data changes, MSmerge_genhistory and
sysmergearticles tables were busy doing something.
I also turned off the 'auto update stats' and 'auto create stats', and
results still the same.
|||Paul/Hillary any idea? Please help.
7.0) on a 100MB database with 300 tables. The transaction log size grows to
more than 100MB every hour, even though there are no data changes. Is this
normal?
BTW, I am using 'PUSH to Subscriber' and both Publisher and Subscriber's
transaction log has same issue.
|||Madan,
something very fishy here - can you run profiler to see what is happening
behind the scenes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||--from transaction log using log explorer--
Table Insert Delete
Modify
dbo.MSmerge_genhistory 10600 10600 0
dbo.sysmergearticles 0 0
2120
Even thought there were no data changes, MSmerge_genhistory and
sysmergearticles tables were busy doing something.
I also turned off the 'auto update stats' and 'auto create stats', and
results still the same.
|||Paul/Hillary any idea? Please help.
Merge Replication: Content of MSmerge_* Tables
Hi All,
I have set up MERGE Replication with NOSYNC option and 14 days subscription
retention period.
My both the servers are in sync with each other since when I started
replication a month back.
But I find there are thousands of records in MSmerge_contents,
MSmerge_tombstone, MSmerge_genhistory.
I want to know how to get rid of all the unwanted entries from the above
tables.
Regards
Javed Iqbal
Javed,
please run sp_mergemetadataretentioncleanup and see if this helps.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul
Thanks for your reply.
This stored proc is already been running by the merge agent every minute.
Any other option is highly appreciated.
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elzAKGIBGHA.628@.TK2MSFTNGP10.phx.gbl...
> Javed,
> please run sp_mergemetadataretentioncleanup and see if this helps.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also I am getting these types of errors
The merge process could not perform retention-based meta data cleanup in
database 'ABC'.
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elzAKGIBGHA.628@.TK2MSFTNGP10.phx.gbl...
> Javed,
> please run sp_mergemetadataretentioncleanup and see if this helps.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
you could try increasing the merge agent's querytimeout.
Also, you could try reindexing the metadata tables.
If this doesn't help, please enable logging on your merge agent:
http://support.microsoft.com/support.../q312/2/92.asp
and post up the log file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Attached is my agent logging file. I still have thousands of records into
those tables.
Thanks in advance
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SBHyfJBGHA.2708@.TK2MSFTNGP12.phx.gbl...
> Javed,
> you could try increasing the merge agent's querytimeout.
> Also, you could try reindexing the metadata tables.
> If this doesn't help, please enable logging on your merge agent:
> http://support.microsoft.com/support.../q312/2/92.asp
> and post up the log file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
begin 666 Merge_Replication_Verbose_Logging_For_Errors.zip
M4$L#!!0````(`(5CE3-[['W5.0X``*/I`0`P````365R9V5?4F5P;&EC871I
M;VY?5F5R8F]S95],;V=G:6YG7T9O<E]%<G)O<G,N='AT[9UK3^-(%H:_1\I_
M*.T7@.D0SMI- B#3:98$=M00T2WI:&JU6D6,7X&G'E?$%.K.:_[ZG;'(AF,2W
MQ$[R2B,-':=.'=OO>:M<>6Q?B-'8M1Z?...8P#IFF* J[L0Q7>.+!9Q?"'0E7
M]RWAU&NSCWO_OF8][CYSE]WSD6T9X3?8^2-W_"Z[&=\%@.TO=UP>ZQZ.O?;H9
M3SYX^V<O&'@.+7SVMU^JU.^X:%(P2&(YL[O,N4^JU"^$XW/ MYY'Y@.EU:GN]:
M@.\ 7+CN(Z?,@.;8/C`]ES]'?L7M1KE_^\Z759W(&HU[YQUZ.C0)EVCA7E6#D]
MH90#CXZ1HP]I!\R!J-?.[SXS0S@./PAWJCD&?:M0C!7GSF5JO^:[N>+H1'E9#
M'^D#._JNRW63"<<>=]EMO6:9=(RL!XOZ^",0/F?&DTZI_ZU><X33=P+;E@.W[
MAK"#H>.%@.<6+0]\.//V1`C;I@.Z'^@._GR:V&:S.:T!ZK6B39$+1>W.)R;S!9T
M5$TZ/M&&W^8;>,QRHJ#40M%:[[99CLE_T+:3URV4-K,MG[NZ'85K:RVM\YJ$
MY^L^'THUO-ODBA?F67_RV:?UVG]4[2=-_8F4W*;.NVVMJZCL_.:_<6>\R_YG
MZ+;-O%'_IO?(_>?H'+*_,H=YXO;()*&-@.H%M>4]TK!NWL>H\3-N).9%O*#*/
MVZ3L\ 1X(J!B.6*>^VR9[,$50S;4/3J8Q\?>V//"UAY[H5PX"T8C[C;HF_*4
M'K*?7S_X(,5J)?C>*_)E.'?JY5GK#[G[R/NZ-#%*LZ$<,=GK),3!NW\V%SXY
MS'5"%[,9N>+!HJILG!ZQENPIC)_ %^^FRHLUN;F$Z[7/CN5;NFW]26UA?GMF
M?C,ER&KUF2,,$5!'=-1-;M@.Z5>,_S$&X^U2FT?^CHIY\_#.=U[[\JW$X:T)[
M:D?S`>=9=^7>-;1V^W#:=K;]9WGF*1._\>:;1^&1E7E1#5#Q^V/^H_':I2R$
MBR_7U^=?/W^Y/3B<1IT&G31IS/J)VEQ>W9W_<D6VJWOLX$*8_(Y40%6\JN7U
MQ>?+UU;AGZM;7'RYN3N__]S[<MO[^MOUK,OA2'<M3S@.]?VSS=[.3-&7[W@.74
M=.$PT]GG8B]DSK-\]/)HK#9H,W?[%IWOCR9!T0@.:-[#_M2CHWC0BBVVRJD*T
MM %CHY#0OSYQ1I5O<,^C$QW8)AFG-* P=J+ QQ^-XW3V'X4[[I)1W7ZE<@.GG
M35W&XJ+4:[?!<"#K5UZT=4AY-Y11*/8-9$B5W+NZ_W9UGSQ+92'#<+HUS=.W
MAE1J(O!I'BBG/4/]NSQ/^B1M4MDQ76Q*][ >`QIG? IQ+1XMYRNUE.W(7:EX
MJ;*8[IC,Y;X[#K\D/3KT9MHIN@.0T9B((9Z()9!#3:J6]%M0FP45N[&7Y37AP
MPPMR%IITZ-$7N-:?N 5&P%T<`9O==KN :_V$8?)=ZR_KI!+7^F4FF.Q:/WF&
MF[C6SYX-KO5A?NLU/USK[]2U/JIWZZLWY7)-UF(O9,Y3[6O]^6*(NP)%-6SS
M6+;T["_1][I6@.)KUVCVG:N#/,DHX]7^])+8B-407T.\;MMXT?"V:4<*6[Q=,
M+(\9-M<=&2P8T48ZJ>&9I3,U&5/9_)0U=DTG-G08E]1"8<_DV6IXAS+J32^:
M-=.D^8D,0;CC(SI7,5^@.NO"I&^\H7(F)^X8OA@..*X/#D2>WV_OXZLH5N6I/J
MI-IR'KDG12J7L*:CPP>M;T743-8X[0>U&KSNH_E1AY?D*<NZG-5&GLD+YBD[
MY\RMKJ+DFGA@.J(>@.%@.2EYAOJX4^04XR<X$\0%/P)<H(_P9_@.3_ GR G^!$%M
MH3^]7\!JM^G3:/4NI,1<WS+H"!Z,7/$[::\?(0>QZ[<...94`6I]LC_NIFGDO
MUHCWY^22JC7_P8WP5Y6^X$:JED\B<.UQ/UF R6(?R4;3WB[W-=0S4@.-5B5R^
M;+)@.1%LY_:DPD\L`X9^R.FS+\+W#V.5$50G9/KG<F"V^RSUA/U/K-QV5"NME
MZZGZK;(<BUUK`Z@.24"4&\,0#>+N8&R@.3ALD'52[KI!)099D)Q HP1N3+<!%29
M/9MXJ#+[M6ZLH[Z%*F-9K30]PD#WS4 !9FXKF(E"W=M"33/GT0!50MZ5D;<&
MJ+)\R+"U`C+<,::RI-T%4@.E?WAI?/L5/;A#4&@.0%) !R@.C]!4/ G^!/\"?X$
M0<&?("?X$P2UW_ZT*<!QY%K"M?QQWW(\WPT,/S7H2#L@.HK7@.)'CC26<!/]3F
M\<.S_'QCM@.X`. )PW%K $33DZS%+UP`TY$Z.O:?%T) )P^2C(9=U4@.D:LLP$
M8P:47!EN@.H;,GDV*1TR^91IQ)0OS2Z0_D(P@.&5&H6U:H:>8K( !DA[^K(NW"2
M44U]JP9HR"+PP/8*/%#M[!8.6=;^@.H>$NV^-NW?P>QD$5:2@.SO![/N14N)S@.
M3Q 4_ ER@.C_!G^!/\"?("?X$06VA/R7G$0U;MX9>W^6&<,U4)&->DC)ZM*4I
MAKKE>)G:RFZ?+7^<J?&*9VK./V/R=(&1;+,I(JEI!3QC,DM\()A ,/&,25"5
M&,&K.()WBJ$J$X;)1U4NZZ025&69"<:,$;DRW 15V<G,MI3PC$F8W]:;7Q'L
M21H-@.\P$F8EBK\Y,)U&AIIGS@.,R$O"LG[^*6XC10E:DI0VT%9;ACSY @.L:7?!
M5,*7M\:7\0P2"&H=@.@.(3`#G!GR H^!/\"?X$?X*@.X$^0$_P)@.MIO?]IY,C+[
MV[M-W;+'?;F0E[II0APSKFG6=X:WE07>\H1-><M6*S_/F2D^>$[PG. YP7-B
M]E#%V<-9,3QGPC#Y>,YEG52"YRPSP9@.Q(E>&F^ YS\!SPOS*-3^PF& Q4:B[
M4JAIYBM@.,2'ORLF[N"4\%4_)+ 5P/-DOGK.DW07/"6_?&F\_P>]]$%21@.@.*/
M`#D5+R?X$P0%?X*<X$_P)_@.3_ ER@.C]!4%OH3\E!0SW5.[O5!?I/95/Z3\D/
M%V
#K00:"'00J"%&,@.J-Y"I747I*LV\:&'R,#G0PA6=E(\6EIQ@.S!B1*\.U
MHX6YLHE'"Q/X8IH+0:"%>VQ^0 N!%J)0JU.HK>+0PBA@.&V@.AY%T9>;>!%NX&
M6JBN>GBBVMHMN+"T'09>"'_?%G]7@.1="4.L0%'X>AYS@.3Q 4_ G^!'^"/T%0
M\"?("?X$0>VW/R7'"UW^1W^&&&9Y$F*^!TY2_P'W,CV",1T9J6F+\.+<FZ[G
M'HS8S/HB[2SQ04>"C@.0="3H2XW 5QV&U@. <O)@.^3CXY<UDDEZ,@.R$XP9(W)E
MN DZ,GLVH"-A?NLU/]"1VTI'%D$*H=CWMM@.+F?. L(2\RY)W<0MJ&NC(U+!@.
M' HXSPIV=HN-+&EW04;"E[?&E\_PRQD$5:"@.5 6_[$-.A<L)_@.1!P9\@.)_@.3
M_ G^!'^"G.!/$%09@.BI\(?=#O- <6DX_#Y\X"2 !R_62E7.<8VL!0VRR*86H
MYG\"9(;H8!S!.()Q!..($;6*(ZI6#..8,$P^QG%9)Y5@.',M,, &:,R)7A)AC'
M[-G$,X[9+SYC'16,XQZ;'QA'/ $2A;IEA9IFOH*72T/>U9$W7BZ])XSCCKU<
MNJ3=!>,(;]\:;\?+QR"H=0@.*O]%#3O G" K^!'^"/\&?(*B$@.NK GR"GPN4$
M?X*@.*N%/ZR ,XUJ/7$NXEC_N6X[GNX'A]P4W4D4P](?^`YV=@.6Y\[_/AR.7/
M%G])%2+=4R!;BP!CBTT!QN;9C&!4L_*16>*#D 0A"4(2A"3&XRJ.Q\UB",F$
M8?(1DLLZJ00A66:",6-$K@.PW04@.V,W,J("1A?H68WX?Z`R$)0A*%NF6%FF:^
M`D(2\JZ<O(M;%%-!2):"##9S(X-J>81DS)Z)P#9I\N,S&LSE,6<NE[G2@.?\D
M6YES@.</NJ*M4QU,IJ]<+*ME'.B7=VU^OK\GBPLNP+GM=")I?N;ES!5V:20.]
M#88#:;WLDZ:V3M6SL];)J5P=]2*S*GE':&IU<WY[.;<O_]+I(LF47R?...N?R
M5^:RGII*F+K,1W8_S?,UK<;?CYC\[^.G[4_2H,&B=W7_[>I^_ J#&CE73=%1U
M+I]?N!.ZK,/]%^%^9]QUA7O,+IZX\9V-*>)TBRF,0+IN>)K*7E3&8B46*S%/
M6NL\J=5M?4C+I)C\)PR3;[%R62>56*PL,\%DBY7),]S$8F7V;+!8"...;K_EA
ML1*OK$&Q[V&Q%S+GP8(GY%V6O(M;\,1B9?K%2FW%8F7<4F1E% BN+7YM=O;MQ
M!PRW<V/:L2.^W.XJ)\#](:@.U""KK0 ]_@.IS@.3Q 4_ ER@.C_!G^!/\"?("?X$
M0>V&/[U?P%K7[=SB=U)NWQ1#W7*\3&UEM\^6/T[9..]]Y/P'-\(?9U*W-'7+
M'O?E4F#JII-]SO)JH-4WK<<U>Q*!2]DFV]NYN]Y5I...VE;.AG;+I?>D=)=M]
M[[*3M$NO\_?*9\P*=\OC;GD`J !0,=VIXG3GI)B[Y1.&R0>@.+NND$@.!JF0G&
MC!&Y,MP$@.)H]&P"H,+_UFA\`5-PMCT+=LD)-,U_!W?*0=W7DC;OE8[F_W7N?
M4'LED;E3[Q-:T^X"0(6W;XVWXWGX$-0Z! 6 `G*"/T%0\"?X$_P)_@.1!P9\@.
M)_@.3!+7?_I0<4/2M(>^3=W$_%=>8CUO-#G]N&,-LJ8N\8Y-->4>U,^,=E:QO
M'\K4`8!*`)4`*@.%48OBNXO!]NH0L2 $H) R3#ZA<UDDE@.,HR$XP9(W)EN F@.
M,GLV`"IA?NLU/P"5`"I1J%M6J(7,5P!40MYER;NX-3050&4IA&$<09CN$9=;
M!526M+L`*N'M6^/M^,$-@.EJ'H `$0$[P)P@.*_@.1_@.C_!GR H^!/D!'^"H/;;
MGPHE%.<`0VT!_U/8E/[3\M.%&#+01:6"VT\/]02P$"% `4````" "%8Y4S
M>^Q]U3D.``"CZ0$`, `````````!`" `````````365R9V5?4F5P;&EC871I
M;VY?5F5R8F]S95],;V=G:6YG7T9O<E]%<G)O<G,N='AT4$L%!@.`````!``$`
*7@.```(<.````````
`
end
|||Thanks Javed.
Occasionally you're getting network connectivity errors. When there are no
errors - most of the time - the metadata records are being removed. Apart
form the network errors, it all seems to be working correctly. You have a 14
days subscription
retention period which accounts for these tables not being fully cleaned
up - perhaps you can try reducing this retention period?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thaks Paul for your prompt and through help.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O343DsgBGHA.344@.TK2MSFTNGP11.phx.gbl...
> Thanks Javed.
> Occasionally you're getting network connectivity errors. When there are no
> errors - most of the time - the metadata records are being removed. Apart
> form the network errors, it all seems to be working correctly. You have a
14
> days subscription
> retention period which accounts for these tables not being fully cleaned
> up - perhaps you can try reducing this retention period?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
I have set up MERGE Replication with NOSYNC option and 14 days subscription
retention period.
My both the servers are in sync with each other since when I started
replication a month back.
But I find there are thousands of records in MSmerge_contents,
MSmerge_tombstone, MSmerge_genhistory.
I want to know how to get rid of all the unwanted entries from the above
tables.
Regards
Javed Iqbal
Javed,
please run sp_mergemetadataretentioncleanup and see if this helps.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul
Thanks for your reply.
This stored proc is already been running by the merge agent every minute.
Any other option is highly appreciated.
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elzAKGIBGHA.628@.TK2MSFTNGP10.phx.gbl...
> Javed,
> please run sp_mergemetadataretentioncleanup and see if this helps.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also I am getting these types of errors
The merge process could not perform retention-based meta data cleanup in
database 'ABC'.
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elzAKGIBGHA.628@.TK2MSFTNGP10.phx.gbl...
> Javed,
> please run sp_mergemetadataretentioncleanup and see if this helps.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
you could try increasing the merge agent's querytimeout.
Also, you could try reindexing the metadata tables.
If this doesn't help, please enable logging on your merge agent:
http://support.microsoft.com/support.../q312/2/92.asp
and post up the log file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Attached is my agent logging file. I still have thousands of records into
those tables.
Thanks in advance
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SBHyfJBGHA.2708@.TK2MSFTNGP12.phx.gbl...
> Javed,
> you could try increasing the merge agent's querytimeout.
> Also, you could try reindexing the metadata tables.
> If this doesn't help, please enable logging on your merge agent:
> http://support.microsoft.com/support.../q312/2/92.asp
> and post up the log file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
begin 666 Merge_Replication_Verbose_Logging_For_Errors.zip
M4$L#!!0````(`(5CE3-[['W5.0X``*/I`0`P````365R9V5?4F5P;&EC871I
M;VY?5F5R8F]S95],;V=G:6YG7T9O<E]%<G)O<G,N='AT[9UK3^-(%H:_1\I_
M*.T7@.D0SMI- B#3:98$=M00T2WI:&JU6D6,7X&G'E?$%.K.:_[ZG;'(AF,2W
MQ$[R2B,-':=.'=OO>:M<>6Q?B-'8M1Z?...8P#IFF* J[L0Q7>.+!9Q?"'0E7
M]RWAU&NSCWO_OF8][CYSE]WSD6T9X3?8^2-W_"Z[&=\%@.TO=UP>ZQZ.O?;H9
M3SYX^V<O&'@.+7SVMU^JU.^X:%(P2&(YL[O,N4^JU"^$XW/ MYY'Y@.EU:GN]:
M@.\ 7+CN(Z?,@.;8/C`]ES]'?L7M1KE_^\Z759W(&HU[YQUZ.C0)EVCA7E6#D]
MH90#CXZ1HP]I!\R!J-?.[SXS0S@./PAWJCD&?:M0C!7GSF5JO^:[N>+H1'E9#
M'^D#._JNRW63"<<>=]EMO6:9=(RL!XOZ^",0/F?&DTZI_ZU><X33=P+;E@.W[
MAK"#H>.%@.<6+0]\.//V1`C;I@.Z'^@._GR:V&:S.:T!ZK6B39$+1>W.)R;S!9T
M5$TZ/M&&W^8;>,QRHJ#40M%:[[99CLE_T+:3URV4-K,MG[NZ'85K:RVM\YJ$
MY^L^'THUO-ODBA?F67_RV:?UVG]4[2=-_8F4W*;.NVVMJZCL_.:_<6>\R_YG
MZ+;-O%'_IO?(_>?H'+*_,H=YXO;()*&-@.H%M>4]TK!NWL>H\3-N).9%O*#*/
MVZ3L\ 1X(J!B.6*>^VR9[,$50S;4/3J8Q\?>V//"UAY[H5PX"T8C[C;HF_*4
M'K*?7S_X(,5J)?C>*_)E.'?JY5GK#[G[R/NZ-#%*LZ$<,=GK),3!NW\V%SXY
MS'5"%[,9N>+!HJILG!ZQENPIC)_ %^^FRHLUN;F$Z[7/CN5;NFW]26UA?GMF
M?C,ER&KUF2,,$5!'=-1-;M@.Z5>,_S$&X^U2FT?^CHIY\_#.=U[[\JW$X:T)[
M:D?S`>=9=^7>-;1V^W#:=K;]9WGF*1._\>:;1^&1E7E1#5#Q^V/^H_':I2R$
MBR_7U^=?/W^Y/3B<1IT&G31IS/J)VEQ>W9W_<D6VJWOLX$*8_(Y40%6\JN7U
MQ>?+UU;AGZM;7'RYN3N__]S[<MO[^MOUK,OA2'<M3S@.]?VSS=[.3-&7[W@.74
M=.$PT]GG8B]DSK-\]/)HK#9H,W?[%IWOCR9!T0@.:-[#_M2CHWC0BBVVRJD*T
MM %CHY#0OSYQ1I5O<,^C$QW8)AFG-* P=J+ QQ^-XW3V'X4[[I)1W7ZE<@.GG
M35W&XJ+4:[?!<"#K5UZT=4AY-Y11*/8-9$B5W+NZ_W9UGSQ+92'#<+HUS=.W
MAE1J(O!I'BBG/4/]NSQ/^B1M4MDQ76Q*][ >`QIG? IQ+1XMYRNUE.W(7:EX
MJ;*8[IC,Y;X[#K\D/3KT9MHIN@.0T9B((9Z()9!#3:J6]%M0FP45N[&7Y37AP
MPPMR%IITZ-$7N-:?N 5&P%T<`9O==KN :_V$8?)=ZR_KI!+7^F4FF.Q:/WF&
MF[C6SYX-KO5A?NLU/USK[]2U/JIWZZLWY7)-UF(O9,Y3[6O]^6*(NP)%-6SS
M6+;T["_1][I6@.)KUVCVG:N#/,DHX]7^])+8B-407T.\;MMXT?"V:4<*6[Q=,
M+(\9-M<=&2P8T48ZJ>&9I3,U&5/9_)0U=DTG-G08E]1"8<_DV6IXAS+J32^:
M-=.D^8D,0;CC(SI7,5^@.NO"I&^\H7(F)^X8OA@..*X/#D2>WV_OXZLH5N6I/J
MI-IR'KDG12J7L*:CPP>M;T743-8X[0>U&KSNH_E1AY?D*<NZG-5&GLD+YBD[
MY\RMKJ+DFGA@.J(>@.%@.2EYAOJX4^04XR<X$\0%/P)<H(_P9_@.3_ GR G^!$%M
MH3^]7\!JM^G3:/4NI,1<WS+H"!Z,7/$[::\?(0>QZ[<...94`6I]LC_NIFGDO
MUHCWY^22JC7_P8WP5Y6^X$:JED\B<.UQ/UF R6(?R4;3WB[W-=0S4@.-5B5R^
M;+)@.1%LY_:DPD\L`X9^R.FS+\+W#V.5$50G9/KG<F"V^RSUA/U/K-QV5"NME
MZZGZK;(<BUUK`Z@.24"4&\,0#>+N8&R@.3ALD'52[KI!)099D)Q HP1N3+<!%29
M/9MXJ#+[M6ZLH[Z%*F-9K30]PD#WS4 !9FXKF(E"W=M"33/GT0!50MZ5D;<&
MJ+)\R+"U`C+<,::RI-T%4@.E?WAI?/L5/;A#4&@.0%) !R@.C]!4/ G^!/\"?X$
M0<&?("?X$P2UW_ZT*<!QY%K"M?QQWW(\WPT,/S7H2#L@.HK7@.)'CC26<!/]3F
M\<.S_'QCM@.X`. )PW%K $33DZS%+UP`TY$Z.O:?%T) )P^2C(9=U4@.D:LLP$
M8P:47!EN@.H;,GDV*1TR^91IQ)0OS2Z0_D(P@.&5&H6U:H:>8K( !DA[^K(NW"2
M44U]JP9HR"+PP/8*/%#M[!8.6=;^@.H>$NV^-NW?P>QD$5:2@.SO![/N14N)S@.
M3Q 4_ ER@.C_!G^!/\"?("?X$06VA/R7G$0U;MX9>W^6&<,U4)&->DC)ZM*4I
MAKKE>)G:RFZ?+7^<J?&*9VK./V/R=(&1;+,I(JEI!3QC,DM\()A ,/&,25"5
M&,&K.()WBJ$J$X;)1U4NZZ025&69"<:,$;DRW 15V<G,MI3PC$F8W]:;7Q'L
M21H-@.\P$F8EBK\Y,)U&AIIGS@.,R$O"LG[^*6XC10E:DI0VT%9;ACSY @.L:7?!
M5,*7M\:7\0P2"&H=@.@.(3`#G!GR H^!/\"?X$?X*@.X$^0$_P)@.MIO?]IY,C+[
MV[M-W;+'?;F0E[II0APSKFG6=X:WE07>\H1-><M6*S_/F2D^>$[PG. YP7-B
M]E#%V<-9,3QGPC#Y>,YEG52"YRPSP9@.Q(E>&F^ YS\!SPOS*-3^PF& Q4:B[
M4JAIYBM@.,2'ORLF[N"4\%4_)+ 5P/-DOGK.DW07/"6_?&F\_P>]]$%21@.@.*/
M`#D5+R?X$P0%?X*<X$_P)_@.3_ ER@.C]!4%OH3\E!0SW5.[O5!?I/95/Z3\D/
M%V
#K00:"'00J"%&,@.J-Y"I747I*LV\:&'R,#G0PA6=E(\6EIQ@.S!B1*\.UMHX6YLHE'"Q/X8IH+0:"%>VQ^0 N!%J)0JU.HK>+0PBA@.&V@.AY%T9>;>!%NX&
M6JBN>GBBVMHMN+"T'09>"'_?%G]7@.1="4.L0%'X>AYS@.3Q 4_ G^!'^"/T%0
M\"?("?X$0>VW/R7'"UW^1W^&&&9Y$F*^!TY2_P'W,CV",1T9J6F+\.+<FZ[G
M'HS8S/HB[2SQ04>"C@.0="3H2XW 5QV&U@. <O)@.^3CXY<UDDEZ,@.R$XP9(W)E
MN DZ,GLVH"-A?NLU/]"1VTI'%D$*H=CWMM@.+F?. L(2\RY)W<0MJ&NC(U+!@.
M' HXSPIV=HN-+&EW04;"E[?&E\_PRQD$5:"@.5 6_[$-.A<L)_@.1!P9\@.)_@.3
M_ G^!'^"G.!/$%09@.BI\(?=#O- <6DX_#Y\X"2 !R_62E7.<8VL!0VRR*86H
MYG\"9(;H8!S!.()Q!..($;6*(ZI6#..8,$P^QG%9)Y5@.',M,, &:,R)7A)AC'
M[-G$,X[9+SYC'16,XQZ;'QA'/ $2A;IEA9IFOH*72T/>U9$W7BZ])XSCCKU<
MNJ3=!>,(;]\:;\?+QR"H=0@.*O]%#3O G" K^!'^"/\&?(*B$@.NK GR"GPN4$
M?X*@.*N%/ZR ,XUJ/7$NXEC_N6X[GNX'A]P4W4D4P](?^`YV=@.6Y\[_/AR.7/
M%G])%2+=4R!;BP!CBTT!QN;9C&!4L_*16>*#D 0A"4(2A"3&XRJ.Q\UB",F$
M8?(1DLLZJ00A66:",6-$K@.PW04@.V,W,J("1A?H68WX?Z`R$)0A*%NF6%FF:^
M`D(2\JZ<O(M;%%-!2):"##9S(X-J>81DS)Z)P#9I\N,S&LSE,6<NE[G2@.?\D
M6YES@.</NJ*M4QU,IJ]<+*ME'.B7=VU^OK\GBPLNP+GM=")I?N;ES!5V:20.]
M#88#:;WLDZ:V3M6SL];)J5P=]2*S*GE':&IU<WY[.;<O_]+I(LF47R?...N?R
M5^:RGII*F+K,1W8_S?,UK<;?CYC\[^.G[4_2H,&B=W7_[>I^_ J#&CE73=%1U
M+I]?N!.ZK,/]%^%^9]QUA7O,+IZX\9V-*>)TBRF,0+IN>)K*7E3&8B46*S%/
M6NL\J=5M?4C+I)C\)PR3;[%R62>56*PL,\%DBY7),]S$8F7V;+!8"...;K_EA
ML1*OK$&Q[V&Q%S+GP8(GY%V6O(M;\,1B9?K%2FW%8F7<4F1E% BN+7YM=O;MQ
M!PRW<V/:L2.^W.XJ)\#](:@.U""KK0 ]_@.IS@.3Q 4_ ER@.C_!G^!/\"?("?X$
M0>V&/[U?P%K7[=SB=U)NWQ1#W7*\3&UEM\^6/T[9..]]Y/P'-\(?9U*W-'7+
M'O?E4F#JII-]SO)JH-4WK<<U>Q*!2]DFV]NYN]Y5I...VE;.AG;+I?>D=)=M]
M[[*3M$NO\_?*9\P*=\OC;GD`J !0,=VIXG3GI)B[Y1.&R0>@.+NND$@.!JF0G&
MC!&Y,MP$@.)H]&P"H,+_UFA\`5-PMCT+=LD)-,U_!W?*0=W7DC;OE8[F_W7N?
M4'LED;E3[Q-:T^X"0(6W;XVWXWGX$-0Z! 6 `G*"/T%0\"?X$_P)_@.1!P9\@.
M)_@.3!+7?_I0<4/2M(>^3=W$_%=>8CUO-#G]N&,-LJ8N\8Y-->4>U,^,=E:QO
M'\K4`8!*`)4`*@.%48OBNXO!]NH0L2 $H) R3#ZA<UDDE@.,HR$XP9(W)EN F@.
M,GLV`"IA?NLU/P"5`"I1J%M6J(7,5P!40MYER;NX-3050&4IA&$<09CN$9=;
M!526M+L`*N'M6^/M^,$-@.EJ'H `$0$[P)P@.*_@.1_@.C_!GR H^!/D!'^"H/;;
MGPHE%.<`0VT!_U/8E/[3\M.%&
#+01:6"VT\/]02P$"% `4````" "%8Y4SM>^Q]U3D.``"CZ0$`, `````````!`" `````````365R9V5?4F5P;&EC871I
M;VY?5F5R8F]S95],;V=G:6YG7T9O<E]%<G)O<G,N='AT4$L%!@.`````!``$`
*7@.```(<.````````
`
end
|||Thanks Javed.
Occasionally you're getting network connectivity errors. When there are no
errors - most of the time - the metadata records are being removed. Apart
form the network errors, it all seems to be working correctly. You have a 14
days subscription
retention period which accounts for these tables not being fully cleaned
up - perhaps you can try reducing this retention period?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thaks Paul for your prompt and through help.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O343DsgBGHA.344@.TK2MSFTNGP11.phx.gbl...
> Thanks Javed.
> Occasionally you're getting network connectivity errors. When there are no
> errors - most of the time - the metadata records are being removed. Apart
> form the network errors, it all seems to be working correctly. You have a
14
> days subscription
> retention period which accounts for these tables not being fully cleaned
> up - perhaps you can try reducing this retention period?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
Wednesday, March 28, 2012
Merge replication, not staying in sync
Hi,
I am running SQL Server 2000 with SP3a. I have a database with a merge
publication that merges the contents of several tables every 2 hours as the
data is updated on both the publisher and the subscriber. I validate all
subscriptions twice a week to ensure they are in sync. On Friday I removed
two columns from a table that has roughly 50 columns. I did this by going to
the properties of the merge publication and dropping the two columns through
the publication interface. This propagated to all servers and all was well.
I came in this Monday, and validated my tables (last time was Friday morning
- successful) and the binary checksum was off and they table I dropped the
two columns from failed validation. I checked the conflict viewer and no
conflicts were shown. The changes seem to be pushing properly from the
subscriber to the publisher, but then they do not flow on to the other
subscribers. I'm thinking that possibly the insert/update/delete triggers
for the merge replication are no longer correct or something but I am not
entirely sure. I am contemplating reinitializing these subscriptions, but I
wondered if anyone had heard of this or had any ideas for a solution so I can
avoid taking this database offline to reinitialize the subcribers.
Thanks,
Paul T.
Hi Paul,
We have recently released a QFE for a bug that would cause data
non-convergence after dropping columns in a table. You can contact Product
Support to make sure you are running into the same bug, and if so, to get
information about getting the QFE. This bug will also be fixed in the
upcoming SP4.
Thanks
Ram Singh
"Paul T." wrote:
> Hi,
> I am running SQL Server 2000 with SP3a. I have a database with a merge
> publication that merges the contents of several tables every 2 hours as the
> data is updated on both the publisher and the subscriber. I validate all
> subscriptions twice a week to ensure they are in sync. On Friday I removed
> two columns from a table that has roughly 50 columns. I did this by going to
> the properties of the merge publication and dropping the two columns through
> the publication interface. This propagated to all servers and all was well.
> I came in this Monday, and validated my tables (last time was Friday morning
> - successful) and the binary checksum was off and they table I dropped the
> two columns from failed validation. I checked the conflict viewer and no
> conflicts were shown. The changes seem to be pushing properly from the
> subscriber to the publisher, but then they do not flow on to the other
> subscribers. I'm thinking that possibly the insert/update/delete triggers
> for the merge replication are no longer correct or something but I am not
> entirely sure. I am contemplating reinitializing these subscriptions, but I
> wondered if anyone had heard of this or had any ideas for a solution so I can
> avoid taking this database offline to reinitialize the subcribers.
> Thanks,
> Paul T.
I am running SQL Server 2000 with SP3a. I have a database with a merge
publication that merges the contents of several tables every 2 hours as the
data is updated on both the publisher and the subscriber. I validate all
subscriptions twice a week to ensure they are in sync. On Friday I removed
two columns from a table that has roughly 50 columns. I did this by going to
the properties of the merge publication and dropping the two columns through
the publication interface. This propagated to all servers and all was well.
I came in this Monday, and validated my tables (last time was Friday morning
- successful) and the binary checksum was off and they table I dropped the
two columns from failed validation. I checked the conflict viewer and no
conflicts were shown. The changes seem to be pushing properly from the
subscriber to the publisher, but then they do not flow on to the other
subscribers. I'm thinking that possibly the insert/update/delete triggers
for the merge replication are no longer correct or something but I am not
entirely sure. I am contemplating reinitializing these subscriptions, but I
wondered if anyone had heard of this or had any ideas for a solution so I can
avoid taking this database offline to reinitialize the subcribers.
Thanks,
Paul T.
Hi Paul,
We have recently released a QFE for a bug that would cause data
non-convergence after dropping columns in a table. You can contact Product
Support to make sure you are running into the same bug, and if so, to get
information about getting the QFE. This bug will also be fixed in the
upcoming SP4.
Thanks
Ram Singh
"Paul T." wrote:
> Hi,
> I am running SQL Server 2000 with SP3a. I have a database with a merge
> publication that merges the contents of several tables every 2 hours as the
> data is updated on both the publisher and the subscriber. I validate all
> subscriptions twice a week to ensure they are in sync. On Friday I removed
> two columns from a table that has roughly 50 columns. I did this by going to
> the properties of the merge publication and dropping the two columns through
> the publication interface. This propagated to all servers and all was well.
> I came in this Monday, and validated my tables (last time was Friday morning
> - successful) and the binary checksum was off and they table I dropped the
> two columns from failed validation. I checked the conflict viewer and no
> conflicts were shown. The changes seem to be pushing properly from the
> subscriber to the publisher, but then they do not flow on to the other
> subscribers. I'm thinking that possibly the insert/update/delete triggers
> for the merge replication are no longer correct or something but I am not
> entirely sure. I am contemplating reinitializing these subscriptions, but I
> wondered if anyone had heard of this or had any ideas for a solution so I can
> avoid taking this database offline to reinitialize the subcribers.
> Thanks,
> Paul T.
Merge Replication with Indexed Tables
I have several systems located aroung the globe that currently have Merge
Replication running just fine. I've recently found that if I add an index to
one of the tables, my application will perform much better.
How does adding indices on tables effect the Merge Replication? Is this
considered a Schema change?
If I Index the NY DB, how will it impact the databases that do not have the
indices?
Thaks,
Randy
randy.crockett@.ectinfo.com
In general it will improve them, especially if the filters are on columns
which are part of the join condition. Too many indexes will degrade overall
insert, update and delete performance, so use indexes sparingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> I have several systems located aroung the globe that currently have Merge
> Replication running just fine. I've recently found that if I add an index
to
> one of the tables, my application will perform much better.
> How does adding indices on tables effect the Merge Replication? Is this
> considered a Schema change?
> If I Index the NY DB, how will it impact the databases that do not have
the
> indices?
> Thaks,
> Randy
> randy.crockett@.ectinfo.com
|||If I add Indexes to a replicated database, do I have to create a new snapshot
and restart the replication?
Is this considered a schema change?
"Hilary Cotter" wrote:
> In general it will improve them, especially if the filters are on columns
> which are part of the join condition. Too many indexes will degrade overall
> insert, update and delete performance, so use indexes sparingly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randy" <Randy@.discussions.microsoft.com> wrote in message
> news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> to
> the
>
>
Replication running just fine. I've recently found that if I add an index to
one of the tables, my application will perform much better.
How does adding indices on tables effect the Merge Replication? Is this
considered a Schema change?
If I Index the NY DB, how will it impact the databases that do not have the
indices?
Thaks,
Randy
randy.crockett@.ectinfo.com
In general it will improve them, especially if the filters are on columns
which are part of the join condition. Too many indexes will degrade overall
insert, update and delete performance, so use indexes sparingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> I have several systems located aroung the globe that currently have Merge
> Replication running just fine. I've recently found that if I add an index
to
> one of the tables, my application will perform much better.
> How does adding indices on tables effect the Merge Replication? Is this
> considered a Schema change?
> If I Index the NY DB, how will it impact the databases that do not have
the
> indices?
> Thaks,
> Randy
> randy.crockett@.ectinfo.com
|||If I add Indexes to a replicated database, do I have to create a new snapshot
and restart the replication?
Is this considered a schema change?
"Hilary Cotter" wrote:
> In general it will improve them, especially if the filters are on columns
> which are part of the join condition. Too many indexes will degrade overall
> insert, update and delete performance, so use indexes sparingly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randy" <Randy@.discussions.microsoft.com> wrote in message
> news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> to
> the
>
>
Merge replication with IMAGE Data Type
Hello all and thank you in advance:
Question 1) Can I use merge replication with tables that IMAGE data
type columns?
Question 2) I need to add my uniqueidenity, if I add to the bottom of
the column list can be sure that ALTER statement will be transparant
and not lock the entire table?
Thank VERY much,
Erik
Yes - merge replication can handle text/image datatypes. This is one factor
that distinguishes it from queued updating subscribers.
I don't follow your other question. Merge uses a column containing a
uniqueidentifier and which has the RowGuid attribute. Only one column can
have this attribute although there can be several columns containing
uniqueidentifiers. Adding in other columns using sp_repladdcolumn with a
default should be fine, or using ALTER TABLE in SQL 2005. Altering the schema
of the table will lock the table whichever way you do it.
Cheers,
Paul Ibison
Question 1) Can I use merge replication with tables that IMAGE data
type columns?
Question 2) I need to add my uniqueidenity, if I add to the bottom of
the column list can be sure that ALTER statement will be transparant
and not lock the entire table?
Thank VERY much,
Erik
Yes - merge replication can handle text/image datatypes. This is one factor
that distinguishes it from queued updating subscribers.
I don't follow your other question. Merge uses a column containing a
uniqueidentifier and which has the RowGuid attribute. Only one column can
have this attribute although there can be several columns containing
uniqueidentifiers. Adding in other columns using sp_repladdcolumn with a
default should be fine, or using ALTER TABLE in SQL 2005. Altering the schema
of the table will lock the table whichever way you do it.
Cheers,
Paul Ibison
Merge Replication with Foreign Keys
I have a database with related tables by foreign keys (Not for Replication),
and I want to use Merge replication, is there any problem to keep the foreign
keys on both sides (publisher and subscriber) or I have to remove them?
Saeed,
you can keep them - as long as they have the NFR attribute.
If you are replicating a huge amount of data, there are issues, and this
article will help explain them:
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
and I want to use Merge replication, is there any problem to keep the foreign
keys on both sides (publisher and subscriber) or I have to remove them?
Saeed,
you can keep them - as long as they have the NFR attribute.
If you are replicating a huge amount of data, there are issues, and this
article will help explain them:
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Merge replication with foreign key constraints
Let's say I have a database with three tables. One for customers, and two
for the invoices. Customers have cust_id field, wich is 'surogate' key, it
contains the org_id where the customer was created. That table should be in
merge replication because I need to be able to add data and make changes on
all locations in the replication system.
The invoices are referenced between them self with foreign key (you can't
insert invoice_id in the detail table if the invoice_id doesn't exsist in
master table), and the invoices table is referenced with customer table with
foreign key, so you can't add cust_id to invoices wich doesn't exsist in
customers table).
The DDL is like this:
CREATE TABLE [dbo].[customers] (
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
[cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoice_details] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[item_id] [int] NOT NULL ,
[item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
[item_price] [decimal](18, 11) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoices] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[invoice_date] [datetime] NOT NULL ,
[remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[cust_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
(
[invoice_id],
[item_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
(
[invoice_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] ADD
CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
[rowguid]
GO
CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] ADD
CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[invoices] (
[invoice_id]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[invoices] ADD
CONSTRAINT [FK_invoices_customers] FOREIGN KEY
(
[cust_id]
) REFERENCES [dbo].[customers] (
[cust_id]
) NOT FOR REPLICATION
GO
For the purpose I'm creating two publications. One for the customers, with
no filtering, because I need to have all the subscribers share the same
data. The other one is for the invoices, but those are filtered within
invoice_id, because I don't want one subscriber to have data that doesn't
belong to it.
So, I create the first merge publication, and at the end SQL server gives me
this:
This publication contains references to foreign keys outside the
publication. The following tables are outside the publication, but contain
foreign keys that are referenced from inside the publication:
invoices (references 'customers')
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Why do I need to add invoices table to the publication?
The other one, when creating publication for the invoices gives me more
headache:
This publication contains references to primary keys outside the
publication.
The following tables are outside the publication, but contain primary keys
that are referenced from inside the publication:
-- customers (referencing table is 'invoices')
Although you can change existing data in the referencing tables, you will
not be able to add rows to those tables. If you want to add rows to the
referencing tables, include the referenced tables as articles in the
publication.
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Again, why do I need to have customers table in the same publication with
invocies' tables? Since I have several publications for the invoices, each
filtering for one particular subscriber, if I add customers to the
publication, I need to add it for every publication I create. This seems
like a waste of resources. Isn't it easier to have just one publication for
the customers?
This is, of course, just a small example derived from the real world
situation. I have several 'primary key' tables (customers, articles, users,
delivery_rates, tax_rates, organizational departments, stocks,
blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
(invocies, stock documents, bills, ...). And, somehow, putting ALL those
tables within the same publication seems a bit messy. I prefer having
similair groups of tables together (for instance, publication for stock
documents has 22 articles, but, the 'primary key tables' that those tables
reference to are in it's own seperate publications).
Am I doing something wrong with my design?
Any help much appreciated!
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
The reason you would need to include all articles related by fk pk
constraints is that you might add an row to a child table which you are
replicating on the publisher where the row exists on the parent table. Then
this child row travels to the subscriber where the parent row does not
exist, and when the constraint is enforced, the transaction is rolled back
on the subscriber and publisher.
Similarly you might delete a parent record on the subscriber, and then when
it hits the publisher it might want to delete all child records belonging to
that parent row if you are not enforcing the constraint for replication, and
if you have cascading deletes and updates.
Under some circumstances you can ignore this warning.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncqtm2o.pol.majk@.fly.srk.fer.hr...
> Let's say I have a database with three tables. One for customers, and two
> for the invoices. Customers have cust_id field, wich is 'surogate' key, it
> contains the org_id where the customer was created. That table should be
> in
> merge replication because I need to be able to add data and make changes
> on
> all locations in the replication system.
> The invoices are referenced between them self with foreign key (you can't
> insert invoice_id in the detail table if the invoice_id doesn't exsist in
> master table), and the invoices table is referenced with customer table
> with
> foreign key, so you can't add cust_id to invoices wich doesn't exsist in
> customers table).
> The DDL is like this:
> CREATE TABLE [dbo].[customers] (
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoice_details] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [item_id] [int] NOT NULL ,
> [item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
> [item_price] [decimal](18, 11) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoices] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [invoice_date] [datetime] NOT NULL ,
> [remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
> CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
> (
> [cust_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
> CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
> (
> [invoice_id],
> [item_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
> CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
> (
> [invoice_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] ADD
> CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
> [rowguid]
> GO
> CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] ADD
> CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[invoices] (
> [invoice_id]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[invoices] ADD
> CONSTRAINT [FK_invoices_customers] FOREIGN KEY
> (
> [cust_id]
> ) REFERENCES [dbo].[customers] (
> [cust_id]
> ) NOT FOR REPLICATION
> GO
>
> For the purpose I'm creating two publications. One for the customers, with
> no filtering, because I need to have all the subscribers share the same
> data. The other one is for the invoices, but those are filtered within
> invoice_id, because I don't want one subscriber to have data that doesn't
> belong to it.
>
> So, I create the first merge publication, and at the end SQL server gives
> me
> this:
> This publication contains references to foreign keys outside the
> publication. The following tables are outside the publication, but
> contain
> foreign keys that are referenced from inside the publication:
> invoices (references 'customers')
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Why do I need to add invoices table to the publication?
> The other one, when creating publication for the invoices gives me more
> headache:
> This publication contains references to primary keys outside the
> publication.
> The following tables are outside the publication, but contain primary
> keys
> that are referenced from inside the publication:
> -- customers (referencing table is 'invoices')
> Although you can change existing data in the referencing tables, you
> will
> not be able to add rows to those tables. If you want to add rows to the
> referencing tables, include the referenced tables as articles in the
> publication.
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Again, why do I need to have customers table in the same publication with
> invocies' tables? Since I have several publications for the invoices, each
> filtering for one particular subscriber, if I add customers to the
> publication, I need to add it for every publication I create. This seems
> like a waste of resources. Isn't it easier to have just one publication
> for
> the customers?
> This is, of course, just a small example derived from the real world
> situation. I have several 'primary key' tables (customers, articles,
> users,
> delivery_rates, tax_rates, organizational departments, stocks,
> blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
> (invocies, stock documents, bills, ...). And, somehow, putting ALL those
> tables within the same publication seems a bit messy. I prefer having
> similair groups of tables together (for instance, publication for stock
> documents has 22 articles, but, the 'primary key tables' that those tables
> reference to are in it's own seperate publications).
> Am I doing something wrong with my design?
> Any help much appreciated!
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
|||On 2004-12-02, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> The reason you would need to include all articles related by fk pk
> constraints is that you might add an row to a child table which you are
> replicating on the publisher where the row exists on the parent table. Then
> this child row travels to the subscriber where the parent row does not
> exist, and when the constraint is enforced, the transaction is rolled back
> on the subscriber and publisher.
Yes, I'm aware of that. But, both tables do exsits on both publisher and
subscriber. The application run at subscriber can't violate constraint, so
can't application on the publisher. Constraints are created with NOT FOR
REPLICATION, so if replication job first inserts child table - it will work.
> Similarly you might delete a parent record on the subscriber, and then when
> it hits the publisher it might want to delete all child records belonging to
> that parent row if you are not enforcing the constraint for replication, and
> if you have cascading deletes and updates.
Yes, the same thing.
> Under some circumstances you can ignore this warning.
Thank you for your response. I recreated publisher/subscriber situation in
'lab', and it seems to be working fine (i just ignored the warning). I'm
able to insert data into foreign key table (as long as I have primary key
table with up-to-date data), replication works fine, everything is ok.
But, now I have another issue. Since the number of subscribers is going to
be rather high, some 200-300 subscribers (all MSDEs on laptops) i wanted to
script the subscriptions. In snapshot options for the particular publication
i choose the snapshot agent to DROP and recreate tables on the subscriber,
referential integrity included. But, publication wizzard tells me it can't
create foreign key constraints because the reffered tables are not in that
publication. Wich I understand, because, if I first push the invoices
publication to the subscriber, there is customers table missing, so, realy
no constraints to that tabe could be created.
Is there workaround for this? I need to have initial snapshots to delete all
the data on the remote side. But, putting all the tables to one publications
seems like a LOT of mess here. In my example, if I have 50 subscriptions I
should publish the customers table 50 times, instead of just once.
So, my question is again, am I doing something wrong when designing the
replication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
for the invoices. Customers have cust_id field, wich is 'surogate' key, it
contains the org_id where the customer was created. That table should be in
merge replication because I need to be able to add data and make changes on
all locations in the replication system.
The invoices are referenced between them self with foreign key (you can't
insert invoice_id in the detail table if the invoice_id doesn't exsist in
master table), and the invoices table is referenced with customer table with
foreign key, so you can't add cust_id to invoices wich doesn't exsist in
customers table).
The DDL is like this:
CREATE TABLE [dbo].[customers] (
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
[cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoice_details] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[item_id] [int] NOT NULL ,
[item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
[item_price] [decimal](18, 11) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoices] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[invoice_date] [datetime] NOT NULL ,
[remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[cust_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
(
[invoice_id],
[item_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
(
[invoice_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] ADD
CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
[rowguid]
GO
CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] ADD
CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[invoices] (
[invoice_id]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[invoices] ADD
CONSTRAINT [FK_invoices_customers] FOREIGN KEY
(
[cust_id]
) REFERENCES [dbo].[customers] (
[cust_id]
) NOT FOR REPLICATION
GO
For the purpose I'm creating two publications. One for the customers, with
no filtering, because I need to have all the subscribers share the same
data. The other one is for the invoices, but those are filtered within
invoice_id, because I don't want one subscriber to have data that doesn't
belong to it.
So, I create the first merge publication, and at the end SQL server gives me
this:
This publication contains references to foreign keys outside the
publication. The following tables are outside the publication, but contain
foreign keys that are referenced from inside the publication:
invoices (references 'customers')
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Why do I need to add invoices table to the publication?
The other one, when creating publication for the invoices gives me more
headache:
This publication contains references to primary keys outside the
publication.
The following tables are outside the publication, but contain primary keys
that are referenced from inside the publication:
-- customers (referencing table is 'invoices')
Although you can change existing data in the referencing tables, you will
not be able to add rows to those tables. If you want to add rows to the
referencing tables, include the referenced tables as articles in the
publication.
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Again, why do I need to have customers table in the same publication with
invocies' tables? Since I have several publications for the invoices, each
filtering for one particular subscriber, if I add customers to the
publication, I need to add it for every publication I create. This seems
like a waste of resources. Isn't it easier to have just one publication for
the customers?
This is, of course, just a small example derived from the real world
situation. I have several 'primary key' tables (customers, articles, users,
delivery_rates, tax_rates, organizational departments, stocks,
blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
(invocies, stock documents, bills, ...). And, somehow, putting ALL those
tables within the same publication seems a bit messy. I prefer having
similair groups of tables together (for instance, publication for stock
documents has 22 articles, but, the 'primary key tables' that those tables
reference to are in it's own seperate publications).
Am I doing something wrong with my design?
Any help much appreciated!
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
The reason you would need to include all articles related by fk pk
constraints is that you might add an row to a child table which you are
replicating on the publisher where the row exists on the parent table. Then
this child row travels to the subscriber where the parent row does not
exist, and when the constraint is enforced, the transaction is rolled back
on the subscriber and publisher.
Similarly you might delete a parent record on the subscriber, and then when
it hits the publisher it might want to delete all child records belonging to
that parent row if you are not enforcing the constraint for replication, and
if you have cascading deletes and updates.
Under some circumstances you can ignore this warning.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncqtm2o.pol.majk@.fly.srk.fer.hr...
> Let's say I have a database with three tables. One for customers, and two
> for the invoices. Customers have cust_id field, wich is 'surogate' key, it
> contains the org_id where the customer was created. That table should be
> in
> merge replication because I need to be able to add data and make changes
> on
> all locations in the replication system.
> The invoices are referenced between them self with foreign key (you can't
> insert invoice_id in the detail table if the invoice_id doesn't exsist in
> master table), and the invoices table is referenced with customer table
> with
> foreign key, so you can't add cust_id to invoices wich doesn't exsist in
> customers table).
> The DDL is like this:
> CREATE TABLE [dbo].[customers] (
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoice_details] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [item_id] [int] NOT NULL ,
> [item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
> [item_price] [decimal](18, 11) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoices] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [invoice_date] [datetime] NOT NULL ,
> [remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
> CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
> (
> [cust_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
> CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
> (
> [invoice_id],
> [item_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
> CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
> (
> [invoice_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] ADD
> CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
> [rowguid]
> GO
> CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] ADD
> CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[invoices] (
> [invoice_id]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[invoices] ADD
> CONSTRAINT [FK_invoices_customers] FOREIGN KEY
> (
> [cust_id]
> ) REFERENCES [dbo].[customers] (
> [cust_id]
> ) NOT FOR REPLICATION
> GO
>
> For the purpose I'm creating two publications. One for the customers, with
> no filtering, because I need to have all the subscribers share the same
> data. The other one is for the invoices, but those are filtered within
> invoice_id, because I don't want one subscriber to have data that doesn't
> belong to it.
>
> So, I create the first merge publication, and at the end SQL server gives
> me
> this:
> This publication contains references to foreign keys outside the
> publication. The following tables are outside the publication, but
> contain
> foreign keys that are referenced from inside the publication:
> invoices (references 'customers')
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Why do I need to add invoices table to the publication?
> The other one, when creating publication for the invoices gives me more
> headache:
> This publication contains references to primary keys outside the
> publication.
> The following tables are outside the publication, but contain primary
> keys
> that are referenced from inside the publication:
> -- customers (referencing table is 'invoices')
> Although you can change existing data in the referencing tables, you
> will
> not be able to add rows to those tables. If you want to add rows to the
> referencing tables, include the referenced tables as articles in the
> publication.
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Again, why do I need to have customers table in the same publication with
> invocies' tables? Since I have several publications for the invoices, each
> filtering for one particular subscriber, if I add customers to the
> publication, I need to add it for every publication I create. This seems
> like a waste of resources. Isn't it easier to have just one publication
> for
> the customers?
> This is, of course, just a small example derived from the real world
> situation. I have several 'primary key' tables (customers, articles,
> users,
> delivery_rates, tax_rates, organizational departments, stocks,
> blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
> (invocies, stock documents, bills, ...). And, somehow, putting ALL those
> tables within the same publication seems a bit messy. I prefer having
> similair groups of tables together (for instance, publication for stock
> documents has 22 articles, but, the 'primary key tables' that those tables
> reference to are in it's own seperate publications).
> Am I doing something wrong with my design?
> Any help much appreciated!
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
|||On 2004-12-02, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> The reason you would need to include all articles related by fk pk
> constraints is that you might add an row to a child table which you are
> replicating on the publisher where the row exists on the parent table. Then
> this child row travels to the subscriber where the parent row does not
> exist, and when the constraint is enforced, the transaction is rolled back
> on the subscriber and publisher.
Yes, I'm aware of that. But, both tables do exsits on both publisher and
subscriber. The application run at subscriber can't violate constraint, so
can't application on the publisher. Constraints are created with NOT FOR
REPLICATION, so if replication job first inserts child table - it will work.
> Similarly you might delete a parent record on the subscriber, and then when
> it hits the publisher it might want to delete all child records belonging to
> that parent row if you are not enforcing the constraint for replication, and
> if you have cascading deletes and updates.
Yes, the same thing.
> Under some circumstances you can ignore this warning.
Thank you for your response. I recreated publisher/subscriber situation in
'lab', and it seems to be working fine (i just ignored the warning). I'm
able to insert data into foreign key table (as long as I have primary key
table with up-to-date data), replication works fine, everything is ok.
But, now I have another issue. Since the number of subscribers is going to
be rather high, some 200-300 subscribers (all MSDEs on laptops) i wanted to
script the subscriptions. In snapshot options for the particular publication
i choose the snapshot agent to DROP and recreate tables on the subscriber,
referential integrity included. But, publication wizzard tells me it can't
create foreign key constraints because the reffered tables are not in that
publication. Wich I understand, because, if I first push the invoices
publication to the subscriber, there is customers table missing, so, realy
no constraints to that tabe could be created.
Is there workaround for this? I need to have initial snapshots to delete all
the data on the remote side. But, putting all the tables to one publications
seems like a LOT of mess here. In my example, if I have 50 subscriptions I
should publish the customers table 50 times, instead of just once.
So, my question is again, am I doing something wrong when designing the
replication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
Monday, March 26, 2012
Merge replication when new tables are created regularly
Hi all,
I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani
The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.
David
"Maani" <Maani@.discussions.microsoft.com> wrote in message
news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
> Hi all,
> I am trying to replicate a database (sql server 2000) to a remote site. I
> use merge replication and do almost continous replication.
> My problem is I have one application which creates atleast 6 or 7 new
> tables
> a day. Everytime it create a tables, snapshot agent restarts again and
> this
> makes the entire server slow. Also when doing this snapshot agent fails
> most
> often!
> How can I get around this issue. Anybody with insight to this issue, plz
> help me..
> Regards,
> Maani
|||So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)
"David" wrote:
> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
> database.
> David
> "Maani" <Maani@.discussions.microsoft.com> wrote in message
> news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
>
>
I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani
The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.
David
"Maani" <Maani@.discussions.microsoft.com> wrote in message
news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
> Hi all,
> I am trying to replicate a database (sql server 2000) to a remote site. I
> use merge replication and do almost continous replication.
> My problem is I have one application which creates atleast 6 or 7 new
> tables
> a day. Everytime it create a tables, snapshot agent restarts again and
> this
> makes the entire server slow. Also when doing this snapshot agent fails
> most
> often!
> How can I get around this issue. Anybody with insight to this issue, plz
> help me..
> Regards,
> Maani
|||So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)
"David" wrote:
> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
> database.
> David
> "Maani" <Maani@.discussions.microsoft.com> wrote in message
> news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
>
>
Merge Replication using joined tables and userid
I have an application where I want to use merge replication to link up with
Pocket pc's running sql sever ce. I have the queries written that will
extract the data I want to replicate but can not figure out how to set it up
in the replicate filters. Here is one of the queries.
SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
dbo.SalesCall.VendorRepCall,
dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
dbo.SalesCall.Created, dbo.SalesCall.LastModified,
dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
FROM dbo.SalesCall INNER JOIN
dbo.Account ON dbo.SalesCall.AccountID =
dbo.Account.AccountID INNER JOIN
dbo.UserTerritories ON dbo.Account.TerritoryID =
dbo.UserTerritories.TerritoryID INNER JOIN
dbo.Users ON dbo.UserTerritories.UserID =
dbo.Users.UserID
WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
GETDATE()))
I also want to replace the "cbc\dgriffith" with "cbc\" + login id of current
user.
Any Ideas? Is this even possible?
Thanks
I think so. When you create your publication, set as much of your filter
criteria that exists in the tables, then create your Joins down below for
the Accout, UserTerritories, and Users tables. Do not try to set up the
joins as part of your filter criteria.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>I have an application where I want to use merge replication to link up with
> Pocket pc's running sql sever ce. I have the queries written that will
> extract the data I want to replicate but can not figure out how to set it
> up
> in the replicate filters. Here is one of the queries.
> SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
> dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
> dbo.SalesCall.VendorRepCall,
> dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
> dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
> dbo.SalesCall.Created, dbo.SalesCall.LastModified,
> dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
> FROM dbo.SalesCall INNER JOIN
> dbo.Account ON dbo.SalesCall.AccountID =
> dbo.Account.AccountID INNER JOIN
> dbo.UserTerritories ON dbo.Account.TerritoryID =
> dbo.UserTerritories.TerritoryID INNER JOIN
> dbo.Users ON dbo.UserTerritories.UserID =
> dbo.Users.UserID
> WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
> 'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
> GETDATE()))
> I also want to replace the "cbc\dgriffith" with "cbc\" + login id of
> current
> user.
> Any Ideas? Is this even possible?
> Thanks
>
|||I guess that I still don't quite understand what part goes where.
There are two filter sections. The query I gave as an example when run from
query analyzer will return all sales calls made by dgriffith for the previous
10 days.
Thanks.
"Earl" wrote:
> I think so. When you create your publication, set as much of your filter
> criteria that exists in the tables, then create your Joins down below for
> the Accout, UserTerritories, and Users tables. Do not try to set up the
> joins as part of your filter criteria.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>
>
|||You set up the filter in two steps. In your case, you would FIRST go to the
filter on SalesCalls and select all the fields that you want and set your
WHERE criteria. It's important to note that in the first section, you do not
handle the JOINED tables at all. Next, you select the JOIN down below and
click on the tables you want to JOIN (one each line) and set up the JOIN
criteria there. Thus, you will have set up 3 JOINS in the bottom section,
one each for Accounts, Users, and SalesTerritories.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...[vbcol=seagreen]
>I guess that I still don't quite understand what part goes where.
> There are two filter sections. The query I gave as an example when run
> from
> query analyzer will return all sales calls made by dgriffith for the
> previous
> 10 days.
> Thanks.
> "Earl" wrote:
|||Thanks for the help. I figured out part of the problem. For this to work I
would have to use left or right joins as there may not be records in some of
the tables. With an inner join I end up not seeing some records. I don't see
a way to change this behavior. I am now thinking I may have to do this using
multiple merge subscriptions.
"Earl" wrote:
> You set up the filter in two steps. In your case, you would FIRST go to the
> filter on SalesCalls and select all the fields that you want and set your
> WHERE criteria. It's important to note that in the first section, you do not
> handle the JOINED tables at all. Next, you select the JOIN down below and
> click on the tables you want to JOIN (one each line) and set up the JOIN
> criteria there. Thus, you will have set up 3 JOINS in the bottom section,
> one each for Accounts, Users, and SalesTerritories.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...
>
>
sql
Pocket pc's running sql sever ce. I have the queries written that will
extract the data I want to replicate but can not figure out how to set it up
in the replicate filters. Here is one of the queries.
SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
dbo.SalesCall.VendorRepCall,
dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
dbo.SalesCall.Created, dbo.SalesCall.LastModified,
dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
FROM dbo.SalesCall INNER JOIN
dbo.Account ON dbo.SalesCall.AccountID =
dbo.Account.AccountID INNER JOIN
dbo.UserTerritories ON dbo.Account.TerritoryID =
dbo.UserTerritories.TerritoryID INNER JOIN
dbo.Users ON dbo.UserTerritories.UserID =
dbo.Users.UserID
WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
GETDATE()))
I also want to replace the "cbc\dgriffith" with "cbc\" + login id of current
user.
Any Ideas? Is this even possible?
Thanks
I think so. When you create your publication, set as much of your filter
criteria that exists in the tables, then create your Joins down below for
the Accout, UserTerritories, and Users tables. Do not try to set up the
joins as part of your filter criteria.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>I have an application where I want to use merge replication to link up with
> Pocket pc's running sql sever ce. I have the queries written that will
> extract the data I want to replicate but can not figure out how to set it
> up
> in the replicate filters. Here is one of the queries.
> SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
> dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
> dbo.SalesCall.VendorRepCall,
> dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
> dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
> dbo.SalesCall.Created, dbo.SalesCall.LastModified,
> dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
> FROM dbo.SalesCall INNER JOIN
> dbo.Account ON dbo.SalesCall.AccountID =
> dbo.Account.AccountID INNER JOIN
> dbo.UserTerritories ON dbo.Account.TerritoryID =
> dbo.UserTerritories.TerritoryID INNER JOIN
> dbo.Users ON dbo.UserTerritories.UserID =
> dbo.Users.UserID
> WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
> 'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
> GETDATE()))
> I also want to replace the "cbc\dgriffith" with "cbc\" + login id of
> current
> user.
> Any Ideas? Is this even possible?
> Thanks
>
|||I guess that I still don't quite understand what part goes where.
There are two filter sections. The query I gave as an example when run from
query analyzer will return all sales calls made by dgriffith for the previous
10 days.
Thanks.
"Earl" wrote:
> I think so. When you create your publication, set as much of your filter
> criteria that exists in the tables, then create your Joins down below for
> the Accout, UserTerritories, and Users tables. Do not try to set up the
> joins as part of your filter criteria.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>
>
|||You set up the filter in two steps. In your case, you would FIRST go to the
filter on SalesCalls and select all the fields that you want and set your
WHERE criteria. It's important to note that in the first section, you do not
handle the JOINED tables at all. Next, you select the JOIN down below and
click on the tables you want to JOIN (one each line) and set up the JOIN
criteria there. Thus, you will have set up 3 JOINS in the bottom section,
one each for Accounts, Users, and SalesTerritories.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...[vbcol=seagreen]
>I guess that I still don't quite understand what part goes where.
> There are two filter sections. The query I gave as an example when run
> from
> query analyzer will return all sales calls made by dgriffith for the
> previous
> 10 days.
> Thanks.
> "Earl" wrote:
|||Thanks for the help. I figured out part of the problem. For this to work I
would have to use left or right joins as there may not be records in some of
the tables. With an inner join I end up not seeing some records. I don't see
a way to change this behavior. I am now thinking I may have to do this using
multiple merge subscriptions.
"Earl" wrote:
> You set up the filter in two steps. In your case, you would FIRST go to the
> filter on SalesCalls and select all the fields that you want and set your
> WHERE criteria. It's important to note that in the first section, you do not
> handle the JOINED tables at all. Next, you select the JOIN down below and
> click on the tables you want to JOIN (one each line) and set up the JOIN
> criteria there. Thus, you will have set up 3 JOINS in the bottom section,
> one each for Accounts, Users, and SalesTerritories.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...
>
>
sql
Merge Replication to multiple servers losing data
Hi,
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql
Friday, March 23, 2012
merge replication Sql server 2000 with SQLCE 2.0
I do a merge replication between Sql server 2000 and SQLCE 2.0
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1:
What is the best pratice for keep the information update?
Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2:
Does someone know about some web site talk about this type of trick?
ThanksMay refer to this link http://msdn.microsoft.com/library/en-us/sqlce/htm/_lce_repl_intro_replication_architecture.asp which lists from SQL SErver CE books online.
http://csaw.biz/tips/sql-server-ce.php about KBAs with refers to CE.
http://www.winnetmag.com/SQLServer/Article/ArticleID/9004/9004.html
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1:
What is the best pratice for keep the information update?
Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2:
Does someone know about some web site talk about this type of trick?
ThanksMay refer to this link http://msdn.microsoft.com/library/en-us/sqlce/htm/_lce_repl_intro_replication_architecture.asp which lists from SQL SErver CE books online.
http://csaw.biz/tips/sql-server-ce.php about KBAs with refers to CE.
http://www.winnetmag.com/SQLServer/Article/ArticleID/9004/9004.html
Merge replication scenario - how to have inventory working properly
Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>
Merge Replication Replicate Master and related tables
hi all,
Could we configure a merge replication such as replicate the master table and its all related tables (relation deep could be 1.)
We dont want to manually find master table relations and configure replication for the related tables.
please help for that configuration
kinds
No but why would you want to?|||No, replication wizard will not find and define all the FK dependencies for you, you have to do this yourself.|||Thanks StatisticsIO.com and Greg Y for your valuable help. |||In repl UI, there is capability to create a dynamic filter on the parent table and then automatically generate the join filters based on PK-FK, do try it out in the publication wizard.sqlWednesday, March 21, 2012
Merge Replication Problems
I am having a heck of a time trying to get merge replication to work. I have two tables that consistently fail validation - even just rowcount after applying a snapshot that I created seconds before and at a time when there is no activity on the tables.
Is there a guide/info somewhere about how to troulbeshoot this? The tables are fairly straightforward (but 35,000 rows) and there are no filters set up. The remote database is connected over the internet and I was hoping to be able to get the two databases to synch continuously.
Also, when I create the merge agent, the wizard doesn't show the schedule option (i.e. whether to run continuously or on a schedule) and therefore I have to set the -continuous option on the merge agent manually.
I have been struggling with this for 2 weeks so any help/pointers would be most appreciated.
After more playing around, it appears that if I create a merge publication to a database that already has a continuous transactional subscription, I don't get the option to set the merge subscription to update the merge subscription continuously. I've now put all my tables into one big merge publication.... hopefully that will work.
Am I on the right track: in sql2000 is there some limitation that doesn't allow a continuous merge and a continuous transactional publication/subscription to and from the same publisher/subscriber database pair?
thanks....
|||I don't know if this is the answer to my own question but: it appears that trying to get continuous merge and continuous transactional replication to coexist from the same publisher database to the same subscription database was my problem.
I have no idea why, nor whether this is documented somewhere, but now I have made all of my publications continuous merge (even though transactional would seem to be the reight choice for most of the tables) gets over all the problems I was having with the merge publications continually failing.
Can anyone confirm this? Or did I unknowingly stumble upon some other fix while reconfiguring all replication from scratch?
|||if you want to do validation, you need to do it when activity on the subscriber and publisher are minimal, otherwise if changes are continuously happening on one end while validation is occuring, there's a chance you'll never be in sync.
If you stop the transactional replication, and stop all other changes, I bet your validation will pass.
Merge Replication Problems
I am having a heck of a time trying to get merge replication to work. I have two tables that consistently fail validation - even just rowcount after applying a snapshot that I created seconds before and at a time when there is no activity on the tables.
Is there a guide/info somewhere about how to troulbeshoot this? The tables are fairly straightforward (but 35,000 rows) and there are no filters set up. The remote database is connected over the internet and I was hoping to be able to get the two databases to synch continuously.
Also, when I create the merge agent, the wizard doesn't show the schedule option (i.e. whether to run continuously or on a schedule) and therefore I have to set the -continuous option on the merge agent manually.
I have been struggling with this for 2 weeks so any help/pointers would be most appreciated.
After more playing around, it appears that if I create a merge publication to a database that already has a continuous transactional subscription, I don't get the option to set the merge subscription to update the merge subscription continuously. I've now put all my tables into one big merge publication.... hopefully that will work.
Am I on the right track: in sql2000 is there some limitation that doesn't allow a continuous merge and a continuous transactional publication/subscription to and from the same publisher/subscriber database pair?
thanks....
|||I don't know if this is the answer to my own question but: it appears that trying to get continuous merge and continuous transactional replication to coexist from the same publisher database to the same subscription database was my problem.
I have no idea why, nor whether this is documented somewhere, but now I have made all of my publications continuous merge (even though transactional would seem to be the reight choice for most of the tables) gets over all the problems I was having with the merge publications continually failing.
Can anyone confirm this? Or did I unknowingly stumble upon some other fix while reconfiguring all replication from scratch?
|||if you want to do validation, you need to do it when activity on the subscriber and publisher are minimal, otherwise if changes are continuously happening on one end while validation is occuring, there's a chance you'll never be in sync.
If you stop the transactional replication, and stop all other changes, I bet your validation will pass.
merge replication problem
I need to merge data from 2 tables on different servers running on SQL server 2000. I a trying to use the merge replication function of SQL 2000 but there is One major problem. One of my server is on the internet and the other is inside our network so I dont want any data being pushed from our server on the internet to our server inside our network.
The publisher is our internal server (distributor is also on this server) and the subscriber is the internet server. I am also using the push subscription option for the subscriber. Right now our firewall blocks acces from our internet server (subscriber) to our inside server(publisher) and i get an error message from the merge agent saying the process could not connect to subscriber.
Is there any way to make it work using the merge agent ?
Thanks folksCheck this article:
http://support.microsoft.com/?id=321822
The publisher is our internal server (distributor is also on this server) and the subscriber is the internet server. I am also using the push subscription option for the subscriber. Right now our firewall blocks acces from our internet server (subscriber) to our inside server(publisher) and i get an error message from the merge agent saying the process could not connect to subscriber.
Is there any way to make it work using the merge agent ?
Thanks folksCheck this article:
http://support.microsoft.com/?id=321822
Subscribe to:
Posts (Atom)