Merge Replication and MSDE: sp4

Showing posts with label sp4. Show all posts

Friday, March 30, 2012

Merge rplication - records not being replicated.

We are replicating 5 subscriptions to 500 users with MSDE databases. All
databases are SQL Server 2000 SP4.
The issue we are seeing is that some users are not seeing all the records
they should. (Merge replication) Examples:
User A creates some new records. The records get replicated to the server
and then are not in user A's database (they should still be in the users
database). There are no conflicts. Re-building the snap shot and
re-initializing with the upload changes selected does not fix the issue.
Re-building the snap shot and re-initializing with the upload changes Not
selected does fix the issue.
Any suggestions for tracking this proble down would be appreciated.
Are you using any dynamic filters? If the added rows on A are not allowed
according to the filter, the row is outside of the partition and this could
account for such behaviour.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
We are using dynamic filters. The users are only allowed to add records
that are in the partition.
|||Then this is normal behaviour.
It would help if you explain a little more about what you would like to
happen.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication: sp_MSgetmetadatabatch Duration

I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
pull subscribers.
A few of my users are having problems with replication. This is all
done via VPN connections to our site (so they're not on our LAN.)
The merge agent fails with the error "The process could not query row
data at the 'Publisher'."
After running Profiler, it appears that the this is the longest
duration of anything run.
I can't find any real details on this stored procedure, so I'm not
real sure what its doing. I'm also NOT a replication expert. I know
enough to have gotten it running and its been working okay, but these
problems have slowly been creeping up. We can normally get the
synchronization to go through, but its frustrating to my users, and I
may be facing a mutiny! Help! I'll be glad to provide any more details
that anyone requests, and if anyone has any details on what this
stored procedure is doing or how I can find out, that would be most
appreciated!
Try setting querytimeout to a large value or using the slowlink
profile.
On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
|||On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> Try setting querytimeout to a large value or using the slowlink
> profile.
> On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
>
>
>
>
> - Show quoted text -
I've done both of those (Set QueryTimeout = 6000) and it still occurs.
|||Index fragmentation could be cuase, you should be rebuilding your merge
system table indexes on a regular basis:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"dday515@.gmail.com" wrote:

> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
>
|||On Nov 15, 1:48 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> Index fragmentation could be cuase, you should be rebuilding your merge
> system table indexes on a regular basis:
> DBCC DBREINDEX (MSmerge_contents, '', 80)
> DBCC DBREINDEX (MSmerge_genhistory, '', 80)
> DBCC DBREINDEX (MSmerge_tombstone, '', 80)
> DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
> DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
> ChrisB MCDBA
> MSSQLConsulting.com
>
> "dday...@.gmail.com" wrote:
>
>
> - Show quoted text -
I've done a full reindex as well prior, still the same problem!
|||Did you try the slow link profile?
Is it possible also that your network link is going down during your sync?
Can you run a ping -t between the publisher and subscriber to verify that
the link stays up during the sync?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Daniel Day" <dday515@.gmail.com> wrote in message
news:3d27eaf9-9105-47e0-96bc-c6afe7763da0@.v4g2000hsf.googlegroups.com...
> On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> I've done both of those (Set QueryTimeout = 6000) and it still occurs.

Wednesday, March 28, 2012

Merge Replication with vertical Filters

Hello
it's possible to create a Merge-Publication with vertical Filter on an
Sql-Server 2000 with SP3?
in our developer System with SP4 i can make a replication, but in the Target
System i didn't.
Error 20009 add the article...
Thanks
Daniel
Yes it is, but ensure that you have defaults in place for the columns you
are not replicating or that they are nullable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Dani Schwegler" <d.schwegler@.hispeed.ch> wrote in message
news:ezRNYPmUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hello
> it's possible to create a Merge-Publication with vertical Filter on an
> Sql-Server 2000 with SP3?
> in our developer System with SP4 i can make a replication, but in the
> Target System i didn't.
> Error 20009 add the article...
> Thanks
> Daniel
>
sql

Monday, March 26, 2012

Merge replication via cradle using ActiveSync

Hello. Is there any possibility to connect and proceed merge replication between SQL Mobile and SQL Server 2000 SP4 using ActiveSync? I need to deal with this situation:
- mobile application is working using self .sdf database but from time to time it connects to SQL Server and do some synchronization (synch or asynch, really doesn't matter). But, when there is no network connection (broken, suspended, wifi broken etc.) I have to create an option - Pocket PC will land into cradle and I have to synch all changes made in SDF and SQL Server using ActiveSync (or something like that - via USB).
So my question is : is there any simple mechanism to do this? Or I have to write some Active Sync manager which will be dealing with my replication? (pretty hard to do). Awaiting answer - Maciej Wysocki.

PS. I am using Windows CE 3.0 but I can setup Windows Mobile 5.0 too, I am just wondering if there is any chance to make this type of synch. working.

The merge replication over ActiveSync works since it uses http/https and ActiveSync provides pass thru http connections. You do not have to write anything for ActiveSync. Just cradle the device and try to sync it should work.If you have any problems, let me know, we can try to figure it out

Manish

|||Ok. Thanks for reply. I wanna know one more thing which I really need. Is there any chance to fire synchronization everytime when Pocket PC is put into cradle? I know that I can create a thread ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=596053&SiteID=1 ) with checking section, but it's not good solution (every new thread = less performance). So putting Pocket into cradle fires any event? Thanks in advance. Maciej Wysocki

PS. If getting Pocket out of cradle is detectable too I will be glad to get to know how can I check it.

Merge Replication updating SP3 to SP4

Gurus,
I am planning to update SP3 to SP4 on a replicated server.
A- Please correct me if i wrong in order.
1- Apply SP4 on Publisher / Distributor (on single box)
2- All subscribers.
B- As per my knowledge there are no issues while applying SP4. Is there any
known issue while applying the sp4 has to be taken care of?
TIA
Regards
Javed Iqbal
Javed,
I agree with the order. I've had a search of this newsgroup (using Google
Groups) to check for sp4 and there are hardly any reported errors so far.
The main on e is
http://groups.google.com/group/micro...85 a5a37097d4
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 23, 2012

merge replication sqlserver2000sp4 sqlce3.0 does that really work ?

Hi,

we have developped an application on pdaphone. we try to use merge replication between our sql server 2000 sp4 and sql Ce 3.0 via GPRS . Sometime that works very fine . Sometime it's a nightmare !!! nothing work . we have multiple error....

can someone tell me, what are the rules to make it work ?

can someone tell me, if it really works fine ?

thanks

Avoid complex filters and joins.

It is supported, yes, but personally I would recommend upgrading to SQL Server 2005 SP2, where many improvements have been made to Merge replication.

merge replication sqlserver2000sp4 sqlce3.0 does that really work ?

Hi,

can someone tell me, what are the rules to make it work ?

can someone tell me, if it really works fine ?

thanks

Avoid complex filters and joins.

It is supported, yes, but personally I would recommend upgrading to SQL Server 2005 SP2, where many improvements have been made to Merge replication.

Wednesday, March 21, 2012

Merge replication problem

Hi!

I have problem with merge replication between SQL Server Mobile 2005 and SQL Server 2000 SP4. In 99% situation replication is done succesfully, but sometimes some of the rows are not moved from pocket pc to my server. In this situation column __sysSR is NULL. Please help!!! I use managed code and BeginSynchronize method to start replication!

I use this code:

Private Sub repliciraj_gprs()
Try
Me.LabelStatusValue.ForeColor = Color.DarkBlue
Me.MenuItem1.Enabled = False
Me.MenuItem2.Enabled = False
'Me.ButtonSynchronize.Enabled = False
'Me.Button2.Enabled = False
'Dim connected As Boolean = False

Me.repl = New SqlCeReplication()
repl.SubscriberConnectionString = "Data Source=dokumenti.sdf"

If File.Exists("/dokumenti.sdf") = False Then

Dim Gprs As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "Gprs", "")
Dim Publisher As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "Publisher", "")
Dim Baza As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "Baza", "")
Dim User As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "User", "")
Dim Password As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "Password", "")
Dim Publication As String = Registry.GetValue("HKEY_LOCAL_MACHINE\Putnik\", "Publication", "")

repl.AddSubscription(AddOption.CreateDatabase)
repl.InternetUrl = Gprs & "/replikator/sqlcesa30.dll"
repl.Publisher = Publisher
repl.PublisherDatabase = Baza
repl.PublisherLogin = User
repl.PublisherPassword = Password
repl.Publication = Publication
repl.Subscriber = "MySubscriber"

MessageBox.Show("Nakon zavr?etka replikacije bit ?e kreirana nova baza za putnika", "Upozorenje")
Else
repl.LoadProperties()
End If

Label1.Text = "DNS: " & repl.InternetUrl & ", Izdava?: " & repl.Publisher & ", Publikacija: " & repl.Publication & ", Pretplatnik: " & repl.Subscriber

konekcija.Connect(True, ConnectionMode.Synchronous) 'Sinkrono da sa?eka kraj spajanja

Dim ar As IAsyncResult = repl.BeginSynchronize( _
New AsyncCallback(AddressOf Me.SyncCompletedCallback), _
New OnStartTableUpload(AddressOf Me.OnStartTableUploadCallback), _
New OnStartTableDownload(AddressOf Me.OnStartTableDownloadCallback), _
New OnSynchronization(AddressOf Me.OnSynchronizationCallback), repl)

Catch ex As SqlCeException
MessageBox.Show("Pojavila se gre?ka: " & ex.Message & ", provjerite konekciju", "Gre?ka pri repliciranju")
Catch x As Exception
MessageBox.Show(x.Message, "Gre?ka pri repliciranju")
End Try

End Sub

Please, anyone? Help!|||

Hi Ivica,

The post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=508786&SiteID=1 provides information about the system columns. Have you notices a particular condition when the data is not uploaded from SQL Mobile to SQL Server. The condition can be column trpe, any particular insert etc. This will help in troubleshooting the issue

Regards,

Ambrish

|||Ok that, but did somebody solved this problem already?|||same thing happens to me. build no. of my sql server 2005 is 9.0.3175

|||

I am experiencing what appears to be the same issue:

SQL CE 3.0 using merge replication against a SQL 2000 SP4 back-end.

Appears to work in 99% of cases but in some cases does not work - non-convergence.

Our mobile application performs merge replication every 5 minutes. On the deice this behaviour has been observed, the device is continuing to synchronise successfully - but the row changes that have been found to have failed never update on the server - despite being present in the mobile database table.

For each of the rows that failed, the __sysMC column has a value of 0x81 and __sysSR is null. All the rows fall into one of two values for the __sysCG column (change generation). Some of the other rows in the same table with the same change generation value DID replicate correctly however.

I have a copy of the .SDF file from the device for diagnosis if required.

The mobile device connects to the back-end server over a remote wireless network which is obviously unreliable by nature. It is entirely possible that communications could be disrupted during a synchronisation. It was my understanding that replication could handle this scenario gracefully.

The only other point worth noting is that the date on the device is set incorrectly, and so the __sysCD column has a value set back in 2005. Without knowing how merge replication works under the hood I am unsure whether this should make any difference.

I am very keen to have this issue diagnosed and am happy to provide any information necessary.

|||

It is important that the device date is properly set. My experience is that sync between SQL Mobile 3.0 and SQL Server 2000 is not very reliable, as the backend technology has not been updated since SQL Server 2000 was released. Many improvements have been made to merge sync in SQL Server 2005 (must be SP2 or later)

|||

What effect does having the wrong date have?

If the date was critical to the replication algorithm then I would expect it to fail entirely - yet what I am seeing is that a small number of rows appear not to have replicated.

Is this a known issue that is resolved in SQL Server 2005?

|||If you are filtering based on dates, you may see issues. For a workaround see http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

We aren't using any row filters based on dates - we only use static row filters that don't involve any date types.

|||

Ivica

We have a very similar issue to the one you have raised here. Our solution uses the same system components as yours and (on very rare occassions) we see that updates made to a particualr column on a particualar mobile database table are not reflected in the master database following a successful synchronisation through SQL Server Merge Replication.

Did you have any success on getting an answer to your query? If so, I would be very grateful if you could post this information on here.

Regards
David.

Merge replication problem

Hi!

I use this code:

Me.repl = New SqlCeReplication()
repl.SubscriberConnectionString = "Data Source=dokumenti.sdf"

If File.Exists("/dokumenti.sdf") = False Then

MessageBox.Show("Nakon zavr?etka replikacije bit ?e kreirana nova baza za putnika", "Upozorenje")
Else
repl.LoadProperties()
End If

Label1.Text = "DNS: " & repl.InternetUrl & ", Izdava?: " & repl.Publisher & ", Publikacija: " & repl.Publication & ", Pretplatnik: " & repl.Subscriber

konekcija.Connect(True, ConnectionMode.Synchronous) 'Sinkrono da sa?eka kraj spajanja

End Sub

Please, anyone? Help!|||

Hi Ivica,

Regards,

Ambrish

|||Ok that, but did somebody solved this problem already?|||same thing happens to me. build no. of my sql server 2005 is 9.0.3175

|||

I am experiencing what appears to be the same issue:

SQL CE 3.0 using merge replication against a SQL 2000 SP4 back-end.

Appears to work in 99% of cases but in some cases does not work - non-convergence.

I have a copy of the .SDF file from the device for diagnosis if required.

I am very keen to have this issue diagnosed and am happy to provide any information necessary.

|||

What effect does having the wrong date have?

If the date was critical to the replication algorithm then I would expect it to fail entirely - yet what I am seeing is that a small number of rows appear not to have replicated.

Is this a known issue that is resolved in SQL Server 2005?

|||If you are filtering based on dates, you may see issues. For a workaround see http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

We aren't using any row filters based on dates - we only use static row filters that don't involve any date types.

|||

Ivica

Did you have any success on getting an answer to your query? If so, I would be very grateful if you could post this information on here.

Regards
David.

sql

Merge replication problem

Hi!

I use this code:

Me.repl = New SqlCeReplication()
repl.SubscriberConnectionString = "Data Source=dokumenti.sdf"

If File.Exists("/dokumenti.sdf") = False Then

MessageBox.Show("Nakon zavr?etka replikacije bit ?e kreirana nova baza za putnika", "Upozorenje")
Else
repl.LoadProperties()
End If

Label1.Text = "DNS: " & repl.InternetUrl & ", Izdava?: " & repl.Publisher & ", Publikacija: " & repl.Publication & ", Pretplatnik: " & repl.Subscriber

konekcija.Connect(True, ConnectionMode.Synchronous) 'Sinkrono da sa?eka kraj spajanja

End Sub

Please, anyone? Help!|||

Hi Ivica,

Regards,

Ambrish

|||Ok that, but did somebody solved this problem already?|||same thing happens to me. build no. of my sql server 2005 is 9.0.3175

|||

I am experiencing what appears to be the same issue:

SQL CE 3.0 using merge replication against a SQL 2000 SP4 back-end.

Appears to work in 99% of cases but in some cases does not work - non-convergence.

I have a copy of the .SDF file from the device for diagnosis if required.

I am very keen to have this issue diagnosed and am happy to provide any information necessary.

|||

What effect does having the wrong date have?

If the date was critical to the replication algorithm then I would expect it to fail entirely - yet what I am seeing is that a small number of rows appear not to have replicated.

Is this a known issue that is resolved in SQL Server 2005?

|||If you are filtering based on dates, you may see issues. For a workaround see http://msdn2.microsoft.com/en-us/library/ms365153.aspx|||

We aren't using any row filters based on dates - we only use static row filters that don't involve any date types.

|||

Ivica

Did you have any success on getting an answer to your query? If so, I would be very grateful if you could post this information on here.

Regards
David.

Merge replication problem

Hi!

I use this code:

Me.repl = New SqlCeReplication()
repl.SubscriberConnectionString = "Data Source=dokumenti.sdf"

If File.Exists("/dokumenti.sdf") = False Then

MessageBox.Show("Nakon zavr?etka replikacije bit ?e kreirana nova baza za putnika", "Upozorenje")
Else
repl.LoadProperties()
End If

Label1.Text = "DNS: " & repl.InternetUrl & ", Izdava?: " & repl.Publisher & ", Publikacija: " & repl.Publication & ", Pretplatnik: " & repl.Subscriber

konekcija.Connect(True, ConnectionMode.Synchronous) 'Sinkrono da sa?eka kraj spajanja

End Sub

Please, anyone? Help!|||

Hi Ivica,

Regards,

Ambrish

|||Ok that, but did somebody solved this problem already?|||same thing happens to me. build no. of my sql server 2005 is 9.0.3175

|||

I am experiencing what appears to be the same issue:

SQL CE 3.0 using merge replication against a SQL 2000 SP4 back-end.

Appears to work in 99% of cases but in some cases does not work - non-convergence.

I have a copy of the .SDF file from the device for diagnosis if required.

I am very keen to have this issue diagnosed and am happy to provide any information necessary.

|||

What effect does having the wrong date have?

If the date was critical to the replication algorithm then I would expect it to fail entirely - yet what I am seeing is that a small number of rows appear not to have replicated.

Is this a known issue that is resolved in SQL Server 2005?

|||If you are filtering based on dates, you may see issues. For a workaround see http://msdn2.microsoft.com/en-us/library/ms365153.aspx|||

We aren't using any row filters based on dates - we only use static row filters that don't involve any date types.

|||

Ivica

Did you have any success on getting an answer to your query? If so, I would be very grateful if you could post this information on here.

Regards
David.

Merge Replication Problem

Hello
I have merge Replication MSSQL Server 2000 SP4, One Publisher and 5
subscribers,
a) Subscribers1 has inserted the record at 8 AM and Send it to
subscribers2 & subscribers3
b) Using DTS, but not insert directly subscribers2 DB its Inserted
TempDB to subscribers2 DB using Store procedure.
c) Subscribers2 users update same records at 3 PM. It means now other
Subscribers not update those records.
d) I changed my article
@.property='compensate_for_errors',@.value='false'.
e) I have facing some conflict Problems
f) After Synchronization those records was deleted at some
Subscriber
g) We are Synchronization at 1 AM. For Schedule bases
Here the Question.
Why the Data going to deleted?
Which data going to Synchronies in above case?
Suppose I update row 1 following time
Subscribers1 at 6 am
Subscribers1 at 7 am
Subscribers1 at 8 am
Subscribers1 at 9 am
Subscribers1 at 11 am
Which data going to Synchronies in above case?
Please Explaning Clearly
Thanks
Sam
Sam I am having problems understanding the scenario you present. Before I
start guessing at what happened I want to explain a couple of things.
a) compensate_for_errors - this switch means that if a conflict occurs the
data will not be wiped out on the conflict loser. For example if a row with
the same PK value is simultaneously inserted on the publisher and subscriber
and the compensate_for_errors switch is set to true (true is default for SQL
2000, false is the default for SQL 2005) the row on the subscriber will be
deleted and replaced with the row from the publisher. If the
compensate_for_errors setting is set to false, the row on the subscriber
will not be deleted - in other words - the subscribers row will remain.
With this setting all such compensating actions will not occur.
b) when you use DTS with the defaults the rows dts'd into the table are not
logged and will not be replicated. Consult
http://support.microsoft.com/kb/275680/en-us for more information.
c) exactly what do you mean by "Using DTS, but not insert directly
subscribers2 DB its Inserted TempDB to subscribers2 DB using Store
procedure." If you mean it was DTS'd to tempdb and then from tempdb to the
subscriber2 db, this should have been logged and any inserts or other DML as
a result of this action should be replicated.
d) did you run conflict viewer for a window into what is happening?
It sounds like what happened what that in step b the rows were not logged
and hence never made it to subscriber 1 and subscriber 3. I don't understand
what happened in f). The conflict viewer should have a record of what
happened.
To answer your questions - data should not be deleted with the
compensate_for_errors setting set to false - only true.
From what you describe in the below scenario, if you are updating the same
row the update should flow with each synchronization unless you are doing
column level tracking - this would be logged as a conflict and the conflict
winner should win the conflict - ie its data should make it to all the
subscribers.
If the sync doesn't happen each hour, only the final result will travel. For
example if the sync runs at 11 am, only the final 11 am change will move.
HTH
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Sam" <shankar@.cdscom.co.th> wrote in message
news:Os7L2bEOGHA.812@.TK2MSFTNGP10.phx.gbl...
> Hello
> I have merge Replication MSSQL Server 2000 SP4, One Publisher and 5
> subscribers,
> a) Subscribers1 has inserted the record at 8 AM and Send it to
> subscribers2 & subscribers3
> b) Using DTS, but not insert directly subscribers2 DB its Inserted
> TempDB to subscribers2 DB using Store procedure.
> c) Subscribers2 users update same records at 3 PM. It means now other
> Subscribers not update those records.
> d) I changed my article
> @.property='compensate_for_errors',@.value='false'.
> e) I have facing some conflict Problems
> f) After Synchronization those records was deleted at some
> Subscriber
> g) We are Synchronization at 1 AM. For Schedule bases
>
> Here the Question.
> Why the Data going to deleted?
> Which data going to Synchronies in above case?
>
> Suppose I update row 1 following time
> Subscribers1 at 6 am
> Subscribers1 at 7 am
> Subscribers1 at 8 am
> Subscribers1 at 9 am
> Subscribers1 at 11 am
> Which data going to Synchronies in above case?
>
> Please Explaning Clearly
> Thanks
> Sam
>
>
>
>
>
>
>
|||THANKS Hilary
I need to test , let Contact You.
Sam
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:erQqQoHOGHA.2992@.tk2msftngp13.phx.gbl...
> Sam I am having problems understanding the scenario you present. Before I
> start guessing at what happened I want to explain a couple of things.
> a) compensate_for_errors - this switch means that if a conflict occurs the
> data will not be wiped out on the conflict loser. For example if a row
> with the same PK value is simultaneously inserted on the publisher and
> subscriber and the compensate_for_errors switch is set to true (true is
> default for SQL 2000, false is the default for SQL 2005) the row on the
> subscriber will be deleted and replaced with the row from the publisher.
> If the compensate_for_errors setting is set to false, the row on the
> subscriber will not be deleted - in other words - the subscribers row
> will remain. With this setting all such compensating actions will not
> occur.
> b) when you use DTS with the defaults the rows dts'd into the table are
> not logged and will not be replicated. Consult
> http://support.microsoft.com/kb/275680/en-us for more information.
> c) exactly what do you mean by "Using DTS, but not insert directly
> subscribers2 DB its Inserted TempDB to subscribers2 DB using Store
> procedure." If you mean it was DTS'd to tempdb and then from tempdb to the
> subscriber2 db, this should have been logged and any inserts or other DML
> as a result of this action should be replicated.
> d) did you run conflict viewer for a window into what is happening?
> It sounds like what happened what that in step b the rows were not logged
> and hence never made it to subscriber 1 and subscriber 3. I don't
> understand what happened in f). The conflict viewer should have a record
> of what happened.
> To answer your questions - data should not be deleted with the
> compensate_for_errors setting set to false - only true.
> From what you describe in the below scenario, if you are updating the same
> row the update should flow with each synchronization unless you are doing
> column level tracking - this would be logged as a conflict and the
> conflict winner should win the conflict - ie its data should make it to
> all the subscribers.
> If the sync doesn't happen each hour, only the final result will travel.
> For example if the sync runs at 11 am, only the final 11 am change will
> move.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Sam" <shankar@.cdscom.co.th> wrote in message
> news:Os7L2bEOGHA.812@.TK2MSFTNGP10.phx.gbl...
>

Merge replication problem

Hi!

I use this code:

Me.repl = New SqlCeReplication()
repl.SubscriberConnectionString = "Data Source=dokumenti.sdf"

If File.Exists("/dokumenti.sdf") = False Then

MessageBox.Show("Nakon zavr?etka replikacije bit ?e kreirana nova baza za putnika", "Upozorenje")
Else
repl.LoadProperties()
End If

Label1.Text = "DNS: " & repl.InternetUrl & ", Izdava?: " & repl.Publisher & ", Publikacija: " & repl.Publication & ", Pretplatnik: " & repl.Subscriber

konekcija.Connect(True, ConnectionMode.Synchronous) 'Sinkrono da sa?eka kraj spajanja

End Sub

Please, anyone? Help!|||

Hi Ivica,

Regards,

Ambrish

|||Ok that, but did somebody solved this problem already?|||same thing happens to me. build no. of my sql server 2005 is 9.0.3175

|||

I am experiencing what appears to be the same issue:

SQL CE 3.0 using merge replication against a SQL 2000 SP4 back-end.

Appears to work in 99% of cases but in some cases does not work - non-convergence.

I have a copy of the .SDF file from the device for diagnosis if required.

I am very keen to have this issue diagnosed and am happy to provide any information necessary.

|||

What effect does having the wrong date have?

If the date was critical to the replication algorithm then I would expect it to fail entirely - yet what I am seeing is that a small number of rows appear not to have replicated.

Is this a known issue that is resolved in SQL Server 2005?

|||If you are filtering based on dates, you may see issues. For a workaround see http://msdn2.microsoft.com/en-us/library/ms365153.aspx|||

We aren't using any row filters based on dates - we only use static row filters that don't involve any date types.

|||

Ivica

Did you have any success on getting an answer to your query? If so, I would be very grateful if you could post this information on here.

Regards
David.

Monday, March 19, 2012

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Merge Replication not replicating updates

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count,*FROM sysmergearticles WHERE name LIKE'%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Wednesday, March 7, 2012

Merge replication error

Hello, everybody,
I'm using SQL 2000 SP4 on W2K & W2003 Server. In the last 2 weeks, I
encounter a very annoying problem with the replication:
"The merge process encountered an unexpected network error. The connection
to Publisher 'AR-MAIN' is no longer available" - error number -2147199469
And along with that: "General network error. Check your network
documentation."
The problem is that the error occurs only sometime.
And only occurs to the merge process on one table (no filters, table to
table replication).
The others replicate just fine...
However, it's true that this one is the most accesed table.
I have a VPN connection between the Publisher's site and the Subcriber.
But the VPN link is stable, there are no bottlenecks - only the two SQL
servers seem to have problems.
Any idea? Could it be something related to the SQL server, or should I look
elsewhere?
Thanks,
Valentin
This error is most frequently transient and caused by lossy network
connections. Try using the verbose profile to see if this shows the error
which occurs before this error which is sometimes masked by the general
network error.
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
"Valentin Holban" <ValentinHolban@.discussions.microsoft.com> wrote in
message news:D362A7BA-5D76-480E-9B94-4C145F65B520@.microsoft.com...
> Hello, everybody,
> I'm using SQL 2000 SP4 on W2K & W2003 Server. In the last 2 weeks, I
> encounter a very annoying problem with the replication:
> "The merge process encountered an unexpected network error. The connection
> to Publisher 'AR-MAIN' is no longer available" - error number -2147199469
> And along with that: "General network error. Check your network
> documentation."
> The problem is that the error occurs only sometime.
> And only occurs to the merge process on one table (no filters, table to
> table replication).
> The others replicate just fine...
> However, it's true that this one is the most accesed table.
> I have a VPN connection between the Publisher's site and the Subcriber.
> But the VPN link is stable, there are no bottlenecks - only the two SQL
> servers seem to have problems.
> Any idea? Could it be something related to the SQL server, or should I
> look
> elsewhere?
> Thanks,
> Valentin

Merge replication downloading old data to the suscriber

I'm having an error with merge replication my suscriber are downloading old data, all this start happen after I install SP4.

let explaint this with more details ,

if a made an update to a row in a table from the publisher , the agent history show that 1 change have downloaded to the suscriber, a put an audit in the suscriber and yes one update was downloaded but the row still the same, look like the suscriber downloaded old data instead of the curent change that i made.

If i delete the row, the rows gets delete at the suscriber, if a insert the row the row get insterted ok.

The problem only happen with rows inserted at the suscriber, that later on get update at the publisher. My suscriber has MSDE 2000 SP3 an my Server has SQL Enterprice 2000 SP4.

Any help would be apreciate. thxs.

After 2 weeks of research, my coworkers and me found out that the Update trigers for the publication were incorrectly createad by the sp_repladdcolumn store procedure. There is a call inside that store procedure to another store procedure call sp_MSaddmergetriggers call after the article tigers were delete with the store procedure sp_MSdroparticletriggers. Inside the sp_MSaddmergetriggers another store procedure is called sp_MSaddupdatetrigger the store procedure create the Update triger for the article related with the replication , it was made upside the sp_MSaddmergetriggers because the update triger for the replication is far more complex than the insert and delete.

Inside the sp_MSaddupdatetrigger the value for the varible inside the triger @.missingbm is pupulated from the table sysmergearticles column missing_cols but some how the value for the column was wrong. this column tell you if the amout of columns for the table is not equal to the max colmun id in the syscolumns table, this can only happen if you have deleted columns from the table. well fixing the update triger was as simple as in the sentence

set @.missingbm = 0X00

include the right value from the sysmergearticles missing_cols column instead of 0X00.

this is not an issue on sql server 2005 since the update triger does not have contants inside, it gets the values directly from the table sysmergearticles.

if you read the code inside these store procedure sp_MSaddmergetriggers and sp_MSaddupdatetrigger from the master database in the system store procedure you will see that this store procedure were made in rush. ON sql 2005 they took time to build this procedures.

|||Thanks Pedro for looking into this, I'll look into this as well to see if this is truly and bug and see if it has been addressed in any existing QFE or not. I can assure you that the stored procedure was not made into a rush, it's just that in SQL 2005 there was a lot more to take into account and in 5 years we had more time and more eyes to work on it

. Understand that you're on SQL 2000 SP4, and you most likely hit an untested or uncommon scenario, if it was very common it most likely would have been hit and reported a long time ago.|||

Thanks Greg for looking into the post. Rush or not I have no intentions of blame anybody, any software have bugs that is an inherited from complex systems and thousands of lines of code.

I would explain my scenario so you might want to use on your testing environment (I’m assuming that you work for Microsoft). As you said this is not something common and I can’t replicate the problem at will. I have a few servers with the same data base and same publication not all of the servers were affected with this problem, something that tells me that this behavior can’t be replicated easily. To make clear my problem I would say that I have a non-convergence between subscriber and publisher in a pull subscription on the update statement. If the row is inserted at the subscriber any update made at this row in the subscriber will replicate to the publisher but not the other way around , and vice versa if the row is inserted at the publisher any update in the publisher is replicated at the subscriber but the update at the subscriber for this row inserted in the publisher is not replicated at the publisher. Even if I update the row at both the publisher and the subscriber I have no conflicts and I should have since is the same rowguid.

All this start happening after I upgrade my servers to SP4 from SP3 and use the sp_repladdcolumn to add 5 columns to one of my tables. The table needs especial conditions to be involved on this behavior on my case where 5 tables with these conditions and only 2 tables were affected.

1. The tables need to have at least one columns deleted and a few added after. This means that the max(colid) from syscolumns for that table needs to be different than the count(colid) from syscolumns.

This query will tell you those tables.

SELECT *

FROM sysmergearticles

WHERE objid in (SELECT id

FROM syscolumns

GROUP BY id

HAVING max(colid) <> count(colid) )

ORDER BY name

2. the Publication need to be merge replication with pull suscribers, column tracking need to be true in the merge articles and the merge article need to have sub filter query.

3. al least one article needs to have a vertical partition , this will happen any way if you use the sp_repladdcolumn.

4. I don’t know if this is necessary or not but you may need it, the rowguid column in the merge articles can’t be at the end.

On my impresion the problem is that colv1 value (this value is in the msmerge_contents table) generated ussing the missing columns values from the mergearticles table , is generated incorrectly since the value for @.missingbm is incorrectly, and this value is use in the update trigger on this sentence.

set @.cv = { fn UPDATECOLVBM(@.cv, @.nick, @.bm, @.missingbm, { fn GETMAXVERSION(@.lineage) }) }

Another thing that update tiger for the merge articles were correclty generatead in the suscriber but incorreclty in the publisher that is why the colv1 values are diferents between the publisher ans suscribers.

I will post another comment explaing the code inside the store procudure sp_MSaddmergetriggers and sp_MSaddupdatetrigger and how this was done different in 2005 for column leveltracking merge replication

Hope this help. Regards

Pedro R. Lopez.

|||

Pedro, thanks for the feedback. If possible, could you please submit your feedback to the SQL Server Feedback site, this way it gets logged in our bugs database and is on everyone's radar:

http://connect.microsoft.com/SQLServer/Feedback

|||

I think I've run into a similar problem.

I have a table that is being replicated through merge replication. It has been a while since it was first published, so some columns have been added and removed.

A few weeks ago, SQL Server 2000 Service Pack 4 was installed on the server.

Today I got a complaint that one of the columns was not being updated in the subscriber.

While running a trace on the server, I noticed that after updating one column in the table, the stored procedure that does the updating (the sp_upd_<guid> procedure) was called as if the column "next to" the updated column was updated.

Doing some research on this problem, I came across your post

My situation is a bit different from the one described here, this is what happened at my end:

- The table was first already part of a transactional replication
- some columns were added and removed
- then the table was also published via merge replication (push) to another server / database
- one column was added to the table
- Service pack 4 was installed

Things that are different:

- The query posted at point 1) to get the suspect articles doesn't return all the articles when run on the subscriber.
- There are no pull subscribers, only one push-subscriber. No filtering is done.

The solution posted here (manually altering the @.missingbm to the value found in sysmergearticles.missing_cols in the update trigger on the table) works in the Publisher, because there the whole history of the table (added and removed columns) is known.

In the subscriber, this doesn't work, because the missing_cols doesn't contain a value other than the default (0x00). I might be able to use the values from the Publisher, but I'm not 100% sure that this would work. If I try it and it's not the right value, I might be in even deeper trouble.

This has happened on two servers (our development server, and at a client), so this may be a reproducable problem. Our server was updated from service pack 3, at the client service pack 2 was installed before updating to sp4.

So, to summarize my question: What do I have to change to make an update in the subscriber work? You seem to have done a lot of research into this problem, so you might be able to help me out.

Kind regards,

Bart Holthuijsen

|||

Bart keep in the suscriber the value 0x00 since this values is local to the database , but it confuse me because the same table structure shuold be at both the publisher and the suscriber. Now, can you test in your system if the non-convergence problem persist for new rows inserted after you made de fix ? in both direction suscriber-publisher and publusher-suscriber. for the old row that have this problem before, the problem will persist since the colv1 columns in the merge contents table is corructed, maybe deleting this rows and re-inserting again will solve your problem, I hope you know the rows with the problem, this rows are any rows inserted or updated after you install the Sp4 or since you detect the problem.

any other question let me know.

|||Here are the test results for today.

query to get columns in both tables (run at the publisher):
declare @.TableName sysname
set @.TableName = 'Werkrapportveld'
declare @.LocalTableName nvarchar(256)
SET @.LocalTableName = N'<PUBLISHER>.dbo.' + @.TableName
declare @.RemoteTableName nvarchar(256)
SET @.RemoteTableName = N'<SUBSCRIBER>.dbo.' + @.TableName

select @.TableName as TableName, s1.name as ColumnName, s1.colid as LocalColID, s2.colid as RemoteColID
FROM syscolumns s1
left outer join <SUBSCRIBER>.dbo.syscolumns s2
on s1.name = s2.name
-- uncomment this to only get columns with different ids (these are the columns that will not be updated correctly)
--and s1.colid <> s2.colid
where s1.id = object_id(@.LocalTableName)
AND s2.id = object_id(@.RemoteTableName)
order by s1.colid

Results:

TableName ColumnName LocalColID RemoteColID
Werkrapportveld WerkrapportVeldID 1 1
Werkrapportveld Omschrijving 2 2
Werkrapportveld DataTypeID 3 3
Werkrapportveld DatumVanaf 4 4
Werkrapportveld DatumTot 5 5
Werkrapportveld InvoerMedewerkerID 6 6
Werkrapportveld MutatieMedewerkerID 7 7
Werkrapportveld InvoerDatum 8 8
Werkrapportveld MutatieDatum 9 9
Werkrapportveld DataTypeFormatID 11 10
Werkrapportveld rowguid 12 11
Werkrapportveld Format 14 12

The create date of the update triggers:

<PUBLISHER data>:
select crdate
from sysobjects
where name = 'upd_6E0445E642B0409C9CB2A4115359CA75'
2006-06-01 10:48:52.140

Note: this was the day Service Pack 4 was installed

code in the update trigger:
/* only do the map down when needed */
set @.missingbm = 0x00

select missing_cols,missing_col_count,name
from sysmergearticles where name = 'WerkrapportVeld'

missing_cols missing_col_count name
0x0012 2 WerkrapportVeld

<SUBSCRIBER data>:
crdate
2006-06-01 10:49:30.220
(sp4 install date)

update trigger:
/* only do the map down when needed */
set @.missingbm = 0x00

missing_cols missing_col_count name
0x00 0 WerkrapportVeld

** We now add a new column **

exec sp_repladdcolumn
@.source_object = 'WerkrapportVeld'
, @.column = 'dummy'
, @.typetext = 'tinyint null'
, @.publication_to_add = default
, @.schema_change_script = null
, @.force_invalidate_snapshot = 1
, @.force_reinit_subscription = 0
GO

output:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_sel_6E0445E642B0409CCF919DB8A88D4CB5_pal'. The stored procedure will still be created.
/*after running the procedure, the sp_sel_6E0445E642B0409CCF919DB8A88D4CB5_pal does exist, so that warning can probably be ignored*/

<PUBLISHER data>:
crdate : 2006-11-03 12:03:52.653

update trigger:
/* only do the map down when needed */
set @.missingbm = 0x0012

missing_cols missing_col_count name
0x0012 2 WerkrapportVeld

<SUBSCRIBER data>:

crdate : 2006-11-03 12:06:12.577

update trigger:
/* only do the map down when needed */
set @.missingbm = 0x00

missing_cols missing_col_count name
0x00 0 WerkrapportVeld

Columns: same, but one exta column
TableName ColumnName LocalColID RemoteColID
Werkrapportveld dummy 15 13

Updates that happen at the publisher work, changes are replicated to the subscriber.

Subscriber updates still go wrong:
When the Format column is updated, the update procedure for rowguid is called (rowguid in the publisher has the colid of Format in the subscriber)
When the dummy column is updated, the update procedure is called for no column (all parameters are 'default'). I think this happens because the dummy column has id 13 in the subscriber. In the publisher, there is no column with that id, so no column is updated.

** Drop the column **

exec sp_repldropcolumn @.source_object = 'WerkrapportVeld'
, @.column = 'Dummy'
, @.force_invalidate_snapshot = 1
, @.force_reinit_subscription = 0
GO

<PUBLISHER data>:
crdate : 2006-11-03 12:49:59.483

update trigger:
/* only do the map down when needed */
set @.missingbm = 0x0052

missing_cols missing_col_count name
0x0052 3 WerkrapportVeld

<SUBSCRIBER data>:

crdate : 2006-11-03 12:51:39.030

update trigger:
/* only do the map down when needed */
set @.missingbm = 0x0010

missing_cols missing_col_count name
0x0010 1 WerkrapportVeld

Update at the publisher still works correctly.

Subscriber update:
When the Format column is updated, the update procedure for rowguid is called (rowguid in the publisher has the colid of Format in the subscriber)

This is still the same as before adding and removing the dummy column.

Conclusions:
The good news seems to be that the update triggers in the publisher and subscriber databases are only wrong after service pack 4 is installed (bug in the service pack scripts?). If a column is added or removed by the replication procedures after the install, the triggers will be fixed.
Unfortunately, updates that happen at the subscriber are still wrong. Columns that are updated that have a different ColID than in the publisher will update a different column, or no column at all if no column with that id exists in the publisher.

I still don't know how to fix the problem with updates on the subscriber. After adding the dummy column (which alters the update trigger), the trigger code is correct. But somewhere down the line these correct values are used to call the update stored procedure on the publisher, and there the wrong parameters are used. I haven't found out yet how the parameters for the update are determined.

I hope this will help someone determine what the problem is and hopefully find a fix.|||I don't know if anyone is still following this thread hijack, but here are some new findings.

The table trigger at the subscriber is still not correct. It has been altered to work correctly if the newly dropped column was the only column that was dropped, but that is not the case. Any further modification to the database schema seem to work okay too, they just start at the drop of the Dummy column in the example code posted above.

The table triggers for insert / update and delete can be regenerated by calling the "sp_MSaddmergetriggers" system stored procedure for the tables to do. This is easier (and safer) than manually altering the triggers.

I've made a script to do this. It recreates the triggers for all articles that have missing columns.
This should be executed at the publisher and at the subscriber(s).

DECLARE @.Error INT;
SET @.Error = 0;

DECLARE @.TableName SYSNAME;

-- create the cursor
IF(@.Error = 0)
BEGIN
DECLARE Cur_Recreate_Trigger
CURSOR LOCAL FAST_FORWARD FOR
-- get the name of the tables with missing columns
SELECT name
FROM sysmergearticles
WHERE missing_col_count > 0;
SET @.Error = @.@.Error;
END;

--Open the cursor
IF(@.Error = 0)
BEGIN
OPEN Cur_Recreate_Trigger;
SET @.Error = @.@.Error;
END;

IF(@.Error = 0)
BEGIN
-- get the first data
FETCH NEXT FROM Cur_Recreate_Trigger INTO @.TableName;
SET @.Error = @.@.Error;

-- while results found:
WHILE(@.Error = 0 and @.@.Fetch_Status <> -1)
BEGIN
IF((@.@.Fetch_Status <> -2) AND (@.Error = 0))
BEGIN
-- show the table to do
RAISERROR(@.TableName,0,1) WITH NOWAIT;
-- re-create the triggers
EXEC @.Error = dbo.sp_MSaddmergetriggers @.TableName;
END;

IF(@.Error = 0)
BEGIN
-- get the next tablename to do
FETCH NEXT FROM Cur_Recreate_Trigger INTO @.TableName;
SET @.Error = @.@.Error;
END;
END;
END;

After the script has run, the @.missingbm in the triggers have the value that can be found in the sysmergearticles table. Unfortunately, in my case, the information in the subscriber database is incorrect.

While doing some more testing, I found out that during the initial initialization, the missing_col_count column in the subscriber is the same as in the publisher. Since all further database schema modifications were done by the replication stored procedures (sp_repladdcolumn and sp_repldropcolumn) the value for missing_col_count should be the same in both the publisher and the subscriber.

The following query SHOULD return no rows:
SELECT s1.name, s1.missing_col_count, s2.missing_col_count
FROM <publisher>.dbo.sysmergearticles s1
INNER JOIN <subscriber>.dbo.sysmergearticles s2
ON s1.name = s2.name
WHERE s1.missing_col_count <> s2.missing_col_count

Unfortunately for me, some rows are returned for my databases.
I know this can be "fixed" by reinitializing the subscriber, but I'd rather not do that, because that would mean I'd have to take the database offline while doing the re-init. Just doing the re-init also doesn't guarantee that it won't happen again in the future, just that it is fixed for the moment.

Since this problem only affects a few columns in my case, I'd much rather make SQL Server update the missing_col_count and missing_cols in the sysmergearticles table and manually choose which data to use by updating the column I want to keep (update tablename set column = column where <some criteria>). Replication would take care of updating the other database.

Now I just have to find a way to have SQL Server update the sysmergearticles table.

Does anyone know how to do this?

Alternatively, I could manually update the data in the sysmergearticles table, but I haven't figured out what values to use for the columns. Plus, updating system tables is a big no-no.

Any help?|||

Bart on my opinion this is a bug, Problem that you may not be able to solve.

If is possible for you to drop the publication and recreate it again, do it. Before re-create it make sure that you re-arrange the columns in your table to have no gap's in syscolumns, you could do this by script out you table, move the data to a temporaly table and recreate the table move the data back. do this for any table that have the missing_cols > 0 on sysmergeartcicles.

after that , reinit your suscriber. having 0 in missing columns for all your replicated tables is the best.

if you find any solution please , post it into this treat.

|||

There are known bugs in SQL Server 2000 with vertical partitioning and DML.

The whole feature is redesigned in SQL Server 2005 and customers should be moving to SQL Server 2005 when they can.

If this is a serious issue, I would recommend contacting CSS.

|||Thank you both for the feedback.

I fixed this (for now) by re-initializing the merge replication. I'll try not to drop any columns until we've upgraded to SQL Server 2005. I've had my eye on some of the new features in '05 anyway, so hopefully this will convince someone further up the ladder.

I couldn't re-create the tables on the publisher to fill the gaps in the column ids, because the tables are part of another publication too. Plus: this is live data, so the users would not be very happy if I took the database down.

Friday, March 30, 2012

Merge rplication - records not being replicated.

Merge Replication: sp_MSgetmetadatabatch Duration

Wednesday, March 28, 2012

Merge Replication with vertical Filters

Monday, March 26, 2012

Merge replication via cradle using ActiveSync

Merge Replication updating SP3 to SP4

Friday, March 23, 2012

merge replication sqlserver2000sp4 sqlce3.0 does that really work ?

merge replication sqlserver2000sp4 sqlce3.0 does that really work ?

Wednesday, March 21, 2012

Merge replication problem

Merge replication problem

Merge replication problem

Merge replication problem

Merge Replication Problem

Merge replication problem

Monday, March 19, 2012

Merge Replication not replicating updates

Merge Replication not replicating updates

Merge Replication not replicating updates

Merge Replication not replicating updates

Merge Replication not replicating updates

Wednesday, March 7, 2012

Merge replication error

Merge replication downloading old data to the suscriber

Merge Replication and MSDE

Blog Archive

About Me