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

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.

Merge Replication Without Merging Client To Server

I want to SYNC the data on the server to the client, but never the client data to the server.

Is such a configuration possible with Merge Replication under SQL CE?

Thanks again!

Merge is designed to be bidirectional but RDA is intended for scenarios like yours.

Remote Data Access (RDA) with tracking turned OFF on your SQL Mobile tables is the way to solve this. You already have the IIS infrastructure in place since you're doing merge today. RDA pull the data to SQL Mobile on some schedule that supports the way your mobile users operate.

-Darren

|||

If you are talking about SQL Mobile 3.0 and SQL Server 9.0. YES :)

Choose the article type as "Download Only". This way you always get changes from SQL Server down to SQL Mobile. However, your changes in SQL Mobile will not be sent to SQL Server.

Useful MSDN URL: http://msdn2.microsoft.com/en-us/library/ms147295.aspx

Thanks,

LaxmI NRO, MSFT, SQL Mobile, Microsoft Corporation

Merge Replication using Web Sync - Proxy Auto config problem

Hi

I am trying to setup merge replication using web sync. I keep on getting the message: The Proxy Auto-configuration URL was not found. Yes I have read all the postings telling me to check my internet explorer settings concerning proxy server and auto-detection - I am not using a proxy - auto detection is unchecked.

Does anyone have another clue?

Thanks

Alex

2006-08-16 15:31:09.375 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.437 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.453 The upload message to be sent to Publisher 'CRAW' is being generated
2006-08-16 15:31:09.453 The merge process is using Exchange ID '20E2BF4F-8812-431F-8BF8-94A44B80C16E' for this web synchronization session.
2006-08-16 15:31:09.500 The Proxy Auto-configuration URL was not found.

After having no clue where to go on I decided to try it again setting it up using RMO.

Worked without even mentioning the proxy auto config

Imports Microsoft.SqlServer.Replication

Imports Microsoft.SqlServer.Management.Common

Public Class Replication

Private subscriberName As String

Private publisherName As String

Private windowsLogin As String

Private windowsPWD As String

Private publicationName As String

Private publicationDbName As String

Private subscriptionDbName As String

Private Hostname As String

Sub New(ByVal EntityID As Guid, ByVal SubscriberHost As String, ByVal PublisherHost As String, ByVal Login As String, ByVal PWD As String, ByVal Publication As String, ByVal PublicationDB As String, ByVal SubscriptionDB As String)

subscriberName = SubscriberHost

publisherName = PublisherHost

'the guid of the entity is passed as hostname to be used for filtering

Hostname = EntityID.ToString.ToUpper

windowsLogin = Login

windowsPWD = PWD

publicationName = Publication

subscriptionDbName = SubscriptionDB

publicationDbName = PublicationDB

End Sub

Sub SetupWebPull()

Dim webSyncUrl As String = "https://myhost/websync/replisapi.dll"

'Create connections to the Publisher and Subscriber.

Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)

Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.

Dim publication As MergePublication

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

subscriberConn.Connect()

' Ensure that the publication exists and that

' it supports pull subscriptions and Web synchronization.

publication = New MergePublication()

publication.Name = publicationName

publication.DatabaseName = publicationDbName

publication.ConnectionContext = publisherConn

If publication.LoadProperties() Then

If (publication.Attributes And PublicationAttributes.AllowPull) = 0 Then

publication.Attributes = publication.Attributes _

Or PublicationAttributes.AllowPull

End If

If (publication.Attributes And PublicationAttributes.AllowWebSynchronization) = 0 Then

publication.Attributes = publication.Attributes _

Or PublicationAttributes.AllowWebSynchronization

End If

' Define the pull subscription.

subscription = New MergePullSubscription()

subscription.ConnectionContext = subscriberConn

subscription.PublisherName = publisherName

subscription.PublicationName = publicationName

subscription.PublicationDBName = publicationDbName

subscription.DatabaseName = subscriptionDbName

subscription.HostName = hostname

subscription.CreateSyncAgentByDefault = True

' Specify the Windows login credentials for the Merge Agent job.

subscription.SynchronizationAgentProcessSecurity.Login = windowsLogin

subscription.SynchronizationAgentProcessSecurity.Password = windowsPWD

' Enable Web synchronization.

subscription.UseWebSynchronization = True

subscription.InternetUrl = webSyncUrl

' Specify the same Windows credentials to use when connecting to the

' Web server using HTTPS Basic Authentication.

subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication

subscription.InternetLogin = windowsLogin

subscription.InternetPassword = windowsPWD

' Create the pull subscription at the Subscriber.

subscription.Create()

Dim registered As Boolean = False

' Verify that the subscription is not already registered.

For Each existing As MergeSubscription In publication.EnumSubscriptions()

If existing.SubscriberName = subscriberName Then

registered = True

End If

Next

If Not registered Then

' Register the local subscription with the Publisher.

publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

End If

Else

' Do something here if the publication does not exist.

Throw New ApplicationException(String.Format("The publication '{0}' does not exist on {1}.", publicationName, publisherName))

End If

Catch ex As Exception

' Implement the appropriate error handling here.

Throw New ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex)

Finally

subscriberConn.Disconnect()

publisherConn.Disconnect()

End Try

End Sub

Sub PullMergeReplication()

' Create a connection to the Subscriber.

Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

conn.Connect()

' Define subscription properties.

subscription = New MergePullSubscription()

subscription.ConnectionContext = conn

subscription.DatabaseName = subscriptionDbName

subscription.PublisherName = publisherName

subscription.PublicationDBName = publicationDbName

subscription.PublicationName = publicationName

' If the pull subscription and the job exists, start the agent job.

If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then

subscription.SynchronizeWithJob()

Else

' Do something here if the subscription does not exist.

Throw New ApplicationException(String.Format("A subscription to '{0}' does not exists on {1}", publicationName, subscriberName))

End If

Catch ex As Exception

' Do appropriate error handling here.

Throw New ApplicationException("The subscription could not be synchronized.", ex)

Finally

conn.Disconnect()

End Try

End Sub

End Class

|||?

I ran into a similar problem. It could be related to certificates. How did you generate your certificate?

--
Hilary Cotter

|||it is a problem with CA certificate;
on subscriber:
1. log on as user from which you try to run sync application
(if it is a windows service log on as service account)
2. install CA certificate ("Automatically select certificate store...")
3. test web page in IE (https://site_location/replisapi.dll)
4. In "Internet Options" -> "Connections" -> "Lan Settings" uncheck "Automatically detect settings"
5. run sync app.
6. enjoysql

Merge Replication using Web Sync - Proxy Auto config problem

Hi

I am trying to setup merge replication using web sync. I keep on getting the message: The Proxy Auto-configuration URL was not found. Yes I have read all the postings telling me to check my internet explorer settings concerning proxy server and auto-detection - I am not using a proxy - auto detection is unchecked.

Does anyone have another clue?

Thanks

Alex

2006-08-16 15:31:09.375 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.437 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.453 The upload message to be sent to Publisher 'CRAW' is being generated
2006-08-16 15:31:09.453 The merge process is using Exchange ID '20E2BF4F-8812-431F-8BF8-94A44B80C16E' for this web synchronization session.
2006-08-16 15:31:09.500 The Proxy Auto-configuration URL was not found.

After having no clue where to go on I decided to try it again setting it up using RMO.

Worked without even mentioning the proxy auto config

Imports Microsoft.SqlServer.Replication

Imports Microsoft.SqlServer.Management.Common

Public Class Replication

Private subscriberName As String

Private publisherName As String

Private windowsLogin As String

Private windowsPWD As String

Private publicationName As String

Private publicationDbName As String

Private subscriptionDbName As String

Private Hostname As String

Sub New(ByVal EntityID As Guid, ByVal SubscriberHost As String, ByVal PublisherHost As String, ByVal Login As String, ByVal PWD As String, ByVal Publication As String, ByVal PublicationDB As String, ByVal SubscriptionDB As String)

subscriberName = SubscriberHost

publisherName = PublisherHost

'the guid of the entity is passed as hostname to be used for filtering

Hostname = EntityID.ToString.ToUpper

windowsLogin = Login

windowsPWD = PWD

publicationName = Publication

subscriptionDbName = SubscriptionDB

publicationDbName = PublicationDB

End Sub

Sub SetupWebPull()

Dim webSyncUrl As String = "https://myhost/websync/replisapi.dll"

'Create connections to the Publisher and Subscriber.

Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)

Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.

Dim publication As MergePublication

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

subscriberConn.Connect()

' Ensure that the publication exists and that

' it supports pull subscriptions and Web synchronization.

publication = New MergePublication()

publication.Name = publicationName

publication.DatabaseName = publicationDbName

publication.ConnectionContext = publisherConn

If publication.LoadProperties() Then

If (publication.Attributes And PublicationAttributes.AllowPull) = 0 Then

publication.Attributes = publication.Attributes _

Or PublicationAttributes.AllowPull

End If

If (publication.Attributes And PublicationAttributes.AllowWebSynchronization) = 0 Then

publication.Attributes = publication.Attributes _

Or PublicationAttributes.AllowWebSynchronization

End If

' Define the pull subscription.

subscription = New MergePullSubscription()

subscription.ConnectionContext = subscriberConn

subscription.PublisherName = publisherName

subscription.PublicationName = publicationName

subscription.PublicationDBName = publicationDbName

subscription.DatabaseName = subscriptionDbName

subscription.HostName = hostname

subscription.CreateSyncAgentByDefault = True

' Specify the Windows login credentials for the Merge Agent job.

subscription.SynchronizationAgentProcessSecurity.Login = windowsLogin

subscription.SynchronizationAgentProcessSecurity.Password = windowsPWD

' Enable Web synchronization.

subscription.UseWebSynchronization = True

subscription.InternetUrl = webSyncUrl

' Specify the same Windows credentials to use when connecting to the

' Web server using HTTPS Basic Authentication.

subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication

subscription.InternetLogin = windowsLogin

subscription.InternetPassword = windowsPWD

' Create the pull subscription at the Subscriber.

subscription.Create()

Dim registered As Boolean = False

' Verify that the subscription is not already registered.

For Each existing As MergeSubscription In publication.EnumSubscriptions()

If existing.SubscriberName = subscriberName Then

registered = True

End If

Next

If Not registered Then

' Register the local subscription with the Publisher.

publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

End If

Else

' Do something here if the publication does not exist.

Throw New ApplicationException(String.Format("The publication '{0}' does not exist on {1}.", publicationName, publisherName))

End If

Catch ex As Exception

' Implement the appropriate error handling here.

Throw New ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex)

Finally

subscriberConn.Disconnect()

publisherConn.Disconnect()

End Try

End Sub

Sub PullMergeReplication()

' Create a connection to the Subscriber.

Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

conn.Connect()

' Define subscription properties.

subscription = New MergePullSubscription()

subscription.ConnectionContext = conn

subscription.DatabaseName = subscriptionDbName

subscription.PublisherName = publisherName

subscription.PublicationDBName = publicationDbName

subscription.PublicationName = publicationName

' If the pull subscription and the job exists, start the agent job.

If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then

subscription.SynchronizeWithJob()

Else

' Do something here if the subscription does not exist.

Throw New ApplicationException(String.Format("A subscription to '{0}' does not exists on {1}", publicationName, subscriberName))

End If

Catch ex As Exception

' Do appropriate error handling here.

Throw New ApplicationException("The subscription could not be synchronized.", ex)

Finally

conn.Disconnect()

End Try

End Sub

End Class

|||?

I ran into a similar problem. It could be related to certificates. How did you generate your certificate?

--
Hilary Cotter

|||it is a problem with CA certificate;
on subscriber:
1. log on as user from which you try to run sync application
(if it is a windows service log on as service account)
2. install CA certificate ("Automatically select certificate store...")
3. test web page in IE (https://site_location/replisapi.dll)
4. In "Internet Options" -> "Connections" -> "Lan Settings" uncheck "Automatically detect settings"
5. run sync app.
6. enjoy

Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti

|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

Merge Replication Schema Sync Problems

I am using a merge publication set to synchronize schema changes. Why am I getting the following message when I try to sync after having added columns in the publication database using ALTER TABLE statements?

The schema definition of the destination table ... in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table.

Also, why doesn't reinitializing the subscription with @.upload_first = false not fix the problem?

I have encountered this problem with both merge replication and with transactional replication

When you add columns at the publisher they do not always get published correctly. You can check in the GUI for the properties of the article and will sometimes see that the new columns are not checked.

I have found that it is necessary to either use the GUI or TSQL to explicitly add the missing columns to the publication. If you have tried to merge resync before doing this then I think that you will need to reinitialise your subscriptions

In my experience this behaviour seems to happen frequently with transactional replication, but only occasionally with merge replication

reinit with upload first won't fix the problem if the merge is attempting to synchronize and enumerate data changes with different schemas on the publisher and subscriber

aero1

Merge Replication not working after 1st Sync

I am really stuck on this, if anyone has some insight into this problem
any help would appreciated...
I'll try to explain what is happening the best I can:
We have a server running Windows Advanced Server 2000 (SP4) w/ SQL
server 2000 (SP3a) (from now on Server A). I have a publication on this
machine with dynamic filters (Changing the HOST_NAME()). The
publication is sending the snapshots to another machine (desktop
machine). The Mobile agent is in the same machine as the snapshots.
The mobile application is syncing fine when hitting Server A. The sync
is done Asynchronously.
Then we have Server B. Running Windows Server 2003 (SP1) w/ SQL 2000
(SP4), same publication w/ dynamic filter however the snapshots and the
mobile agent are in this server.
The mobile application will sync the 1st time but any subsequent syncs
will not work. I check on the Replication monitor and it tells me that
the Merge was a success but the mobile application will not execute the
download table callback, it will execute the Sync callback 5 times and
not proceed in executing the download table callback.
If I change the configuration on the mobile app to point to Server A
the sync will work just fine but, if I change it back to Server B the
sync will work once then it will stop working.
Anyone have some suggestions for troubleshooting?
Update on the situation, it turns out the Sync works fine, what's
going on is that when I sync to Server A, the average sync time is 3
minutes for 2000 rows, on Server B it's taking 45 minutes to sync 1000
rows, any ideas on how to improve/troubleshoot the situation?
I also ran profiler but I have no clue what to search for. In profiler
I couln't find any issues or unless I am not looking for the right
things. Can someone tell me what I can look for in profiler if there
is anything to look for?
Specs for Server A:
CPU: 2 Pentium 3 (550 MHz each)
RAM: 3 GBs
OS: Server 2000 Advanced (SP4)
SQL 2000 SP3
Specs for Server B:
CPU: 2 Xeon Dual-Core (2.8 GHz each)
RAM: 4 GBs
OS: Server 2003 (SP1)
SQL 2000 SP4

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

merge replication generation problem

Merge Replication Web Sync - Pull subscriptions
publisher and distributor: SQL Server 2005 X64 build 2221
clients are SQL Express builld 2047
We have 3 of our clients that received the following error at the end
of a merge.
SessionStatistics:
============================
UploadInserts:4
DownloadInserts:1748
DownloadUpdates:67
DownloadDeletes:15
SchemaChanges:1
ChangeDeliveryTime:64sec
SchemaChangeandBulkInsertTime:26sec
DeliveryRate:28.66rows/sec
TotalSessionDuration:368sec
================================================== ===========
2007-04-1823:50:38.669 The merge process was unable to create a new
generation at the'Subscriber'.Troubleshoot by restarting the
synchronization with verbose history logging and specify an output
file to which to write.
Since that time, they recieve the same error message at the beginning
of every merge attempt and fail immediately:
2007-04-1900:28:06.672 The merge process was unable to create a new
generation at the 'Subscriber'...
We have marked for re-init, but they get the above message prior and
fail, before ever doing a re-init.
Has anyone seen this? Have any suggestions what to look for/at?
TIA,
jg
Hi,
I am having the same problems with Merge Replication, did you find a solution?
Cheers
Pieter
"johng@.garrisonenterprises.net" wrote:

> Merge Replication Web Sync - Pull subscriptions
> publisher and distributor: SQL Server 2005 X64 build 2221
> clients are SQL Express builld 2047
> We have 3 of our clients that received the following error at the end
> of a merge.
> SessionStatistics:
> ============================
> UploadInserts:4
> DownloadInserts:1748
> DownloadUpdates:67
> DownloadDeletes:15
> SchemaChanges:1
> ChangeDeliveryTime:64sec
> SchemaChangeandBulkInsertTime:26sec
> DeliveryRate:28.66rows/sec
> TotalSessionDuration:368sec
> ================================================== ===========
> 2007-04-1823:50:38.669 The merge process was unable to create a new
> generation at the'Subscriber'.Troubleshoot by restarting the
> synchronization with verbose history logging and specify an output
> file to which to write.
>
> Since that time, they recieve the same error message at the beginning
> of every merge attempt and fail immediately:
> 2007-04-1900:28:06.672 The merge process was unable to create a new
> generation at the 'Subscriber'...
>
> We have marked for re-init, but they get the above message prior and
> fail, before ever doing a re-init.
> Has anyone seen this? Have any suggestions what to look for/at?
> TIA,
> jg
>