Merge Replication with Indexed Tables

I have several systems located aroung the globe that currently have Merge
Replication running just fine. I've recently found that if I add an index to
one of the tables, my application will perform much better.
How does adding indices on tables effect the Merge Replication? Is this
considered a Schema change?
If I Index the NY DB, how will it impact the databases that do not have the
indices?
Thaks,
Randy
randy.crockett@.ectinfo.com
In general it will improve them, especially if the filters are on columns
which are part of the join condition. Too many indexes will degrade overall
insert, update and delete performance, so use indexes sparingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> I have several systems located aroung the globe that currently have Merge
> Replication running just fine. I've recently found that if I add an index
to
> one of the tables, my application will perform much better.
> How does adding indices on tables effect the Merge Replication? Is this
> considered a Schema change?
> If I Index the NY DB, how will it impact the databases that do not have
the
> indices?
> Thaks,
> Randy
> randy.crockett@.ectinfo.com
|||If I add Indexes to a replicated database, do I have to create a new snapshot
and restart the replication?
Is this considered a schema change?
"Hilary Cotter" wrote:

> In general it will improve them, especially if the filters are on columns
> which are part of the join condition. Too many indexes will degrade overall
> insert, update and delete performance, so use indexes sparingly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randy" <Randy@.discussions.microsoft.com> wrote in message
> news:BFD6A891-F662-4405-8749-A38ADBC3C0B3@.microsoft.com...
> to
> the
>
>

Merge Replication with filters throwing error

I'm synchronizing SQL Mobile 2005 with SQL Server 2005 using Merge
Replication. I've filtered a table like this:
WHERE HOST_NAME() = convert(nvarchar(100), patientId)
"patientId" is uniqueidentifier type column, the primary key of
"Patient" table.
The error when synchronizing is the following:
"The row operation cannot be reapplied due to an integrity violation.
Check the Publication filter. [ Table = Appointment,Operation =
Insert/Update,RowGuid = {F64E1023-01AD-DA11-8ED9-0004E2B45615} ]}"
Table Patient is joined with table Appointment but I don't understand
the error.
I think this sentence worked in SQL Server 2000 and SQL Server E 2.0.
I would appreciate any help on this.
Regards.
I've also tried same filter for Appointment database, and here I don't
receive any error, but the filtering is not correctly done.
Can anybody tell me if the following filtering is correct: WHERE
HOST_NAME() = convert(nvarchar(100), patientId)
Be sure this filtering has correctly worked in the past.
Any ideas please?
Regards.

merge replication snapshot agent fails

I'm trying to setup merge replication and get the following error from
the snapshot agent after I've finished the 'New Publication Wizard'.
This doesn't make sense to me as it is an automatic process. Also, it
looks like it is complaining about a 'dbo.MSmerge_cont...' view which
is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
Command Text: IF
OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
VIEW
[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
e, SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
command, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
commandSetupDelegate, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
strQuery, SqlConnection connection)
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at
Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
103)
Get help: http://help/103
can you enable logging and post the log back here?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B312292&sd=tech
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
"gabe" <gschnaub@.gmail.com> wrote in message
news:1163095482.849374.43790@.m73g2000cwd.googlegro ups.com...
> I'm trying to setup merge replication and get the following error from
> the snapshot agent after I've finished the 'New Publication Wizard'.
> This doesn't make sense to me as it is an automatic process. Also, it
> looks like it is complaining about a 'dbo.MSmerge_cont...' view which
> is a view created automatically for the merge publication.
> ERROR MESSAGE:
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> FULL DETAILS:
> Message: The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> Command Text: IF
> OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
> VIEW
> [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
> Parameters:
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e, SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
> command, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
> commandSetupDelegate, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
> strQuery, SqlConnection connection)
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at
> Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
> MSSQLServer, Error number: 103)
> Get help: http://help/103
> Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
> 103)
> Get help: http://help/103
>

Merge replication performance dropping. Opinions?

I've been tasked with improving our company's merge replication
performance. We are using SQL merge replication and lately we are seeing
a decrease in the performance. Specifically, we are seeing more errors,
retries, latency, and even missing data. I hope someone can provide
insight or share experiences on replicating at this scale.
Our configuration is such:
- One master server replicates to three distribution servers. These
servers are on the same LAN. The replication jobs are scheduled to run
every 10 minutes.
- Each of the three distribution servers replicates to ~15 servers (total
of 45 subscribers). These subscribers are on connections ranging from
64k to 256k. The replication jobs are scheduled to run once an hour
(staggered times). The subscription is a push subscription running at
the distributor. The subscriptions are filtered per location.
- The publication has 200+ articles, although data changes frequently in
only about 20 tables.
- The data being replicated is primarily either inserts at the
subscribers, or common data inserted/updated at the master. We have 18
tables >1M rows and 3 tables >10M rows. Because of row filtering, not
all these rows get replicated to each subscriber.
The errors:
- No errors between master and distributors, but of course they are on
the LAN.
- "The process could not check the existence of generation at the
Subscriber"
- "The process could not connect to Subscriber xxx"
- "The process could not enumerate changes at the Subscriber"
- "The merge process could not apply the replication metadata" (This
error was returned on a "Completed" task)
- "The process could not query row data at the Subscriber"
Other notes/questions:
- I've tried changing all the subscriber profiles to Slow Link, with not
much improvement.
- The replication taks are launched with QueryTimeout=30000. I believe
this to be excessive, but can't be sure.
- Task durations have run as long as 8 hours.
- Since most of the errors are accompanied by "General network error", I
can accept that our WAN sucks. How can I mitigate this risk?
- Querying the MSMerge tables, I am seeing what I think to be outrageous
row counts: 10M rows in MSMerge_contents at the master, 2M rows in
MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
distributor. Does this indicate anything seriously wrong?
- Database Indexes are defragged once a week.
- I have just modified some triggers to reduce the amount of data being
replicated; a few days will show if it was helpful.
I appreciate any feedback, comments, or suggestions. Thank you!
If your subscribers connect frequently you should try to drop the retention
period to something small, like 4 days.
Also you might want to evaluate how you have created your publication to see
if there are any optimizations you can make. For example do you need
filters? Is Keep partition changes is true?
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
"Anachostic" <anachostic@.remove.700cb.net> wrote in message
news:Xns9950690A3F584anachostic@.66.250.146.128...
> I've been tasked with improving our company's merge replication
> performance. We are using SQL merge replication and lately we are seeing
> a decrease in the performance. Specifically, we are seeing more errors,
> retries, latency, and even missing data. I hope someone can provide
> insight or share experiences on replicating at this scale.
> Our configuration is such:
> - One master server replicates to three distribution servers. These
> servers are on the same LAN. The replication jobs are scheduled to run
> every 10 minutes.
> - Each of the three distribution servers replicates to ~15 servers (total
> of 45 subscribers). These subscribers are on connections ranging from
> 64k to 256k. The replication jobs are scheduled to run once an hour
> (staggered times). The subscription is a push subscription running at
> the distributor. The subscriptions are filtered per location.
> - The publication has 200+ articles, although data changes frequently in
> only about 20 tables.
> - The data being replicated is primarily either inserts at the
> subscribers, or common data inserted/updated at the master. We have 18
> tables >1M rows and 3 tables >10M rows. Because of row filtering, not
> all these rows get replicated to each subscriber.
> The errors:
> - No errors between master and distributors, but of course they are on
> the LAN.
> - "The process could not check the existence of generation at the
> Subscriber"
> - "The process could not connect to Subscriber xxx"
> - "The process could not enumerate changes at the Subscriber"
> - "The merge process could not apply the replication metadata" (This
> error was returned on a "Completed" task)
> - "The process could not query row data at the Subscriber"
> Other notes/questions:
> - I've tried changing all the subscriber profiles to Slow Link, with not
> much improvement.
> - The replication taks are launched with QueryTimeout=30000. I believe
> this to be excessive, but can't be sure.
> - Task durations have run as long as 8 hours.
> - Since most of the errors are accompanied by "General network error", I
> can accept that our WAN sucks. How can I mitigate this risk?
> - Querying the MSMerge tables, I am seeing what I think to be outrageous
> row counts: 10M rows in MSMerge_contents at the master, 2M rows in
> MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
> distributor. Does this indicate anything seriously wrong?
> - Database Indexes are defragged once a week.
> - I have just modified some triggers to reduce the amount of data being
> replicated; a few days will show if it was helpful.
> I appreciate any feedback, comments, or suggestions. Thank you!
>
|||Thanks for your response.
Regarding retention, is this the "Subscriptions expire and may be
dropped..." in the Publication>Properties>General tab? If so, ours is set
to 60 days. I figure reducing the retention period will reduce the size of
the MSMerge_content tables? And would this change require dropping and
recreating all the subscriptions? That's not an option at the current
time.
I could not find where the "keep partition changes" setting is. The only
reference in BO is with SQL-DMO.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#QmoFD4rHHA.1296@.TK2MSFTNGP06.phx.gbl:

> If your subscribers connect frequently you should try to drop the
> retention period to something small, like 4 days.
> Also you might want to evaluate how you have created your publication
> to see if there are any optimizations you can make. For example do you
> need filters? Is Keep partition changes is true?

merge replication over internet

Hello!
I'm doing replication over non-trusted domains. I'm using FTP server to do
that.
I've also read Paul's articel about replication over non - trusted domains.
I'm using snapshot and merge replication.
Snapshot replication works OK, without any problems, but MERGE replication
gives me this error:
The schema script '' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
The process could not connect to FTP site 'PC' using port 21.
(Source: STREZNIK\SAOP (Agent); Error number: 0)
------
Can you help me,
Uros
Uros,
this type of replication normally falls down due to
configuration/connectivity issues that have to be looked at individually.
Please try using an FTP tool to test transferring the snapshot files.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul!
Does MERGE replication need any different settings than snapshot. Because
snapshot over FTP works without any problems.
What are the most common settings that make troubles for MERGE via FTP?
Thanks.
"Paul Ibison" wrote:

> Uros,
> this type of replication normally falls down due to
> configuration/connectivity issues that have to be looked at individually.
> Please try using an FTP tool to test transferring the snapshot files.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Sorry to ask the obvious, but are your FTP settings done correctly on the
merge publication (inc folder paths).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Well, I've made it as for SNAPSHOT.
Here are my settings:
I have path for ftp \SAOPReplication\ftp
Just the same as for SNAPSHOT.
Should tha path be different?
"Paul Ibison" wrote:

> Sorry to ask the obvious, but are your FTP settings done correctly on the
> merge publication (inc folder paths).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Uros,
please can you post up the results of sp_helpmergepublication and
sp_helppublication for the merge and snapshot publications.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul!
Here are the results of those store procedures.
I hope you can sort this out on notepad. I've tried and it worked.
id name
description
status retention sync_mode allow_push allow_pull allow_anonymous
centralized_conflicts priority snapshot_ready
publication_type pubid snapshot_jobid
enabled_for_internet dynamic_filters has_subscription
snapshot_in_defaultfolder alt_snapshot_folder
pre_snapshot_script
post_snapshot_script
compress_snapshot ftp_address
ftp_port ftp_subdirectory
ftp_login
conflict_retention keep_partition_changes
allow_subscription_copy allow_synctoalternate validate_subscriber_info
backward_comp_level publish_to_activedirectory max_concurrent_merge
max_concurrent_dynamic_snapshots
------
-----------
-- -- -- -- -- --
-- -- --
-- --
-- -- --
-- --
-----------
-----------
-----------
------
-----------
------
-- -- --
-----------
-
-- -- --
1 MERGE_STRANKE_SAOPsf
Merge
publication of SAOPsf database from Publisher PC.
1 14 0 1 1 1
1 100.0 1 0
817F7880-1B66-4C7E-8D8D-1AD3270328B7
0x679F4FFFFA5CAE418DC4919151EBD05A 1 0 0
0 \\PC\D$\SAOP\Replikacije
NULL
NULL
1 PC
21 \SAOPreplikacije\ftp
SQLRemote
14 0 0
0 NULL
40 0
0 0
pubid name
restricted
status task replication frequency synchronization method description
immediate_sync enabled_for_internet allow_push allow_pull
allow_anonymous independent_agent immediate_sync_ready allow_sync_tran
autogen_sync_procs snapshot_jobid retention has
subscription allow_queued_tran snapshot_in_defaultfolder alt_snapshot_folder
pre_snapshot_script
post_snapshot_script
compress_snapshot ftp_address
ftp_port ftp_subdirectory
ftp_login
allow_dts
allow_subscription_copy centralized_conflicts conflict_retention
conflict_policy queue_type backward_comp_level publish_to_AD
------
-- -- -- -- --
-----------
-- -- -- -- --
-- -- -- --
-- -- --
-- --
-----------
-----------
-----------
------
-----------
------
-- -- -- --
-- -- -- --
2 SNAPSHOT_ARTIKLI_SAOPsf
0 1
1 1 0 Snapshot
publication of SAOPsf database from Publisher PC.
1 1 1 1 1
1 1 0 0
0x30961C9177CEEF4AA8EDF9FE69CC7E01 336 1 0
0 \\PC\D$\SAOP\Replikacije
NULL
NULL
1 PC
21 \SAOPReplikacije\ftp
SqlService
0 0 NULL
14 NULL NULL 10 0
|||Can the subscriber resolve the host name PC to the FTP Server? It probably
needs to be a Fully Qualified Domain Name like PC.MyDomain.Com, or you need
a hosts file entry mapping PC to the IP address of the FTP server.
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
"uros" <uros@.discussions.microsoft.com> wrote in message
news:3D947F9B-2DA3-4C75-9860-1401E33EEB3C@.microsoft.com...
> Hello!
> I'm doing replication over non-trusted domains. I'm using FTP server to do
> that.
> I've also read Paul's articel about replication over non - trusted
> domains.
> I'm using snapshot and merge replication.
> Snapshot replication works OK, without any problems, but MERGE replication
> gives me this error:
> The schema script '' could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> The process could not connect to FTP site 'PC' using port 21.
> (Source: STREZNIK\SAOP (Agent); Error number: 0)
> ------
> Can you help me,
> Uros
|||Hilary!
I've done that. I've updated the host file. And as I said, SNAPSHOT
replication works fine via FTP, but merge replication fails with that error.
I don't know, how to continue and I have a deadline.
U.
"Hilary Cotter" wrote:

> Can the subscriber resolve the host name PC to the FTP Server? It probably
> needs to be a Fully Qualified Domain Name like PC.MyDomain.Com, or you need
> a hosts file entry mapping PC to the IP address of the FTP server.
> --
> 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
> "uros" <uros@.discussions.microsoft.com> wrote in message
> news:3D947F9B-2DA3-4C75-9860-1401E33EEB3C@.microsoft.com...
>
>
|||Uros,
sorry - even in notepad this is pretty difficult. Please can you set the
query analyser to output the results to text and use a Pipe delimiter, then
repost the results.
Cheers,
Paul Ibison
"uros" <uros@.discussions.microsoft.com> wrote in message
news:3D470926-AEBC-48BB-9875-F2FA879D2CA0@.microsoft.com...
> Paul!
> Here are the results of those store procedures.
> I hope you can sort this out on notepad. I've tried and it worked.
> id name
> description
>
> status retention sync_mode allow_push allow_pull
> allow_anonymous
> centralized_conflicts priority snapshot_ready
> publication_type pubid snapshot_jobid
> enabled_for_internet dynamic_filters has_subscription
> snapshot_in_defaultfolder alt_snapshot_folder
>
> pre_snapshot_script
>
> post_snapshot_script
>
> compress_snapshot ftp_address
> ftp_port ftp_subdirectory
>
> ftp_login
> conflict_retention keep_partition_changes
> allow_subscription_copy allow_synctoalternate validate_subscriber_info
>
> backward_comp_level publish_to_activedirectory max_concurrent_merge
> max_concurrent_dynamic_snapshots
> --
> ------
> ----------
--
> -- -- -- -- -- --
> -- -- --
> -- --
> -- -- --
> -- --
> ----------
--
> ----------
--
> ----------
--
> --
> ------
> --
> ----------
--
> ------
> -- -- --
> --
> ----------
--
> -- -- --
> --
> 1 MERGE_STRANKE_SAOPsf
> Merge
> publication of SAOPsf database from Publisher PC.
>
> 1 14 0 1 1 1
> 1 100.0 1 0
> 817F7880-1B66-4C7E-8D8D-1AD3270328B7
> 0x679F4FFFFA5CAE418DC4919151EBD05A 1 0 0
> 0 \\PC\D$\SAOP\Replikacije
>
> NULL
>
> NULL
>
> 1 PC
> 21 \SAOPreplikacije\ftp
>
> SQLRemote
> 14 0 0
> 0 NULL
>
> 40
> 0
> 0 0
> pubid name
> restricted
> status task replication frequency synchronization method
> description
>
> immediate_sync enabled_for_internet allow_push allow_pull
> allow_anonymous independent_agent immediate_sync_ready allow_sync_tran
> autogen_sync_procs snapshot_jobid retention has
> subscription allow_queued_tran snapshot_in_defaultfolder
> alt_snapshot_folder
>
> pre_snapshot_script
>
> post_snapshot_script
>
> compress_snapshot ftp_address
> ftp_port ftp_subdirectory
>
> ftp_login
> allow_dts
> allow_subscription_copy centralized_conflicts conflict_retention
> conflict_policy queue_type backward_comp_level publish_to_AD
> --
> ------
> -- -- -- -- --
> ----------
--
> -- -- -- -- --
> -- -- -- --
> -- -- --
> -- --
> ----------
--
> ----------
--
> ----------
--
> --
> ------
> --
> ----------
--
> ------
> -- -- -- --
> -- -- -- --
> 2 SNAPSHOT_ARTIKLI_SAOPsf
> 0
> 1
> 1 1 0 Snapshot
> publication of SAOPsf database from Publisher PC.
>
> 1 1 1 1 1
> 1 1 0 0
> 0x30961C9177CEEF4AA8EDF9FE69CC7E01 336 1 0
> 0 \\PC\D$\SAOP\Replikacije
>
> NULL
>
> NULL
>
> 1 PC
> 21 \SAOPReplikacije\ftp
>
> SqlService
> 0 0 NULL
> 14 NULL NULL 10 0
>

Merge replication odd question

Hello,
I need to configure a merge replication between 3 machines, one of these is never connected with the other two machines. I've tried to work with the off-line machine with backup and restore to the offline machine and the others in this way: I backup up a
subscripted DB and restored to the offline machine, update some data and then backup and restore again on the subscripted DB. It breaks the synchronization.
Do you think there is a way to "freeze" a subscribed DB, move it to another off-line machine (moving backups or data and log files, or something else), work on this machine and then restore the updated DB to the freezed one?
And do you think it will work also between a SQL server 2000 publisher and an MSDE subscriber?
Thank you for your help!
Michele
This certainly is an odd request.
I think it might work if you use the keep_replication switch of the restore command while you are restoring your database.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||But Hilary, if we RESTORE over the top of one of the two connected databases
the database that was disconnected then we will lose at the very least all
transactions that happened between the two connected databases won't we?
It is almost as though we want to RESTORE to a 3rd DB with KEEP_REPLICATION
and then fire up the Agents.
Not even sure if that will do it.
Allan
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:F1BFFB4C-C330-4BDD-9753-A7787783F03D@.microsoft.com...
> This certainly is an odd request.
> I think it might work if you use the keep_replication switch of the
restore command while you are restoring your database.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||OK so here was my testing
Create 3 databases
M1,M2,M3
M1 = Publisher
M2,M3 = Subs
Create a table (I used a very simple table with IDENTITY())
Create an article of the table (Handle Identity ranges)
Add to a publication
Start cooking
--Now remove M3 from the topology so we can put it on the laptop for however
long we want and continue to work on it (watch identity ranges if a long
time)
--stop the merge agent and drop the database
--Restore somewhere else with KEEP_REPLICATION
Must be restored as M3 otherwise you get the following when inputting data
ERROR!!
INITCOLVS: The Parameter 'nickname' is invalid
--Input data
Backup from this box now and restore to the original (WITH KEEP_REPLICATION)
Start up the Merge agent
In it goes.
Allan
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:F1BFFB4C-C330-4BDD-9753-A7787783F03D@.microsoft.com...
> This certainly is an odd request.
> I think it might work if you use the keep_replication switch of the
restore command while you are restoring your database.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||I'll try to repro this later.
If you have a subscriber, and you backup that database, and restore it to the 3rd machine using the keep replication switch, make your changes, backup the database and restore it to the second machine, again using keep replication, this should work.
You must have replication installed on the third machine however.
This is a variation of the way you can deploy susbcribers using a backup and restore.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||It works!
Thank you very much Hilary and Allan for your precious help!
Michele
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Merge Replication not merging updates made to subscriber

Hope someone can help with this. I've implemented merge replication and when
I make an update to the server that acts as the distributor the update is
reflected on the subscribers. However when I make a change on the subscriber
I cannot see it on the other subscribers or on the distributor. Any help
regarding this would be appreciated.
Thank you,
Abdul Rauf
Abdul,
how are you making the change on the subscriber? If it is a bulk insert
without firing the triggers, it won't replicate to the publisher. In some
rare other cases this also happens (eg when the filter is set to 1=2 and you
are adding records onto the subscriber. If there is no coresponding record
in MSmerge_contents try using sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Paul, I'm new to replication so I will do more research on what you have
below. I'm testing this scenario so I'm just going into the enterprise
manager of the subscriber database and updating a row in the Pubs database.
Updating a row in the Distributor database works but not in the Subscriber
through enterprise manager.
"Paul Ibison" wrote:

> Abdul,
> how are you making the change on the subscriber? If it is a bulk insert
> without firing the triggers, it won't replicate to the publisher. In some
> rare other cases this also happens (eg when the filter is set to 1=2 and you
> are adding records onto the subscriber. If there is no coresponding record
> in MSmerge_contents try using sp_addtabletocontents to include the rows then
> resynchronise. Alternatively you can use sp_mergedummyupdate for a single
> row.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>

Merge Replication example for SQL Server 2005

Hi guys,
I'm fighting against Merge Replication in SQL Server 2005.
I've tried several times to setup merge replication with a real example
but I can't. I've followed this instructions given by this "article" in
MSDN2 but there is no way:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
I guessed it's something related to permissions because I've migrated
by scripting an old publication I had in SQL Server 2000 to 2005 and
worked without problems.
In the above example another Windows user is created and at the end of
"Create Publication" step, in the "Agent Security" dialog box, I guess
you must enter this user's credentials. All OK. Publication is finished
and then I add IUSR_xxxxxx user to the Publication Access List.
Publication tries to create the initial snapshot but it always give
this error: "Executed as user: PC-Name\snapshot_agent. The step did not
generate any output. The step failed.".
Instead of adding this user in the mentioned dialog box, if I check
"Run under SQL Server Agent Account", publication created the initial
snapshot and works well.
Can anybody tell me if that example is working and is correct? I do not
want to check "run under SQL Server Agent Account", because it says
that it's a security bad practice.
Could somebody point me in the rigth direction please?
Thanks in advance.
It would appear that the login you supplied for running the snapshot agent
job did not have sufficient permissions to start the snapshot agent. One
probable cause is that PC-Name\snapshot_agent may not have read access to
%ProgramFiles%\Microsoft SQL Server\90\COM\snapshot.exe. In any case, you can
troubleshoot this further by starting a cmd shell using "runas
/user:PC-Name\snapshot_agent" on the distributor machine and see if you can
start snapshot.exe manually.
Hope that helps
-Raymond
"Lonifasiko" wrote:

> Hi guys,
> I'm fighting against Merge Replication in SQL Server 2005.
> I've tried several times to setup merge replication with a real example
> but I can't. I've followed this instructions given by this "article" in
> MSDN2 but there is no way:
> http://msdn2.microsoft.com/en-us/library/ms171908.aspx
> I guessed it's something related to permissions because I've migrated
> by scripting an old publication I had in SQL Server 2000 to 2005 and
> worked without problems.
> In the above example another Windows user is created and at the end of
> "Create Publication" step, in the "Agent Security" dialog box, I guess
> you must enter this user's credentials. All OK. Publication is finished
> and then I add IUSR_xxxxxx user to the Publication Access List.
> Publication tries to create the initial snapshot but it always give
> this error: "Executed as user: PC-Name\snapshot_agent. The step did not
> generate any output. The step failed.".
> Instead of adding this user in the mentioned dialog box, if I check
> "Run under SQL Server Agent Account", publication created the initial
> snapshot and works well.
> Can anybody tell me if that example is working and is correct? I do not
> want to check "run under SQL Server Agent Account", because it says
> that it's a security bad practice.
> Could somebody point me in the rigth direction please?
> Thanks in advance.
>
|||Hi Raymond, thanks for your help.
I have given the snapshot_agent user, rights to have "total control"
(instead of "read" you advised me) to "COM" folder, therefore, also to
"snapshot.exe" file.
I again opened SQL Server Management Studio, tried to start Snapshot
Agent and the same error appeared.
Then I tried to start it by command line this way:
runas /user:pc-1\snapshot_agent "C:\Archivos de programa\Microsoft SQL
Server\90\COM\snapshot.exe"
Asks me for the user password and seems like it starts running a
process because opens another command windows but is closed wihout me
seeing any result. I understand that means this user is able to run
snapshot agent.
What is happenning then? Have you tried yourself the example I gave you
in the link. I would really appreciate if you could try and post here
your results. Maybe I've forgotten something or you understand
something different that I did.
What about running the replication under SQL Server Agent Account? Is
it so insecure? As I told you, when migrating my old replication via
script, I noticed it was generated corretly in SQL Server 2005. Then I
opened publication's properties and could see that was running under
"SQL Server Agent account". All publications in SQL Server 2000 run
under this account? I don't remmeber me choosing between I think these
days I'll do it this way because it is the unique way it works ;-)
Please see if you can setup replication following the example and I'll
be willing for your reply.
Thanks very much again.

Merge replication does not working as expected.

Hi,
I am writing again because I've now confirmed that merge
replication does not work in my case. I'll try to
describe my case in a detailed way, as I need to find the
solution (fix) for this.
I have two servers. One main server A, and one subserver
B (there will be more of subservers).
Server B is receiving data from various applications in a
very irregular way. The data are then supposed to be
moved to server A (moved, not copied).
Server A is Publisher and its own Distributor. Server B
is Subscriber. Subscription is "pull" and "anonymous".
The merge replication with filtering is used. The filter
clause indicates a condition impossible. Thanks to this,
following scenario occurs:
- no rows are initiallyu copied from A server to B,
because A has no rows that fulfill the impossible
condition.
- when there are some rows added on B, when
synchronization occurs, the rows are copied from B to A.
Then all rows received on A are checked against the
impossible filter - because none of it satisfies that
condition, all are deleted on B server.
This works OK when the data are inserted on B "outside"
the replication phase.
But when you will insert the data to B when the
replication is in progress, the merge replication (and
subsequent replications) will fail to clear all the rows
on B, and as a result, table on B will stil have some
records from previous replications (all records are
copied, but not all are deleted). I consider this
behaviour as a bug in sql server replication, as I think
that it should be consistent in all situations.
The best way to reproduce this is to create a stored
procedure that inserts for example 10000 records to B,
and run this procedure few seconds before the start of
replication.
I hope that I gave you some light on the subject. I hope
that there are some MS guys related to replication, and
maybe one of them will be able to help me with this (you
can write directly if you need detailed information).
I'll appreciate any help.
Best regards,
Krzysztof Kruszynski
Paul,
Thanks for the link.
When I was reproducing the issue, I found that sometimes I was unable to disturb the first replication. But the seubsequent runs gave me allways some abandoned rows.
Regards,
Krzysztof
"Paul Ibison" wrote:

> Krzysztof,
> I'll try to repro this sometime later this week. Just as
> an aside, as you want to target your newsgroup comment to
> Microsoft, you can use this interface:
> http://communities2.microsoft.com/co.../newsgroups/en
> -us/default.aspx?
> dg=microsoft.public.sqlserver.replication&cat=en-us-
> servers-sqlserver&lang=en&cr=US
> This newsgroup webpage allows you to categorise/filter
> your queries.
> Regards,
> Paul Ibison
>
|||Krzysztof,
yes I can replicate your error. Subsequently doing a dummy
update on the subscriber still didn't ultimately remove
the row from the subscriber. The only way I could resolve
it was to reinitialize, which sounds drastic, but in this
case it merely readds the empty table but resets the
incorrect generation numbers. I'm not on sp3a on this
site, but if you can reproduce it on sp3a, I'd log this
with MS as a bug. Anyway, to resolve your issue, you could
resort to DTS - after all what you are doing is
essentially bypassing normal replication procedures, or
you could reinitialize frequently.
HTH,
Paul Ibison
|||Hi Paul

> I'm not on sp3a on this site, but if you can reproduce
> it on sp3a, I'd log this with MS as a bug.
I'll try to apply the sp3a and let you know about the results. But I don't know where to log it as a bug (or you will log it?).

> Anyway, to resolve your issue, you could
> resort to DTS - after all what you are doing is
> essentially bypassing normal replication procedures
Yep - I know. and I will probably use DTS or something else, not the replication.
Thanks for your help,
Krzysztof
|||You could post it on the feedback area
(http://register.microsoft.com/mswish/suggestion.asp).
Alternatively you could repost it here FAO Microsoft.
Alternatively a MVP (Hilary?) who sees this might have
some special powers to raise it directly with MS. Probably
just leaving it as it is will be sufficient as these
newsgroups are monitored by MS staff as a matter of course.
Regards,
Paul Ibison

Merge replication conflicts on Identity Columns

Hi all,
I've set up a merge replication using sql server 2005.
All settings for "Copy Use Triggers" were set to false
All Identity ranges were set to be handled automatically.
There is a table - "Stock" - which has an ident field. An insert
trigger on this table inserts a new record into another table -
"barcodes" - which itself has its own ident field.
When a new "stock" record is inserted on the subscriber I get a merge
conflict which says:
"A row insert at 'xxx.YYYYYYYYY' could not be propagated to
'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
Explicit value must be specified for identity column in table
'barcodes' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity
column."
It suggested I use sp_adjustpublisheridentityrange, which I did on
both tables, but still the conflict won't resolve.
Any suggestions will be most gratefully received
Thanks
Ken
This should resolve itself the next time the merge agent runs. It is
possibly that you have entered so much data on the subscriber that you
identity range is completely out of whack.
Issue a dbcc checkident(''tablename') on your subscriber and note the value
for the current identity value and current column value. If the current
identity value is greater than your current column value set the current
identity value to be the current column value, i.e.
dbcc checkident(''tablename',reseed, 14)
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
<kengillett@.gmail.com> wrote in message
news:1184849805.324095.134260@.w3g2000hsg.googlegro ups.com...
> Hi all,
> I've set up a merge replication using sql server 2005.
> All settings for "Copy Use Triggers" were set to false
> All Identity ranges were set to be handled automatically.
> There is a table - "Stock" - which has an ident field. An insert
> trigger on this table inserts a new record into another table -
> "barcodes" - which itself has its own ident field.
> When a new "stock" record is inserted on the subscriber I get a merge
> conflict which says:
> "A row insert at 'xxx.YYYYYYYYY' could not be propagated to
> 'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
> Explicit value must be specified for identity column in table
> 'barcodes' either when IDENTITY_INSERT is set to ON or when a
> replication user is inserting into a NOT FOR REPLICATION identity
> column."
> It suggested I use sp_adjustpublisheridentityrange, which I did on
> both tables, but still the conflict won't resolve.
> Any suggestions will be most gratefully received
> Thanks
> Ken
>

Merge Replication Conflict Resolution Latency?

I've been having a hard time finding a definitive answer about merge
replication and any sort of latency on a transaction between a
subscriber and publisher. I apologize in advance for the lengthy post,
but it's difficult to really "cut to the meat" of this one
Here's my scenario: I'm developing a .NET CF application on a PocketPC
that stores data in a SQL CE database that merges with a SQL 2000
publisher when the PDA is docked. Generally speaking, what goes up is
properly reflected when it comes back down via a "is complete" row
filter that simply looks for a flag that is set to 1. For example:
-Publisher has 128 rows
-PDA has 128 rows, 28 rows have been "completed"
-Instantiate merge with publisher
-Result is that there are 128 rows on publisher, 28 are complete, row
filter disregards 28 complete records and sends down 100 records that
are still incomplete to PDA
This seems to work great. Now enter conflicts. I have the resolver set
such that the subscriber always wins. We've had several cases where the
server has been under moderate to heavy load, and there have been a lot
of conflicts. This was an actual occurrence:
-Publisher has 79 rows
-PDA has 79 rows, 69 rows have been "completed"
-Publisher had 79 rows *removed*
-Instantiate merge with publisher
-Result is that there are 69 conflicts at publisher, the subscriber wins
all, but there are still *79* rows on the PDA after merge finishes
-I wait a minute and try again, this time there are about 50 rows on the
subscriber
-I repeat this process several times over the course of 5 minutes until
finally, I am left with the correct number of records on the PDA (0)
My question is, is this normal behavior for the resolver? Are conflicts
generally allowed to be "handed off" and the merge allowed to complete
without reflecting their outcome? If so, is there any way to tune or
change this behavior?
This also has me concerned about a normal merge without any conflicts.
Would there ever be a case in which a merge would get out of sync in
this way? My understanding is that instantiating a merge will always
result in both the supplying subscriber and publisher ending up with the
same data at that moment. My merge agents are all using the default
profile.
Thanks a BUNCH for any help you can offer. I've had an impossible time
answering these questions and I'm a programmer before a DBA
-Mike
Mike wrote:

> -Publisher has 79 rows
> -PDA has 79 rows, 69 rows have been "completed"
> -Publisher had 79 rows *removed*
> -Instantiate merge with publisher
> -Result is that there are 69 conflicts at publisher, the subscriber wins
> all, but there are still *79* rows on the PDA after merge finishes
> -I wait a minute and try again, this time there are about 50 rows on the
> subscriber
> -I repeat this process several times over the course of 5 minutes until
> finally, I am left with the correct number of records on the PDA (0)
I apologize, I had my scenarios crossed. This should have read:
-Publisher has 79 rows
-PDA has 79 rows, 69 rows have been "completed"
-Publisher had all 79 rows *modified*
-Instantiate merge with publisher
-Result is that there are 69 conflicts at publisher, the subscriber wins
all and they are noted as resolved in the agent history, but there are
still *79* rows on the PDA after merge finishes
-I wait a minute and try again, this time there are about 50 rows on the
subscriber
-I repeat this process several times over the course of 5 minutes, each
time the number of rows at the subscriber decreases, until finally, I am
left with the correct number of records on the PDA (10)
Thanks
-Mike

Merge Replication Alther Column in Place

Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew
You first have to drop default contraint which is causing the problem
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"andrew bourne" <andrewbourne@.vardells.com> wrote in message news:eZuBH8UVFHA.3584@.TK2MSFTNGP14.phx.gbl...
Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew