Merge replication, modem connection problems

Hi,
I have problems with merge replication over modem connection.
It works fine over LAN but with modem connection I got error:
"The merge process encountered an unexpected network error.".
I have tried to modified timeout, query timeout etc. - nothing helps.
Problems started for few days ago. We have been using merge replication for
the last two years without any problems.
Additional information:
Server: SQL Server 2000, SP3, Win 2K SP4, dual processor, 1 GB Memory
150 Clients: MSDE SP3, Win 2K SP4
Anyone has seen this problem before?
Thanks and Best Regards,
Rafael
All the time. Are you using the slow link profile?
If you still get this using the slow link profile you are likely
encountering network problems, or have an unstable line. Try to drop your
packet size.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"raffe" <raffe@.community.nospam> wrote in message
news:D0096C0B-3846-4948-9835-4E293378E14D@.microsoft.com...
> Hi,
> I have problems with merge replication over modem connection.
> It works fine over LAN but with modem connection I got error:
> "The merge process encountered an unexpected network error.".
> I have tried to modified timeout, query timeout etc. - nothing helps.
> Problems started for few days ago. We have been using merge replication
for
> the last two years without any problems.
> Additional information:
> Server: SQL Server 2000, SP3, Win 2K SP4, dual processor, 1 GB Memory
> 150 Clients: MSDE SP3, Win 2K SP4
> Anyone has seen this problem before?
> Thanks and Best Regards,
> Rafael
|||Thanks for your answer.
I have tested with slow link profile, I have creted my own profile with very
small packet size and nothing helps. I now that it worked fine last week and
there is still a lot of clients without any replicatiopn problems.
Raffe
"Hilary Cotter" wrote:

> All the time. Are you using the slow link profile?
> If you still get this using the slow link profile you are likely
> encountering network problems, or have an unstable line. Try to drop your
> packet size.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "raffe" <raffe@.community.nospam> wrote in message
> news:D0096C0B-3846-4948-9835-4E293378E14D@.microsoft.com...
> for
>
>
|||can you start logging to try to find out exactly why it is timing out like
this?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"raffe" <raffe@.community.nospam> wrote in message
news:ACCC9978-A042-40B3-8A96-3168A77CF4ED@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> I have tested with slow link profile, I have creted my own profile with
> very
> small packet size and nothing helps. I now that it worked fine last week
> and
> there is still a lot of clients without any replicatiopn problems.
> Raffe
> "Hilary Cotter" wrote:
|||How can I enable logging for merge agents?
"Hilary Cotter" wrote:

> can you start logging to try to find out exactly why it is timing out like
> this?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "raffe" <raffe@.community.nospam> wrote in message
> news:ACCC9978-A042-40B3-8A96-3168A77CF4ED@.microsoft.com...
>
>
|||Hi Raffe,
I think Hilary means the Agent Logs described in the following knowledge
base article
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/kb/312292
However, I would like to set your expectation that replication issues tend
to be very complex and hard to troubleshoot in newsgroups. If you need
instance and insight assistance, I recommend that you open a Support
incident with Microsoft Product Support Services (PSS) so that a dedicated
Support Professional can work with you in a more timely and efficient
manner. If you need any help in this regard, please let me know.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Merge Replication with only a subset of data at BOTH subscriber and publisher

I have a merge replication process (on test data) that is moving a
subset of data from one region to a central office. Now the central
office has it's own existing data, prior to initializing the merge
replication from this publisher.
Basically, when a row that existed prior to initialization is updated
at the subscriber, one that does not meet both a direct row filter and
a join filter, it is still being replicated back to the publisher, the
publisher looks like it then deletes all related records based on the
join filters because that row did not meet the criteria.
Am I trying to make merge rep do something that it does not do? I hope
that I am able to keep one subset of data in the merge process, and
have independent data on both the publisher AND subscriber.
Any help/direction is greatly appreciated.
Tony,
to have independant sets of data without truely editing the merge triggers
you really need to partition it and have separate publications. Views can be
used to amalgamate the data if needed. You can use 'Instead Of' triggers or
Partitioned Views to make them updatable.
HTH,
Paul Ibison
|||Paul,
Thanks for the information, I (stupidly) did not even consider that
possibility. I am going to set up a test here, and I might get back to
you if I run into any issues doing so.
Thanks for the insight!
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Paul, I did setup a test using views to partition off the data that I
want to publish, however it looks like when I publish those alone with
Merge replication that the data is not being transferred. The schema for
the views was initialized properly, but I think I am missing something.
You reference 'partitioned views'. Do I need to do something to the
views on the publisher in order to make changes to the data replicate
over?
Thanks in advance,
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
I didn't intend you to create the view on the publisher :-). This is an
avenue you could go down if you use an indexed view, but it is an overhead
you don't require. All you need to do is to create separate publications.
Each one has a filter to take the rows you are interested in - effectively
to partition the table. These publications will be sent ot the subscriber
and created there as 2 separate tables. If you need to report/query these
tables on the subscriber as though they were one table, you can use views on
the subscriber for this. These subscriber views will be unions and if they
need to be updatable then you could use 'instead of' triggers or partitioned
views.
HTH,
Paul Ibison
|||Paul,
The one problem is that I can not change the schema at the subscriber
nor the publisher, as they are established as well as the data that we
are working with. Obviously, I can add to the schema, which is why I
took the indexed view comment from your response. Currently applications
access the tables directly, and they expect this replicated data to end
up there one way or another.
Basically, if I could replicate just a view from each Publisher to the
central Sub, and have the views seperate the data logically from one
another, then the Subscriber could still work with the data in the table
underneath without having to worry about filters which are not being
evaluated.
This make any sense to you, or am I off the beaten path here?
Thanks again,
Tony D
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
on the publisher you won't need to change the schema, as you can separate
the table logically into two publications using row filters. On the
subscriber you'll have schema changes (additions) which can be transparent
to the user. Each publication replicates to a separate table. These could be
tables X and Y. The original table name is recreated on the subscriber as a
view which amalgamates (unions) the X and Y data. So from the subscriber's
point of view nothing has changed. However this view will only be editable
if you use an 'instead of' trigger or use a partitioned view. Either of
these mechanisms will filter the change into the respective replicated
table.
You mention having the 2 indexed views on the publisher, but they cannot
(easily) be replicated to the same table on the subscriber. You'll also lose
control of which changes are sent back to the publisher.
HTH,
Paul
|||Ok, I understand that so far. One question about the view on the
subscriber which amalgamates the data. You say to make this editable I
could make it a partitioned view. Is that just using 'With
Schemabinding', or do I need to index it also?
Thanks for your time Paul, this has been a help!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||One other hitch using different table names, currently all involved
tables at both the sub and pub have the same names. Is it at all
possible to publish a table so that it is replicated to a table with a
different name at the subscriber?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
have a look at the @.destination_table parameter in sp_addarticle.
HTH,
Paul Ibison
sql

Merge Replication using SQL-DMO

I am in a process of learning Replication in MSDE, especially Merge Replication

Server runs on MS-XP Professional
------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName : ReplTestDB
Table Name : TestTable
MSDE Instance Name : SVR\MYINSTANCE

Now I would like to know how I can configure this database for merge replication
using SQL-DMO

Laptop runs on MS-XP Professional
------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.

MSDE Instance Name : LPT\MYINSTANCE

My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.

How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.

I dont have enterprise manager in both computers since they use only MSDE

Can anyone help me?

Thanks in advance

JPCheck out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).|||The problem with BOL is it is so extensive and the scenario of my situation is not described. I could do replication in the same machine as distributor, publisher and suscriber, but what if the subscriber is another machine as in my case ?|||I found this within these forums:

The sample code you were after is for creating heterogenous publication. It does not work with SQL publications. Try to model after the following sample.

Sub CreateMergePub() Dim osvr As New SQLDMO.SQLServer Dim szLastErrorText As String

Dim lLastErrorNumber As Long
Dim szServerName As String
Dim oReplicationDatabase As SQLDMO.ReplicationDatabase
Dim oDistPublisher As New DistributionPublisher
Dim oMergePublication As New MergePublication
Dim oMergeArticle1 As New SQLDMO.MergeArticle

On Error GoTo ErrorHandler

szServerName = "your servername"

' To use NT integrated security ' osvr.LoginSecure = True

osvr.Connect szServerName

' Enable pubs database for publishing ' Set oReplicationDatabase =
osvr.Replication.ReplicationDatabases("pubs")
'oReplicationDatabase.EnableTransPublishing = True

' Set MergePublication properties, name, snapshot method, trans '
oMergePublication.Name = "Pub1" oMergePublication.SnapshotMethod =
SQLDMOInitSync_BCPNative

' Set non-default properties, i.e. if you do not set the following properties, '
default value will be used ' 'oMergePublication.PublicationAttributes =
SQLDMOPubAttrib_AllowPull Or SQLDMOPubAttrib_AllowAnonymous
'oMergePublication.RetentionPeriod = 64
'oMergePublication.SnapshotSchedule.FrequencyType = SQLDMOFreq_Daily
'oMergePublication.SnapshotSchedule.FrequencyInter val = 1
'oMergePublication.SnapshotSchedule.ActiveStartTim eOfDay = 233000
'oMergePublication.SnapshotSchedule.ActiveEndTimeO fDay = 235959
'oMergePublication.SnapshotSchedule.ActiveStartDat e = 0
'oMergePublication.SnapshotSchedule.ActiveEndDate = 19981205

oReplicationDatabase.MergePublications.Add oMergePublication

' Add article ' oMergeArticle1.Name = "Article1" oMergeArticle1.SourceObjectName
= "jobs" oMergeArticle1.SourceObjectOwner = "dbo"

'Add the article to the publication ' oMergePublication.MergeArticles.Add
oMergeArticle1

' Disconnect from SQL Server szServerName ' osvr.DisConnect Set osvr =
Nothing End

ErrorHandler:

szLastErrorText = Err.Description lLastErrorNumber = Err.Number

MsgBox szLastErrorText, vbOKOnly, "Error " + Trim$(Str$(Err.Number))

End Sub

--
Pung Xu, Microsoft This posting is provided "AS IS" with no warranties, and confers
no rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.

"matt" <matt_e_davis@.hotmail.com> wrote in message
news:d0db2ad5.0205221240.7c427ed7@.posting.google.c om...
> I am having trouble creating a merge publication with SQL-DMO. I modeled my
> solution after the following code:
> --================================================ Sub CreatePublication() '
> Connect to SQL Server Distributor Dim oSqlServer As New SQLDMO.SQLServer
> oSqlServer.Connect "", "sa", "" On Error GoTo ErrorHandler
>
> Dim oSamplePublisher As DistributionPublisher Set oSamplePublisher =
>
oSqlServer.Replication.Distributor.DistributionPub lishers("SAMPLEPUBLISHER")
>
> ' Create Sample Publication Dim oSamplePublication As New
> DistributionPublication oSamplePublication.Name = "SamplePublication"
> oSamplePublication.PublicationDB = "SampleDatabase"
> oSamplePublication.PublicationType = SQLDMOPublication_Transactional
> oSamplePublication.VendorName = "Sample Vendor"
> oSamplePublication.LogReaderAgent = "SampleLogReaderAgent"
> oSamplePublication.SnapshotAgent = "SampleSnapShotAgent"
> oSamplePublication.Description = "Sample Publication Definition"
> oSamplePublication.PublicationAttributes = SQLDMOPubAttrib_AllowPush
>
> ' Add the Publication oSamplePublisher.DistributionPublications.Add
> oSamplePublication
>
> ' Create Sample Articles Dim oSampleArticle1 As New DistributionArticle
> oSampleArticle1.Name = "SampleArticle1" oSampleArticle1.Description = "Sampe
> Article1 Definition" oSampleArticle1.SourceObjectName = "SampleTable1"
>
> Dim oSampleArticle2 As New DistributionArticle oSampleArticle2.Name =
> "SampleArticle2" oSampleArticle2.Description = "Sample Article2 Definition"
> oSampleArticle2.SourceObjectName = "SampleTable2"
>
> ' Add the Articles to the Publication
> oSamplePublication.DistributionArticles.Add oSampleArticle2
> oSamplePublication.DistributionArticles.Add oSampleArticle1
>
> oSqlServer.Close Exit Sub ErrorHandler: PrintErrors oSqlServer Exit Sub End Sub
>
> --================================================ This does create a publication
> but when you right click (via Enterprise Mgr.) Replication Monitor > PublisherName
> > SamplePublication and select Properties an error occurs. The error states that
> the publication is not in the TransPublication collection. The same error occurs
> when you create a merge publication. Except the error is that the publication does
> not exist in the MergePublications collection.
>
> How can I add the publication to the MergePublications collection?? Does anyone
> have any sample code for this?
>
> I am new at SQL-DMO and VB so I am struggling.
>
> Thanks in advance for any help you can give!!
>
> Matt|||Hi
Thanks for the reply.
I solved my problem...
I think the following posting will be helphul to someone ...
The text is too long , so posted in 3 parts

Scenario.. PART 1 SERVER COMPUTER
Desktop - Installed Access 2003 & MSDE 2000
Machine Name - MYSVR
MSDE instance Name - 'MYSVR\MYINSTANCE'
User id - 'sa' Password - 'password'

1. Create an Access Project 'TestProj.adp'
2. Create a SQL Database 'TestDB'
3. Create Table Name 'TestTable'
Columns - TestID INT IDENTITY(1,1) PRIMARY KEY
- TestName NVARCHAR(50)
4. Add a record to the table
5. Create a text TestRep01.txt and paste the following
Note that machinename should be replaced with your machine name

/*Script to be copied */

use master
GO
exec sp_adddistributor @.distributor = @.@.servername, @.password = N''
GO
-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @.profile_id = 1
GO
sp_MSupdate_agenttype_default @.profile_id = 2
GO
sp_MSupdate_agenttype_default @.profile_id = 4
GO
sp_MSupdate_agenttype_default @.profile_id = 6
GO
sp_MSupdate_agenttype_default @.profile_id = 11
GO
-- Adding the distribution database
exec sp_adddistributiondb @.database = N'distribution',
@.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.data_file = N'distribution.MDF',
@.data_file_size = 3,
@.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.log_file = N'distribution.LDF',
@.log_file_size = 3,
@.min_distretention = 0,
@.max_distretention = 72,
@.history_retention = 48,
@.security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @.publisher = @.@.servername,
@.distribution_db = N'distribution',
@.security_mode = 1,
@.working_directory = N'\\MYSVR\C$\Program Files\Microsoft SQL Server\MSSQL$MYSINSTANCE\ReplData',
@.trusted = N'false',
@.thirdparty_flag = 0
GO

/*script ends*/

5. Create a folder 'ReplData' under 'C:\Program Files\Microsoft SQL
Server\MSSQL$MYINSTANCE' if it is not there.
6. Make sure SQLServer agent is running, if not, start that now.
7. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep01.txt
-o ResultTestRep01.txt -b
8. If so far so good , copy the follwoing script to another text file TestRep02.txt

/* Script starts*/
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'TestDB',
@.optname = N'merge publish',
@.value = N'true'
GO
use [TestDB]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'TestPub',
@.description = N'Merge publ of TestDB.',
@.retention = 14,
@.sync_mode = N'character',
@.allow_push = N'true',
@.allow_pull = N'true',
@.allow_anonymous = N'true',
@.enabled_for_internet = N'false',
@.centralized_conflicts = N'true',
@.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false',
@.ftp_port = 21,
@.ftp_login = N'anonymous',
@.conflict_retention = 14,
@.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false',
@.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = 'TestPub',
@.frequency_type = 4,
@.frequency_interval = 1,
@.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 1,
@.frequency_subday_interval = 5,
@.active_start_date = 0,
@.active_end_date = 0,
@.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959,
GO
exec sp_grant_publication_access @.publication = N'TestPub',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'TestPub',
@.article = N'TestTable',
@.source_owner = N'dbo',
@.source_object = N'TestTable',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000FFF1,
@.article_resolver = null,
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
/*Script Ends */

9. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep02.txt
-o ResultTestRep02.txt -b

10. If this works fine (check the ResultTestRep02.txt for errors) the proceed

Any doubts and corrections are welcome

Cheers
Jos|||Part 2 of hte previous positing

In server computer , TestProj application

11. Create a form 'TestReplication'
13. Create reference to objects SQLMerge, SQLDSnapshot
and SQLReplError
14. Add
-- Commnad buttons 'cmdSnapShot', 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'

Copy the Code and paste in the VBA
Option Explicit
Private WithEvents SQLMerge As SQLMerge
Private WithEvents SQLMrgSnapshot As SQLSnapshot

Private Sub Form_Load()

Set SQLMerge = New SQLMerge
Set SQLMrgSnapshot = New SQLSnapshot

SQLMerge.Publisher = "MYSVR\MYINSTANCE"
SQLMerge.PublisherSecurityMode = NT_AUTHENTICATION
SQLMerge.PublisherDatabase = "TestDB"
SQLMerge.Publication = "TestPub"

SQLMrgSnapshot.Publisher = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.PublisherDatabase = "TestDB"
SQLMrgSnapshot.Distributor = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.Publication = "TestPub"
SQLMrgSnapshot.DistributorSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.PublisherSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.ReplicationType = MERGE

Exit Sub

End Sub

Private Sub cmdMergePub_Click()
On Error GoTo Failure
Dim replerr As SQLReplError

ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Replication."
DoEvents

'Initialize
SQLMerge.Initialize
'Run
SQLMerge.Run
'Terminate
SQLMerge.Terminate

'Reset objects
ProgressBar.Value = 0

Exit Sub
Failure:
For Each replerr In SQLMerge.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr

ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub

Private Sub cmdSnapshot_Click()
On Error GoTo Failure
Dim replerr As SQLReplError

ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Snapshot Generation."
DoEvents

'Initialize
SQLMrgSnapshot.Initialize

'Run
SQLMrgSnapshot.Run

'Terminate
SQLMrgSnapshot.Terminate

'Reset objects
ProgressBar.Value = 0
Exit Sub

Failure:
For Each replerr In SQLMrgSnapshot.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub

Private Sub PrintErrors(c As Object)
If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "SQL Replication Sample Failure"
End If
End Sub

Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message

'Allow other events
DoEvents

SQLMerge_Status = SUCCESS

End Function

Private Function SQLMrgSnapshot_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message

'Allow other events
DoEvents

'Setting the return code to CANCEL will cause the control to cancel operation
SQLMrgSnapshot_Status = SUCCESS

End Function

15. Now Run only Snapshot by clicking cmdSnapshot button

Continues|||//////////////////////////////////////////////////////////////////////////////////
CLIENT COMPUTER
/////////////////////////////////////////////////////////////////////////////////

16. If success go to your next connected computer
Installed MS-Access 2003 and MSDE 2000
Computer name 'MYLAPTOP'
MSDE instance Name 'MYLAPTOP\MYINSTANCE'
user id 'sa'
passwor 'password'
17. Create an access project TestProjClient
18. Create a SQL Database 'TestDBClient'
Here you do not need to create tables
19. Add a Form TestReplicationClient
20. Create reference to objects SQLMerge and SQLReplError

21. Add
-- Commnad buttons 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'

22. Copy paste the following in the VBA code
'**********************************************

Option Explicit
Private WithEvents SQLMrgSnapshot As SQLSnapshot

Private Sub Form_Load()
Set SQLMerge = New SQLMerge

End Sub

Private Sub cmdMergePub_Click()

with SQLMerge
'------------------
'Set the publisher properties
.Publisher = "MYSVR\MYINSTANCE"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "sa"
.PublisherPassword = "password"
.PublisherDatabase = "TestDB"
.Publication = "TestPub"
'------------------
.PublisherAddress = "MYSVR"
.PublisherNetwork = DEFAULT_NETWORK

'------------------
'Set the distributor properties
' No need to set Distribution Server when both
' Publisher and subscriber are same Server

'------------------
'Set your local subscriber properties
.Subscriber = "MYLAPTOP\MYINSTANCE"
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberDatasourceType = SQL_SERVER
.SubscriberLogin = "sa"
.SubscriberPassword = "password"
.SubscriberDatabase = "TestDBReplica"
.SubscriptionType = ANONYMOUS
'------------------

'------------------
.Initialize

.Run

.Terminate
'------------------

End With
exit sub
ErrH:
Set SQLMerge = Nothing
MsgBox "Unexpected error occured during Merge Process" & _
vbCrLf & Err.Description, vbCritical, "Replication"

End Sub

Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message

'Allow other events
DoEvents

SQLMerge_Status = SUCCESS

End Function

'**********************************************
23. Click cmdMerge

SUCCESS??????
if yes

24. Open the table and add one more record

25. now click cmdMerge again

26. Go to the 1st computer (server computer) and see whether newly added record in the laptop is in the TestProj

DONE

Pls reply if any doubts
Cheers
Jos|||Attached txtx file for scenario on which i solved the replication problem

All comments are corrections are appreciated

cheers
Jos

Merge Replication stopping

Hi,
We have Merge Continuous replication. Every couple of days the Merge
agent stops with an error:
The process could not enumerate changes at the 'Publisher'. Source:
Merge Replication Provider (Agent); Error number: -2147200999
We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
SP4
Has anyone seen this?
Thanks,
Katrin
Yep. The merge agent (replmerg.exe) was either excessively blocked or
deadlocked with another process. How many rows do you have in your
MSmerge_contents and MSmerge_genhistory tables on both publisher and
subscriber?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"katrin" <katrinkump@.gmail.com> wrote in message
news:1143825489.438770.13630@.i39g2000cwa.googlegro ups.com...
> Hi,
> We have Merge Continuous replication. Every couple of days the Merge
> agent stops with an error:
> The process could not enumerate changes at the 'Publisher'. Source:
> Merge Replication Provider (Agent); Error number: -2147200999
> We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
> SP4
> Has anyone seen this?
>
> Thanks,
> Katrin
>

Merge Replication stopping

Hi,
We have Merge Continuous replication. Every couple of days the Merge
agent stops with an error:
The process could not enumerate changes at the 'Publisher'. Source:
Merge Replication Provider (Agent); Error number: -2147200999
We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
SP4
Has anyone seen this?
Thanks,
Katrin
normally this error is transitory and will go away on subsequent syncs. If
it does not enable logging to determine if there is another error which is
masked by this error.
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
<katrinkump@.gmail.com> wrote in message
news:1143825341.447084.133130@.j33g2000cwa.googlegr oups.com...
> Hi,
> We have Merge Continuous replication. Every couple of days the Merge
> agent stops with an error:
> The process could not enumerate changes at the 'Publisher'. Source:
> Merge Replication Provider (Agent); Error number: -2147200999
> We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
> SP4
> Has anyone seen this?
>
> Thanks,
> Katrin
>

Merge Replication SQL2000 Error 2812

Hi !

When trying to start a Merge Replication agent I get the following Error message:

The process could not enumerate the changes at the subscriber. 2812

The snapshot agent works fine as far as I can see.

The replication is set up between a Win2000 / SQL7 / SP 4 and a Win2003 / SQL2000 / SP 3a machine. Sqlserveragent on both machines is run as a system account.

Any tip is welcome!

Thanks
VincentJSProbably it was deadlock or merge agent cannot lock what does it need during enumerating. It is normal thing because of database activity. Try to repeat it and if problem still presents - check all processes by profiler and find where agent stops. Sometimes agent continue to work even if everything is red in EM.

Also you have to set up da omain account for sql server and an sql agent - I wonder how it is working at all (replication) under a local account.|||Hi snail!

I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.

It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)

With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:

Process could not enumerate changes at subscriber. Error 2812

Some additional information is that the merge agent stops with:

call sp_MSEnumerateChanges(?,?,?,?,?)

I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.

Any more suggestions?

Thanks a lot anyway for your help!

Greetings,
VincentJS|||Are you trying to replicate a publisher to a subscriber with different schema on both side?

Originally posted by VincentJS
Hi snail!

I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.

It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)

With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:

Process could not enumerate changes at subscriber. Error 2812

Some additional information is that the merge agent stops with:

call sp_MSEnumerateChanges(?,?,?,?,?)

I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.

Any more suggestions?

Thanks a lot anyway for your help!

Greetings,
VincentJS|||Hello!

That's a good question. I've run and rerun so many tests (but always cleaning up afterwards with sp_mergesubscription_cleanup) that honestly at this stage i couldn't give you a clear answer on that point.

I've reinitialized the subscription a lot of times though and that didn't do it either.

I DID try to publish another table with merging (to another database) and that produced the same error type, despite the fact that I used a brandnew subscription with a totally different file.

Did this answer your question?

Thanks for your help!
VincentJs|||You mentioned about the brand new file, does that mean you have a different schema on the subscriber and trying to get replicated with the publisher with another schema and setup the merge without reinitialize the whole db.

I recalled I have the same problem before with that error message, what I did is to try to recreate my subscriber database and then leave it empty as it is until I create the snapshot to pull over to the subscriber by initiializing snapshot. Then it work properly.You can give a try but, please backup your subscriber database in case you need to restore back the database.|||Hi!

I wrote file but i should have written Table (what's in a name right?). Anyway, no i won't try what you suggested until only the very last moment when all else fails. At this moment its too risky and although i do have backups of all databases, i want to avoid "contaminating" or reinstallations as much as possible.

In the meantime i ran the merge agent in verbose-mode and i got the following messages (simplified):

REPLAGENT STATUS: 3
DistribServer.Pubdb: call {sp_MSenumcolums (?,?)}
SubscrServer.Subdb: call {sp_MSenumchanges (?,?,?,?,?)}
Percent complete: 0
The process could not enumerate changes at the subscriber
REPLAGENT STATUS: 6
Percent complete: 0
Category: COMMAND
Source: failed command
Number:
Message: call sp_MSenumChanges(?,?,?,?,?)

There's more of course but I think this covers the essence. Basically ALL the commands preceding the error ran normally... only at the (presumably) last step mentionned hereabove the merge agent fails.

Have you got any clue what's going wrong?

Thanks! I'll see you tomorrow i hope... It's quite late now here and i'm going home... I've got to eat and sleep sometimes :)

VincentJSsql

Merge Replication Problem (Urgent)

Dear All,
I have an error message when I'm trying to synchronize from SQL Server
Ce to SQL Server 2000.
The error message is
"The process could not deliver the snapshot to the subscriber"
I already know that I need to verify that the IIS user your SQL Server
CE Server Agent is running under has READ permissions to this folder.
But I don't know how to verify that?
You can see my sqlserverce authentication and IIS configuration on the
attachement.
Pls give me some ideas to solve it out?
Thanks
Robert Lie
Have a look at
http://msdn.microsoft.com/library/de...bleconnect.asp
"Robert Lie" wrote:

> Dear All,
> I have an error message when I'm trying to synchronize from SQL Server
> Ce to SQL Server 2000.
> The error message is
> "The process could not deliver the snapshot to the subscriber"
> I already know that I need to verify that the IIS user your SQL Server
> CE Server Agent is running under has READ permissions to this folder.
> But I don't know how to verify that?
> You can see my sqlserverce authentication and IIS configuration on the
> attachement.
> Pls give me some ideas to solve it out?
> Thanks
> Robert Lie
>
>
>
>
|||You need to use an alternate snapshot share which you have specified in your
vd creation wizard. I would use anonymous authentication in your code.
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
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:u%23dxwxhoFHA.2904@.tk2msftngp13.phx.gbl...
> Dear All,
> I have an error message when I'm trying to synchronize from SQL Server
> Ce to SQL Server 2000.
> The error message is
> "The process could not deliver the snapshot to the subscriber"
> I already know that I need to verify that the IIS user your SQL Server
> CE Server Agent is running under has READ permissions to this folder.
> But I don't know how to verify that?
> You can see my sqlserverce authentication and IIS configuration on the
> attachement.
> Pls give me some ideas to solve it out?
> Thanks
> Robert Lie
>
>
>
>


sql

Merge Replication Not Accounting For Dependencies

We have a SQL Serrver 2000 database that utilizes merge replication. Every
now and then the replication process attempts to insert or update tables out
of order. For instance: We have a TransactionDocuments table that has a one
to many relationship with a TransactionLines table. Sometimes SQL Server will
attempt to instert new TransactionLines before it has inserted the
TransactionDocuments the TransactionLines depend on.
I thought merge replication was supposed to keep track of these
dependencies. If so, is there a bug that has a workaround or something else I
need to check?
Thanks,
Matt
Matt,
you could set the NOT FOR REPLICATION attribute on the FKs to true. In SQL
server 2005 it is possible to specify the merge replication order but not so
in SQL 2000. Usually the order selected in SQL Server 2000 is correct but
not always. Please check these articles:
http://www.replicationanswers.com/Me...derArticle.asp
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Merge replication has a rich set of features regarding dependencies, but
these are typically for sensing join filters. As Paul has pointed out child
records can be inserted before parent records, but normally these are
resolved within a batch and if not the next time the agent runs the
dependencies will be fixed as the parent will get in before the child the
second time around.
The generic work around is to use the not for replication switch on all
constraints.
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
"Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
> We have a SQL Serrver 2000 database that utilizes merge replication. Every
> now and then the replication process attempts to insert or update tables
> out
> of order. For instance: We have a TransactionDocuments table that has a
> one
> to many relationship with a TransactionLines table. Sometimes SQL Server
> will
> attempt to instert new TransactionLines before it has inserted the
> TransactionDocuments the TransactionLines depend on.
> I thought merge replication was supposed to keep track of these
> dependencies. If so, is there a bug that has a workaround or something
> else I
> need to check?
> Thanks,
> Matt
|||Thanks,
You both answered my question. It looks like the main problem are self
referencing tables in our DB. Not for replication will fix the problem. Do
you think I should do a constraint check after the replication process is
finished? It makes me nervous that data is getting put in the tables without
the checks in place.
"Hilary Cotter" wrote:

> Merge replication has a rich set of features regarding dependencies, but
> these are typically for sensing join filters. As Paul has pointed out child
> records can be inserted before parent records, but normally these are
> resolved within a batch and if not the next time the agent runs the
> dependencies will be fixed as the parent will get in before the child the
> second time around.
> The generic work around is to use the not for replication switch on all
> constraints.
> --
> 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
> "Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
> news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
>
>
|||Matt,
I suppose the best option is to upgrade to SQL 2005, but if this is not an
option currently for you, then it's entirely your decision about the
constraint checking after synchronization - I never do it FWIW.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication IIS Worker Process Error

Everyday between 18:00 and 20:00 nearly 1000 PDA Subsriber anonymously
synchronise via Merge Replication and at least two time he have the error :

IIS Worker Process
Faulting application w3wp.exe, version 6.0.3790.1830,
faulting module sscerp20.dll, version 2.0.7331.0,
fault address 0x000110f4.

And subscriber which synchronising meanwhile becomes suspect.

Can someone offer a suggestion as to the cause of and correction for
this
error?

Thanks,

Hakan G

Here is some details about our system:

Client Side
OS: Windows Mobile 2003 4.21.1088
DB: SQL CE 2.0
Microsoft SQL Server CE (ssce20.dll) 2.00.4415.0
Microsoft SQL Server CE Client Agent (ssceca20.dll) 2.00.4415.0
Development Tools: VB.NET 2003
Service Pack: .NET Compact Framework 1.0 SP3

Server Side
OS: Microsoft 2003 SP1
Internet Information Services (INETINFO.EXE) 6.0.3790.1830
(srv03_sp1_rtm.050324-1447)
IIS Worker Process (w3wp.exe) 6.0.3790.1830 (srv03_sp1_rtm.050324-1447)
HW:IBM XSERIES_346 Intel(R) Xeon(TM) CPU 3.60GHZ (2CPU) 5,00 GB RAM DB:
SQL CE 2.0
DB:SQL Server Standart Edition 8.00.2039(SP4)

SQL CE Server 2.0
Microsoft SQL Server CE Server Agent (sscesa20.dll) 2.00.7331.0
Microsoft SQL Server CE Replication Provider (sscerp20.dll) 2.00.7331.0

Merge Replication Properties
--
status : 1
retention : 21
sync_mode : 1
allow_push : 1
allow_pull : 1
allow_anonymous : 1
centralized_conflicts : 1
priority : 100.0
snapshot_ready : 1
publication_type : 1
enabled_for_internet : 0
dynamic_filters : 1
has_subscription : 0
snapshot_in_defaultfolder : 1
alt_snapshot_folder : NULL

Merge Agent Profile:
parameter_name value
--
-BcpBatchSize 100000
-ChangesPerHistory 100
-DestThreads 4
-DownloadGenerationsPerBatch 500
-DownloadReadChangesPerBatch 500
-DownloadWriteChangesPerBatch 500
-FastRowCount 1
-HistoryVerboseLevel 1
-KeepAliveMessageInterval 300
-LoginTimeout 15
-MaxDownloadChanges 0
-MaxUploadChanges 0
-MetadataRetentionCleanup 1
-NumDeadlockRetries 5
-PollingInterval 60
-QueryTimeout 300
-SrcThreads 3
-StartQueueTimeout 300
-UploadGenerationsPerBatch 100
-UploadReadChangesPerBatch 100
-UploadWriteChangesPerBatch 100
-Validate 0
-ValidateInterval 60

You have a high-volumne replication scenario going on there and while I have not seen this specific error, I know you can increase the default thread pool used on the IIS server to broker all of this replication. There is a way to get -SrcThreads 3 to a larger number and I just saw it in the Books OnLine recently - I'll have a look and see if I can give you more details on this.

In the meantime perhaps Laxmi knows how to tune this setting off the top of his head.

-Darren

|||

For configuring SQL Mobile Server Agent on IIS: Here are typical knobs,

1) MAX_THREADS_PER_POOL - No.of threads to handle the incoming sync requests

2) CLEANUP_INTERVAL - Sync sessions cleanup

3) USAGE - Restricting the support only to RDA/Merge/Both

Darren, Sorry I did not get what is meant by SrcThreads.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Merge Replication Errors 18456 and 20028

Hello,
I am currently in the process of setting up merge replication and am
encountering some errors that I can not resolve. I hope that I am able to
provide enough information, however please ask for more detail if necessary.
While using the Replication wizard, my first error encountered was the 18456
- the original error message was sa login failed...while messing with the
remote login mapping the error changed to distributor_admin login failed. I
resolved this by using the sp_changedistributor_password and this then
allowed me to run through the wizard to set up the distributor. At this
point, I did not set up the publisher or publications or subscribers.
Ok, so now I can go into the confiuration screen and set up the publisher to
use the non-trusted SQL Server authentication using distributor_admin (also
tried sa). When I specify a merge publication and click ok (only trying one
table at this point) I get the 20028 error saying that the distributor is not
configured correctly.
In reading through multiple forums and asking questions, I'm told to use the
sp_dropserver dropping the logins in the process of course and then the
sp_addserver with the local param.
This has not resolved the issue...There are 3 servers right now in the
sysservers table. S13 is the local server name id=0, DALSQL is the
distributor with id=2 and repl_distributor with id=3.
Any suggestions or thoughts would be greatly appreciated!
Thanks in advance,
Brian
here's the output of the @.@.servername and the server name property along with
the records in the sysservers table...
SELECT @.@.SERVERNAME
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
SELECT * FROM SYSSERVERS
S13
S13
0 1089 S13 SQL Server SQLOLEDB S13 NULL NULL 2005-11-08 08:03:09.880 NULL
NULL NULL NULL 0 0 S13 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB S13 NULL NULL 2005-11-08
07:04:25.793 NULL NULL NULL NULL 0 0 S13 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1089 ORDSQL01 SQL Server SQLOLEDB ORDSQL01 NULL NULL 2005-11-08
05:26:16.060 NULL NULL NULL NULL 0 0 ORDSQL01 0 1 0 0 0 0 1 0 0 0 1 0 NULL
3 1089 DALSQL01.SPAREBACKUP.COM SQL Server SQLOLEDB DALSQL01.SPAREBACKUP.COM
NULL NULL 2005-11-08 05:41:31.450 NULL NULL NULL NULL 0 0
DALSQL01.SPAREBACKUP.COM 0 1 0 0 0 0 1 0 0 0 1 0 NULL
"brianswestra" wrote:

> Hello,
> I am currently in the process of setting up merge replication and am
> encountering some errors that I can not resolve. I hope that I am able to
> provide enough information, however please ask for more detail if necessary.
> While using the Replication wizard, my first error encountered was the 18456
> - the original error message was sa login failed...while messing with the
> remote login mapping the error changed to distributor_admin login failed. I
> resolved this by using the sp_changedistributor_password and this then
> allowed me to run through the wizard to set up the distributor. At this
> point, I did not set up the publisher or publications or subscribers.
> Ok, so now I can go into the confiuration screen and set up the publisher to
> use the non-trusted SQL Server authentication using distributor_admin (also
> tried sa). When I specify a merge publication and click ok (only trying one
> table at this point) I get the 20028 error saying that the distributor is not
> configured correctly.
> In reading through multiple forums and asking questions, I'm told to use the
> sp_dropserver dropping the logins in the process of course and then the
> sp_addserver with the local param.
> This has not resolved the issue...There are 3 servers right now in the
> sysservers table. S13 is the local server name id=0, DALSQL is the
> distributor with id=2 and repl_distributor with id=3.
> Any suggestions or thoughts would be greatly appreciated!
> Thanks in advance,
> Brian
|||Here's the output I receive after disabling publishing and then enabling it.
From there I tried to configure publishing along with the publications and
subscriber. I am so lost and frustrated that I think if I can't resolve this
today then tomorrow morning I'm going to start an incident report with
Microsoft through my MSDN subscription option.
Here are the errors...
SQL Server Enterprise Manager could not enable 'ORDSQL01.SPAREBACKUP.COM as
a subscriber.
Error 14071: Could not find the Distributor or the distribution database for
the local server. The Distributor may not be installed, or the local server
may not be configured as a Publisher at the Distributor.
SQL Server Enterprise Manager could not enable database 'Affiliates' for
merge replication.
Error 20028: The Distributor has not been installed correctly. Could not
enable database for publishing. The Replication option of 'merge publish' of
database 'Affiliates' has been set to false.
**this error occurs for all databases in SEM**
SQL Server Enterprise Manager successfully enabled
'DALSQL01.SPAREBACKUP.COM' as the Distributor for 'DALSQL01.SPAREBACKUP.COM'.
S13
S13
01089S13SQL ServerSQLOLEDBS13NULLNULL2005-11-08
08:03:09.880NULLNULLNULLNULL00S13
010000100010NULL
11609repl_distributorSQL ServerSQLOLEDBS13NULLNULL2005-11-09
04:59:47.530NULLNULLNULLNULL00S13
010010100110NULL
21089ORDSQL01SQL ServerSQLOLEDBORDSQL01NULLNULL2005-11-08
05:26:16.060NULLNULLNULLNULL00ORDSQL01
010000100010NULL
31089DALSQL01.SPAREBACKUP.COMSQL
ServerSQLOLEDBDALSQL01.SPAREBACKUP.COMNULLNULL2005-11-08
05:41:31.450NULLNULLNULLNULL00DALSQL01.SPAREBACKUP.COM
010000100010NULL
"brianswestra" wrote:

> Hello,
> I am currently in the process of setting up merge replication and am
> encountering some errors that I can not resolve. I hope that I am able to
> provide enough information, however please ask for more detail if necessary.
> While using the Replication wizard, my first error encountered was the 18456
> - the original error message was sa login failed...while messing with the
> remote login mapping the error changed to distributor_admin login failed. I
> resolved this by using the sp_changedistributor_password and this then
> allowed me to run through the wizard to set up the distributor. At this
> point, I did not set up the publisher or publications or subscribers.
> Ok, so now I can go into the confiuration screen and set up the publisher to
> use the non-trusted SQL Server authentication using distributor_admin (also
> tried sa). When I specify a merge publication and click ok (only trying one
> table at this point) I get the 20028 error saying that the distributor is not
> configured correctly.
> In reading through multiple forums and asking questions, I'm told to use the
> sp_dropserver dropping the logins in the process of course and then the
> sp_addserver with the local param.
> This has not resolved the issue...There are 3 servers right now in the
> sysservers table. S13 is the local server name id=0, DALSQL is the
> distributor with id=2 and repl_distributor with id=3.
> Any suggestions or thoughts would be greatly appreciated!
> Thanks in advance,
> Brian

Merge Replication Error

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR Pub-TRPSQL2-4 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I am getting the above error in Merge Replication. any one having any idea pls let me know.

Thanks & Regards,

Kasi.

Can you edit the merge agent job step by adding a couple of parameters?

-OutputVerboseLevel 2 -Output your-file-name

Then run the merge agent job again and look the error details in the output file.

Merge Replication Error

Hi,
I'm getting this error on trying to setup a push Merge subscription.
The Merge Process could not initialize the subscription
{call sp_MSmergesubscribedb *;true') }
Invalid column name 'maxversion_at_cleanup'
Invalid column name 'published_in_tran_pub'
The system tables for the merge replication could not be created
successfully.
The push Merge subscription does work with two other SQL Servers. When
reviewing the table schemas, both columns are located in the
sysmergearticles table on the publisher yet neither exists on the
subscriber. Also, the creation date for the system table (sysmergearticles)
on the subscriber is over a year old. I think these are older merge system
tables that were used previously.
Anyone know how to correct this issue or is there a "safe" way to delete the
sytem tables for merge replication and push the subscription out again.
Also, which merge system tables should I delete.
I did add the two new columns to the table on the subscriber but then got
the error message that the publication was invalid. After removing the
columns things went back to the orriginal error message.
Any help is greatly appreciated!
Thanks
Jerry
Jerry,
please check that you have the latest service pack on each computer on your
replication setup.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication Error

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR Pub-TRPSQL2-4 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I am getting the above error in Merge Replication. any one having any idea pls let me know.

Thanks & Regards,

Kasi.

Can you edit the merge agent job step by adding a couple of parameters?

-OutputVerboseLevel 2 -Output your-file-name

Then run the merge agent job again and look the error details in the output file.

Merge Replication and MSDE

I suppose it depends on what you want to do as a result
of the synchronization finishing. The easiest way to make
the process event-driven is to add an additional step to
the merge agent's job and modify the other steps to
always culminate in the execution of your new step. This
additional step could execute a SP, or run a command,
whatever you have in mind.
The alternatives are is to query the history tables (eg
MSdistribution_history), or
tempdb.dbo.MSreplication_agent_status or run the
penultimate script on
http://www.replicationanswers.com/OtherSQL.htm. These 3
solutions each are essentially polling methods and you
might end up doing a lot of unnecessary server work that
way.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>I hava a scenario where I have an SQL Server set up as
the publisher (merge)
>and the subscribers are Laptops with MSDE which work
mostly offline.
>The subscribers are updated once they are online with
the publisher.
>My question is: How can the subscriber tell if the MSDN
database has
>finished the replication with the publisher?
>You see some times the laptops are online just a few
minutes and the
>connection is slow. I need to make sure the subscribers
are online just long
>enough to complete the merge !
>I appreciate any help I get can on this issue :-)
>Regards
>Peter
>.
>
You see I want to make sure the the Subscribers are online long enough to
finish the merge replication.
Is it possible to Start the merge replication using a Stored Procedure
etc... ? That way the users can initiate the merge replication themselves.
By the way, MS Access 2003 is used as a front end to the MSDE database, so
inisiating a merge replication would preferably be started from Access (and
hopefully the user would get some indication that the merge replication was
completed)
ragards
Peter
"Paul Ibison" wrote:

> I suppose it depends on what you want to do as a result
> of the synchronization finishing. The easiest way to make
> the process event-driven is to add an additional step to
> the merge agent's job and modify the other steps to
> always culminate in the execution of your new step. This
> additional step could execute a SP, or run a command,
> whatever you have in mind.
> The alternatives are is to query the history tables (eg
> MSdistribution_history), or
> tempdb.dbo.MSreplication_agent_status or run the
> penultimate script on
> http://www.replicationanswers.com/OtherSQL.htm. These 3
> solutions each are essentially polling methods and you
> might end up doing a lot of unnecessary server work that
> way.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
> the publisher (merge)
> mostly offline.
> the publisher.
> database has
> minutes and the
> are online just long
>
|||To kick off the merge agent using stored procedures, you
can use sp_start_job. There are other programattic
options, usually involving ActiveX controls or command-
line execution, but the above system stored procedure
should do.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Can you give me an example on how a sp_start_job would typically look like to
start the merge replication.
Another question: There are several subscribers. Is it one merge agent for
each subscriber ?
regards
Peter
"Paul Ibison" wrote:

> To kick off the merge agent using stored procedures, you
> can use sp_start_job. There are other programattic
> options, usually involving ActiveX controls or command-
> line execution, but the above system stored procedure
> should do.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||exec msdb..sp_start_job 'DH1791628-xxxPublisher-
xxxPublisher-DH1791628-3'
One merge agent per subscriber - yes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I use SQLDMO to start the Merge Agent in my VB application
I can then check the status of the Job to determine when it has completed.
-Mike
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:55FC83AF-6BE4-4439-9CC4-B73DB0506385@.microsoft.com...
> You see I want to make sure the the Subscribers are online long enough to
> finish the merge replication.
> Is it possible to Start the merge replication using a Stored Procedure
> etc... ? That way the users can initiate the merge replication
themselves.
> By the way, MS Access 2003 is used as a front end to the MSDE database, so
> inisiating a merge replication would preferably be started from Access
(and
> hopefully the user would get some indication that the merge replication
was[vbcol=seagreen]
> completed)
> ragards
> Peter
> "Paul Ibison" wrote:

Merge Replication Agent Process is blocikgn other users

Hi,
I have Merge replication on SQL 2000 since last 6 months.
Recently I have notice that process is blocing other users and than timeouts on its own. Query Timeout is 600.
This started recently and I'm trying to track this down. Any clue or suggestion to this problem I have.
Thanks
Nilay
Nilay,
to find out more information about the cause of your blocking issues you
might like to use the MS scripts:
http://support.microsoft.com/default...;en-us;271509. If it is
simply a matter of having a lot of data modifications being synchronized
then you need to optimize the merge agent. You could:
increase -DownloadGenerationsPerBatch
make sure -MetadataRetentionCleanup is 1 and even run
sp_mergemetadataretentioncleanup manually
run the merge agent more frequently, so as to not accumulate changes
decrease the -PollingInterval if you are running continuously
increase -QueryTimeOut
Alternatively, if you accept a high latency, you might run the merge agent
out of hours.
HTH,
Paul Ibison
|||Thanks This helps

Merge Replication after data moved to a new server.

We need to move a database to a new server that is the main replication server.
The process was to build a new clean server and apply all patches and updates.
Replicate with all servers, then backup the database. Restore this database
to the new server. create a new subscription. All seemed to work except now
only new records created are replicating and any updates to existing records
are not updating. Any Ideas ?.
Conrad,
I'm a little unclear as to what you actually did. Did you have an existing
publication and then move this to a new server? Did the server have the same
name? Did you restore the msdb and distribution databases also? What type of
replication was this?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Envoriment is the following.
Server A publication database
Server B Remote Server
Server C Remote Server
Server A using push subscription to server B & C.
Server D new server.
Process:
Allow agents to sync with Server B & C so all databases are in sync.
Backup Server A Publication Database.
Disable Server A subscriptions. (disable server subscription agents)
Restore Server A Publication Database to Server D ( Different Name )
Create new publication for Server D
Create new push subscriptions to Server B & C
All subscriptions are merge subscriptions only.
When data is changed on server B & C some data was merged ( as new data was
modified or added ) and some create replication errors where the agents
believe that the data was changed outside of replication. ( I beleive the
metadata on Server B & C did not believe the system was in sync and was
trying to update the new publication server while it believes the data was
changed on server A publication and not applied yet )
When I restored the publication database to Server D ( Differenet Server
Name) the metadata is cleared by design ( This was done to make sure metadata
was clean. I assumed because the data was in sync that the remote server B &
C also believed the data is in sync ).
Is there a way to clear all metadata of the remote servers replication
history ?
I was hoping that this process would re-create the initial install of the
databases which worked great for a long time.
"Paul Ibison" wrote:

> Conrad,
> I'm a little unclear as to what you actually did. Did you have an existing
> publication and then move this to a new server? Did the server have the same
> name? Did you restore the msdb and distribution databases also? What type of
> replication was this?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Conrad,
probably needa little more info about the topology - eg why is Server D set
up as another publisher?
Does it publish the same articles or different ones?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Server D is a new faster server replacing Server A
Server A will then become the test server for future application testing in
a replicated env. This is why Server D is using a different Name. Server A
will remain as is but witha role in the test env.
So the production setup is
Server D Publication Server using push subcription to Server B & Server C
Where the data changes are on Server B & C. There are no data changes on
Server D. and backup of the data is on Server D ( Central data center )
"Paul Ibison" wrote:

> Conrad,
> probably needa little more info about the topology - eg why is Server D set
> up as another publisher?
> Does it publish the same articles or different ones?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Conrad,
whenm doing this, I'd synchronize the A publisher with B and C. Restore A on
D then remove the replication setup (after scripting it all out). Then I'd
set up D as the publisher and do a nosync nitialization to B and C. AFAIK,
if you try to move replication in situ to differently named servers this is
not supported as the servername is hardcoded into too many meta data tables.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The step of
do a nosync intialization to B and C. not sure what you mean by this step.?
I setup a subscription where the the data and schema are not used is this
what you mean.
"Paul Ibison" wrote:

> Conrad,
> whenm doing this, I'd synchronize the A publisher with B and C. Restore A on
> D then remove the replication setup (after scripting it all out). Then I'd
> set up D as the publisher and do a nosync nitialization to B and C. AFAIK,
> if you try to move replication in situ to differently named servers this is
> not supported as the servername is hardcoded into too many meta data tables.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I think we're talking about the same thing. It's the option in the wizard
where you select to Not initialize the data because the subscriber already
has it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)