Merge republish Schema changes

Hello,

I'm working on a replication topology that is completely merge. We have a single consolidated instance (SQL 2005 SP1 Standard) that holds all data and is a continuous push merge publication filtered by region to regional instances (SQL 2005 SP1 Standard). Then we have individual user instances (SQL Express SP1) that pulls from the republished regional instances which is filtered by user. Both publications have Replicate Schema Changes set to true.

I'm testing out changes to tables and sps on a test system I've been using this process:

1-Run Snapshot on the Consolidated instance

2-Verify all published articles have a status of 2 in sysmergearticles

3-Run Regional Snapshot

4-Verify all published articles have a status of 2 in sysmergearticles

5-Run alter table scripts

6-Once all three levels have the table changes, run the alter sp scripts

I've gotten to step 5 and and the changes get replicated to the regional instance just fine however only the existing column changes get replicated to the SQLExpress instance, not the new columns. Looking at the articles in the regional publication it shows the new columns, but they are not selected. I know I can manually select them (or probably write a script that adds them to the publication although sp_repladdcolumn has been depreciated), but isn't there a way to make this a completely automated process since it's just a republished database? Also is the process I'm using the correct one?

Thank you,

Aaron Lowe

Is your publication property replication_ddl set to true?|||I apologize for not being clearer in my original post. I had said that replicate schema changes was set to true, this is the replication_ddl property that I was referring to. Thanks, Aaron|||when you add a new column, the column should get replicated to all nodes in your topology. Is the new column not getting replicated at all? Where in your topology are you adding the new columns - publisher, republisher or subscriber?|||I'm adding the columns at my original publisher (the consolidated one). As I said it is pushed down to my subscribers that republish the data (the regional ones that are pushed from the consolidated one), it just doesn't get all the way down to my final subscribers (the individual sqlexpress ones that pull the data). Looking at the properties of the publication on the republisher it shows the columns in the publication but they are not selected.|||if replicate_ddl option is truly enabled at both the publisher and the republisher, then I'm not sure what the problem is. You verified the replicate_ddl column is set to 1 in sysmergepublications table in the published database at both the publisher and republisher?|||

Well, I believe it's correct, here's what is in the sysmergepublications:

Consolidated database (original publisher)

publication name, replicate_ddl

Consolidated, 1

Region, 0

Regional database (republisher)

publication name, replicate_ddl

Consolidated, 1

Region, 1

SQL Express database (subscriber)

publication name, replicate_ddl

Consolidated, 0

Region, 1

Also the status in sysmergearticles in the consolidated db is 2 (active). There are two sets of articles in the sysmergearticles table in the regional db, one for each the consolidated and regional publication. The records in sysmergearticles for the consolidated publication has a status of 1 (Unsynced) while the records for the regional publication have a status of 2 (active). The status in the SQLExpress pull subscriptions is all 1 (Unsynced).

Thanks,

Aaron

|||Can you try your scenario with SP2? We fixed somewhat similar issue in SP2.sql

Merge Replication With SQL Server Express (Getting Started)

Hi

This is my first time working with replication and I was wondering if someone could point me in the right direction. I am looking for some sample code or a Web page that describes how to do merge replication using SQL Server Express on the client machines and SQL Server 2005 on the backend. If someone could help, I would appreciate it. Thanks.

If you are familiar with replication on other SKUs (such as standard edition), setting up on Express is not that complicated. You can treat Express just as another SKU, with the following exceptions:

1) Express can only be a subscriber.

2) There is no SQL Server Agent on Express. Thus if you have pull subscription, you can use one of the following ways to initiate synchronization on Express:

a) Using Sync Manager

b) Using ActiveX

c) Calling replmerg.exe (for merge subscription) or distrib.exe (for transactional subscription) with the proper parameters directly.

Merge replication using wins mobile 5.0

Hi all,

I have developed a mobile program with sql server 2000 merge replication. It works fine in Win mobile 2003 OS, but, not working at all after I upgrade the mobile OS to Win mobile version 5.0

Does anyone have any idea at all what's going on?

Thanks a lot.

AngelaC

What makes you think it's not working, are you getting an error message?|||

Thanks Greg for your prompt reply. The pocket pc program is written in vb.net and I got the following error when I tried to run these codes:

Codes:

Dim replSQL As New SqlCeReplication

...

replSQL.AddSubscription(AddOption.CreateDatabase) <-- error occur

Error raised:

"cannot view indexed property"

I have sp3a installed in SQL Server 2000 and "sqlce.ppc3.arm.CAB" installed in the pocket pc.

Any idea?

Thanks!

|||

Can you catch the exception and dump the HRESULT, Major Number, Minor Number, Native Number, Error Collection ... etc from the exception for us to understand your problem better.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Ev, Microsoft Corp.

|||

Hi Laxmi,

Here is the error message;
Errors: System.data.sqlserverce.SqlCeErrorCollection

Count: 2

Item: Cannot view indexed property

HResult: -2147467259

InnerException: Nothing

Message "SqlCeException:

NativeError 28558

Source: "Microsoft SQL Server 2000 Windows CE Edition"

Any idea?

Thanks

Merge Replication using a guid as a dynamic filter

Hi ...

I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx

My next step would be to implement dynamic filtering using the guid of the entity as a parameter.

I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)

My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.

RMO-Code:

subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"

Filter:

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()

Function:

create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( @.host as uniqueidentifier)
return @.entity
End

When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@.entity, where @.entity is a guid

Thanks for your support

Alex

Hi, Alex,

You can convert the string to binary then to uniqueidentifier, like following:

declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
print @.entity

Output:

00450044-004C-004C-3000-340032003700

Thanks,

Zhiqiang Feng

|||

Thanks for the info changed the function according to your suggestion.

Now a new problem arrises: I receive 0 rows for the filtered tables. When changing the funktion to

ALTER FUNCTION [dbo].[fx_ConvertHostToEntity] ()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
-- return @.entity
return '4bb0e468-c68a-4253-ba82-f71c3a6e302d'
End

I get the rows in the replicated table. So the problem must be somewhere either in the creation of the sanpshots or the resolution of the hostname

|||

Using Replication Monitor for the publication I also found out using the properties of the publication that the snapshot for the data partition has not been created. So I did this manually and will code it later on. When replicating again I got the follwowing error shown up in the Replication Monitor (I left out the first one as I am not using web sync right now):

Partitioned snapshot validation failed for this Subscriber. The snapshot validation token stored in the specified partitioned snapshot location does not match the value '{00620034-0062-0030-6500-340036003800}' used by the Merge Agent when evaluating the parameterized filter function. If specifying the location of the partitioned snapshot (using -DynamicSnapshotLocation), you must ensure that the snapshot files in that directory belong to the correct partition or allow the Merge Agent to automatically detec (Source: MSSQL_REPL, Error number: MSSQL_REPL27223)

Find the full source code below:

Imports Microsoft.SqlServer.Replication

Imports Microsoft.SqlServer.Management.Common

Public Class Replication

Private subscriberName As String

Private publisherName As String

Private windowsLogin As String

Private windowsPWD As String

Private publicationName As String

Private publicationDbName As String

Private subscriptionDbName As String

Private passedHostname As String

''' <summary>

''' a new instance of the replication object

''' </summary>

''' <param name="EntityID">id of the entity as string</param>

''' <param name="SubscriberHost">the hostname of the subscribing sql instance</param>

''' <param name="PublisherHost">the hostname of the publishing sql instance</param>

''' <param name="Login">the name of the windows login to authenticate towards the publisher: domain\user</param>

''' <param name="PWD">the password</param>

''' <param name="Publication">the name of the puplicaiton on the publisher</param>

''' <param name="PublicationDB">the name of the puplication db</param>

''' <param name="SubscriptionDB">the name of the subscription db</param>

''' <remarks></remarks>

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

subscriberName = SubscriberHost

publisherName = PublisherHost

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

PassedHostname = EntityID.ToString

windowsLogin = Login

windowsPWD = PWD

publicationName = Publication

subscriptionDbName = SubscriptionDB

publicationDbName = PublicationDB

End Sub

Sub SetupPullSubscription()

'Create connections to the Publisher and Subscriber.

Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)

Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.

Dim publication As MergePublication

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

subscriberConn.Connect()

' Ensure that the publication exists and that

' it supports pull subscriptions.

publication = New MergePublication()

publication.Name = publicationName

publication.DatabaseName = publicationDbName

publication.ConnectionContext = publisherConn

If publication.LoadProperties() Then

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

publication.Attributes = publication.Attributes Or PublicationAttributes.AllowPull

End If

' Define the pull subscription.

subscription = New MergePullSubscription()

subscription.ConnectionContext = subscriberConn

subscription.PublisherName = publisherName

subscription.PublicationName = publicationName

subscription.PublicationDBName = publicationDbName

subscription.DatabaseName = subscriptionDbName

subscription.HostName = passedHostname

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

subscription.SynchronizationAgentProcessSecurity.Login = windowsLogin

subscription.SynchronizationAgentProcessSecurity.Password = windowsPWD

' Make sure that the agent job for the subscription is created.

subscription.CreateSyncAgentByDefault = True

' Create the pull subscription at the Subscriber.

subscription.Create()

Dim registered As Boolean = False

' Verify that the subscription is not already registered.

For Each existing As MergeSubscription In _

publication.EnumSubscriptions()

If existing.SubscriberName = subscriberName Then

registered = True

End If

Next

If Not registered Then

' Register the local subscription with the Publisher.

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

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

End If

Else

' Do something here if the publication does not exist.

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

End If

Catch ex As Exception

' Implement the appropriate error handling here.

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

Finally

subscriberConn.Disconnect()

publisherConn.Disconnect()

End Try

End Sub

Sub PullMergeReplication()

' Create a connection to the Subscriber.

Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

conn.Connect()

' Define subscription properties.

subscription = New MergePullSubscription()

subscription.ConnectionContext = conn

subscription.DatabaseName = subscriptionDbName

subscription.PublisherName = publisherName

subscription.PublicationDBName = publicationDbName

subscription.PublicationName = publicationName

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

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

subscription.SynchronizeWithJob()

Else

' Do something here if the subscription does not exist.

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

End If

Catch ex As Exception

' Do appropriate error handling here.

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

Finally

conn.Disconnect()

End Try

End Sub

End Class

|||

this means you're trying to apply a dynamic snapshot that's not for your partition. i.e. your filter is for 'StoreA', but you're trying to apply snapshot belonging to 'StoreB'.

|||

After finding no solution to the problem, feeling that the problem has something to to with the conversion vom guid to string, I decided to have a second col in the table that was filled with a trigger: the guid converted to nvarchar.

And suddenly I knew where the problem was hiding:

hostname value: 4bb0e468-c68a-4253-ba82-f71c3a6e302d

cast( convert(varbinary,host_name()) as uniqueidentifier) -> 4BB0E468-C68A-4253-BA82-F71C3A6E302D

And that is the solution: When using a guid as the hostname and filter, the values have to be either both lower case or upper case. Otherwise you will get an empty result for dynamicly filtered tables :)

Thanks for your support

Alex

Merge replication scenario - how to have inventory working properly

Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>

merge replication problem

I am working on merge replication.I am using sql server 2005 standard edition.Will things workout for me if i use this standard edition(just to know).

2. i have been working on merge replication on the same server but different databases.the way i do it is as follows

a.I create a database.

b.Create a publication(is has successfully been created)

c.Create a subscription(successfully created it)

d.Now when i look at the replication monitor when i click on my merge replication

under all subscription tab

status shows:uninitialized subscription and

Connection :it shows unknown

when i double click that it takes me to synchrinization history which has nothing going on with in it.

What would be the reason for the problem.

Please

Can you try the replication monitor and the sync history after you add some articles to the publication then run the snapshot agent on the publisher, and then run the merge agent to initialize the subscriber.|||

Thanks for your attention

after adding some articles.when i run the snapshot agent from

Local publications->my publication->view snap shot agent status->click start

it tries to start but then comes with a message saying "The agent has never been run."

and under local subscritpions->my subscription->view synchronization->

when i click start it says "No agent status information is available".

when i look at view history tab

The job failed. Unable to determine if the owner (xyz\myname) of job 1234511-testdb_merge-merge-27G6Y91-merge_sub- 0 has server access (reason: Could not obtain information about Windows NT group/user 'xyz\myname', error code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

What would be the casue if security permission.How should i set them.

please let me know.

sql

Merge Replication not working after 1st Sync

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

Merge replication not working (Push or Pull)

I'm trying to setup merge replication between two server (serverA and serverB). serverA houses the publisher DB (pubs) and Distribution DB (distribution), server B has the subscriber DB (subs). I tried first to setup a pull subscription, but the snapshot is never created in the subs db, even though the snapshot agent says everything was successful. The merge agent also never changes from the status 'Never Started'.
So I tried a push subscription. This gets me a little further, it creates the snapshot in the subs DB, but then the merge agent fails with the following error.
The subscription to publication 'pubs_customer_test' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
------
The remote server is not defined as a subscription server.
(Source: BBLABCW03 (Data source); Error number: 14010)
------
Please help. Thanks.After creating a pull subscription to a transactional Publication, the status
of the pull subscription in the Database -> Pull Subscription Folder will
never change from the default of 'Never Started'
The job and the Pull Subscription Agent will show history for the agent, but
the status does not change in this view.
The problem is that the name of the job is greater than 100 characters, and is
trying to be placed into a temporary table that only allows 100 characters for
the job name.
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'SQL02-MyProj-DBReplication-SQL01-RptDB-D3E11F67-B38E-4DE4-82AE-9A3358B902AF')
-- Get job ID
-- Run below command with the job id as input
sp_MSenum_replication_job @.job_id = '388BFC85-BB28-434F-A7AE-77EEBB1A3C8B'
-- If you see this error, then you may need to reduce the -- job description name to resolve this issue
Server: Msg 8152, Level 16, State 6, Procedure sp_help_jobhistory, Line 91
String or binary data would be truncated.
NOTE: Then name of the job is a automatically concatenated by SQL of the
Publishing Server Name + Publishing Database + Publication Name + Subscribing Server + Subscribing Database.

Merge Replication Issues

I was trying to test out how well my merge replication is working, so I
created a new table on my main database and on my remote computer I created
the same table but with data in it. I then tried to merge the two up and the
data from my remote computer did not appear on the table on the main
database. What am I doing wrong? Please let me know something...
Corey
Hey --
Well, without knowing all the details...if this was SQL 2K, then you need to
create the table and then add it as an article to the publication...then to
the subscription. So, if you did that already, and started the merge agents
and it STILL isn't working...slap an output file on the merge agent and lets
see what's going on.
Also, try looking in sp_helparticle and see if it even shows up as an
article (sysmergearticles).
Donna
"panacorey" wrote:

> I was trying to test out how well my merge replication is working, so I
> created a new table on my main database and on my remote computer I created
> the same table but with data in it. I then tried to merge the two up and the
> data from my remote computer did not appear on the table on the main
> database. What am I doing wrong? Please let me know something...
> Corey
|||Yes I am using SQL 2k, how would I add the output file to the merge agent?
Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
got the error "Invalid object name 'syspublications'
"Donna W" wrote:
[vbcol=seagreen]
> Hey --
> Well, without knowing all the details...if this was SQL 2K, then you need to
> create the table and then add it as an article to the publication...then to
> the subscription. So, if you did that already, and started the merge agents
> and it STILL isn't working...slap an output file on the merge agent and lets
> see what's going on.
> Also, try looking in sp_helparticle and see if it even shows up as an
> article (sysmergearticles).
> Donna
> "panacorey" wrote:
|||Hmmm...were you in the db that is published in Query Analyzer when you ran
sp_helparticle?
Cause if there's no syspublications, then replication isn't set up properly.
My guess is that you were in master when you ran it...
to put an output file on the agent, follow this article:
http://support.microsoft.com/kb/312292/en-us
For OutputVerboselevel put 3 (undocumented).
Remember to take this off later, as it will very seriously slow down
replicaton.
Look in Books Online for information on sp_addmergearticle, as well as
DEFINITELY read this in Books Online:
Schema Changes on Publication Databases
It's important that you are FULLY aware of what you can and can't do to a
replicated db.
Let us know what you find!
Donna
"panacorey" wrote:
[vbcol=seagreen]
> Yes I am using SQL 2k, how would I add the output file to the merge agent?
> Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
> got the error "Invalid object name 'syspublications'
> "Donna W" wrote:
|||Oh hey, that might be my fault...
sp_helpMERGEarticle...
I always want to forget that...
:D
Donna
"panacorey" wrote:
[vbcol=seagreen]
> Yes I am using SQL 2k, how would I add the output file to the merge agent?
> Sorry, but I am learning as I go. I tried to run the sp_helparticle and i
> got the error "Invalid object name 'syspublications'
> "Donna W" wrote:
|||By default merge replication will whack the table and its data on the
subscriber. While it is possible to put the table in place on the
subscriber, add the rowguid column and the required metadata, in general
this is not recommended.
To make life easier for your self, have merge replication deploy the
subscription itself.
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
"panacorey" <panacorey@.discussions.microsoft.com> wrote in message
news:1C31DDA1-2696-4470-A929-705C26127870@.microsoft.com...
>I was trying to test out how well my merge replication is working, so I
> created a new table on my main database and on my remote computer I
> created
> the same table but with data in it. I then tried to merge the two up and
> the
> data from my remote computer did not appear on the table on the main
> database. What am I doing wrong? Please let me know something...
> Corey

merge replication identity range drives me crazy

Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...

Quote:

Originally posted by Pera Pisar
Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...

|||I've solved the problem.
Some old settings for an early ajusted replication remained, so there were some constraints that didn't pass.
I didn't know that settings like identity range remain after the replication is deleted. Or did I do something wrong?

Merge Replication Identity Range Clash

We are using SQL Server 2000 merge replication and have a publisher with
three remote subscribers. Everything has been working well for a year but
there is a problem with a newly published table article. The table has a
primary key with the Not For Replication option set. However, the publisher
and two of the three subscribers are using the same identity range and
causing primary key violations. We are not sure as to why this has occurred
but it could be fixed by reseeding the identity range for the table at each
subscriber. Does anyone know of a way to do this?
Dropping the article and recreating will not be possible as we do not want
to drop the existing subscriptions.
Any suggestions would be appreciated
Adam
Adam,
you could synchronize, drop the publication then reseed the identity
columns, recreate the publication with manual control of the identity ranges
and add subscribers with the nosync option.
Regards,
Paul Ibison

Merge Replication Hangs at Connecting To Subscriber

This has been working great up until last night when I manuall stopped the
merge publication while it was publishing. When I tried to start it again
the merge agent pushing the data to remote SQL 2k does not get past
'Connecting to Subscriber: XXX'
Below I have pasted the log file and the agent startup parameters.
Agent Config:
-Publisher [SQL_PUBLISHER] -PublisherDB [DB_Source] -Publication
[Pub_Products] -Subscriber [SQL_SUBSCRIBER] -SubscriberDB [DB_Target]
-Distributor [SQL_PUBLISHER] -DistributorSecurityMode 1 -Continuous -Output
c:\mergeDebug.txt -OutputVerboseLevel 2
Debug Log (ran for 30 min):
**** Start Of Log ****
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
SQL_PUBLISHER-DB_Source-Pub_Products-SQL_SUBSCRIBER-6
Percent Complete: 0
Connecting to Distributor 'SQL_PUBLISHER'
Connecting to Distributor 'SQL_PUBLISHER.'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_MSgetversion }
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_helpdistpublisher
(N'SQL_PUBLISHER') }
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'SQL_PUBLISHER')
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
datasource, srvid from master..sysservers where upper(srvname) =
upper(N'SQL_SUBSCRIBER')
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_merge_agentid (0, N'DB_Source', N'Pub_Products', 1, N'DB_Target')}
[4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_profile (6, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Initializing
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: set nocount on declare
@.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255)
select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Percent Complete: 1
Connecting to Publisher 'SQL_PUBLISHER'
[4/14/2005 10:31:38 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
sp_MShelp_subscriber_info (N'SQL_PUBLISHER', N'SQL_SUBSCRIBER')}
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:38 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'SQL_SUBSCRIBER'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'DB_Source'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'DB_Target'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
Server: SQL_SUBSCRIBER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
Server: SQL_PUBLISHER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
**** End Of Log ****
there is a condition where resources are depleted on the publisher or
subcriber which could show up as this. Stop and start SQL Server to see if
it clears it.
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
"thejoneser" <thejoneser@.discussions.microsoft.com> wrote in message
news:C6C25879-5B53-43EE-9ACE-BB12C77FF7A4@.microsoft.com...
> This has been working great up until last night when I manuall stopped the
> merge publication while it was publishing. When I tried to start it again
> the merge agent pushing the data to remote SQL 2k does not get past
> 'Connecting to Subscriber: XXX'
> Below I have pasted the log file and the agent startup parameters.
> Agent Config:
> -Publisher [SQL_PUBLISHER] -PublisherDB [DB_Source] -Publication
> [Pub_Products] -Subscriber [SQL_SUBSCRIBER] -SubscriberDB [DB_Target]
> -Distributor [SQL_PUBLISHER] -DistributorSecurityMode
1 -Continuous -Output
> c:\mergeDebug.txt -OutputVerboseLevel 2
> Debug Log (ran for 30 min):
> **** Start Of Log ****
> Microsoft SQL Server Merge Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent:
> SQL_PUBLISHER-DB_Source-Pub_Products-SQL_SUBSCRIBER-6
> Percent Complete: 0
> Connecting to Distributor 'SQL_PUBLISHER'
> Connecting to Distributor 'SQL_PUBLISHER.'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_MSgetversion }
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.: {call sp_helpdistpublisher
> (N'SQL_PUBLISHER') }
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
> datasource, srvid from master..sysservers where upper(srvname) =
> upper(N'SQL_PUBLISHER')
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: select
> datasource, srvid from master..sysservers where upper(srvname) =
> upper(N'SQL_SUBSCRIBER')
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_merge_agentid (0, N'DB_Source', N'Pub_Products', 1,
N'DB_Target')}
> [4/14/2005 10:31:37 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_profile (6, 4, N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Initializing
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: set nocount on declare
> @.dbname sysname select @.dbname = db_name() declare @.collation
nvarchar(255)
> select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
> N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
> 'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
> collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Percent Complete: 1
> Connecting to Publisher 'SQL_PUBLISHER'
> [4/14/2005 10:31:38 AM]SQL_PUBLISHER.BackOfficeDistribution: {call
> sp_MShelp_subscriber_info (N'SQL_PUBLISHER', N'SQL_SUBSCRIBER')}
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:38 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Percent Complete: 2
> Connecting to Subscriber 'SQL_SUBSCRIBER'
> Percent Complete: 3
> Retrieving publication information
> Percent Complete: 4
> Retrieving subscription information
> Percent Complete: 4
> The merge process is cleaning up meta data in database 'DB_Source'.
> Percent Complete: 4
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> Percent Complete: 4
> The merge process is cleaning up meta data in database 'DB_Target'.
> Percent Complete: 4
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
> MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> Percent Complete: 4
> Uploading data changes to the Publisher
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:40 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> Connecting to Subscriber 'SQL_SUBSCRIBER.DB_Target'
> Server: SQL_SUBSCRIBER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_SUBSCRIBER.DB_Target: {call sp_MSgetversion }
> Connecting to Publisher 'SQL_PUBLISHER.DB_Source'
> Server: SQL_PUBLISHER
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4/14/2005 10:31:41 AM]SQL_PUBLISHER.DB_Source: {call sp_MSgetversion }
> **** End Of Log ****
>
|||Thanks Hilary.
I had to get this fixed yesterday so I deleted the subscription. Did a
manual sync using a scripting tool, then recreated the subscription.
I'm going to try and get my hands on the current hotfix baseline build since
several similar merge replication issues seem to be fixed in it.
-- Chris
"Hilary Cotter" wrote:

> there is a condition where resources are depleted on the publisher or
> subcriber which could show up as this. Stop and start SQL Server to see if
> it clears it.
> --
> 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
> "thejoneser" <thejoneser@.discussions.microsoft.com> wrote in message
> news:C6C25879-5B53-43EE-9ACE-BB12C77FF7A4@.microsoft.com...
> 1 -Continuous -Output
> N'DB_Target')}
> nvarchar(255)
>
>

Merge replication foreign key problem

Hi there. I'm somewhat new to merge replication, and I've been having an issue with one of the scenarios that I've been trying to get working. I am using SQL 2005 on the server, with 2005 express on the client. I have 2 tables:

Photo - which has a PhotoID primary key

PhotoData - which has a PhotoDataID primary key, and a PhotoID foreign key

both primary keys are int, and set to identity. I only want the Photo table to replicate for the merge, because I want the data in the PhotoData table to only be called by demand through a web service (since the images in that table are too large to be included in the normal replication). However, when a client adds a photo to his local database (which adds a record in the photo table, and then it's actual image data in the photodata table), I can't sync the photo table any longer. The error I get is:

Could not drop object 'dbo.Photo' because it is referenced by a FOREIGN KEY constraint

I have the foreign key relationship marked as "not for replication", but that doesn't seem to help. Is there another way I should be doing this? Thanks for any help!

-PHil

Does the client have the Photo and the PhotoData tables with some data in them before it is initilaized?

If so and you dont want to lose them, then you can select the article's property of @.pre_creation_cmd='none'. By default this is set to 'drop ' and at first synchronization, the tables are tried to drop. Now when there is data and the Photo table is tried to drop, it will conflict with the FK.

|||

Hi, Philip!

Maybe, a good solution would be using data filters in merge replication? You can define data filters to filter rows of published data according on condition, dependent on current SQL server. In this case you must include both tables into publication, but data from 2nd table will not be exchanged.

Another solution - to use vertical filtering, by columns. You can exclude column, containing actual image data, from 2nd table in publication.

Both methods are well described in SQL Server Books Online (Replication -> Replication Options -> Filtering Published Data)

David.

|||

p.s. Note, that horizontal filtering (row-based) is more complex technology, and requires careful testing before using in real working system!

Good luck!

|||

Wouldn't vertical filtering not work out when I need to add the photo data into the client database after the replication (via a web service, not replication)? I need that column to exist on the client's PhotoData table, but I just don't want it's data to be replicated. My understanding of vertical filtering is that it completely omits the column entirely, but if there is a way around that, then that woudl be useful.

Otherwise, I'm not sure I follow what you mean with the horizontal filtering. Do a filter where I sync all PhotoData rows with a PhotoDataID < 0 (i.e., no rows at all), and then just add them via the web service afterwards?

Thanks for your help!

|||

Yes, the client will have data inside it before initialization, and it'll be a pull subscription. Sorry for a basic question, but where do I change this setting you're talking about?

|||The property is for sp_addmergearticle or you can set it in UI too.|||

You are right, vertical filtering will remove the column from table at subscriber.

Try to use horizontal filtering as you specified (with simple condition to synchronize no rows).

I think, this will work.

|||

I just tried your suggestions, and got the following error when I tried to synchronize:

Message
2006-12-26 11:47:07.177 {call sp_MSsetconflicttable (N'Photo', N'MSmerge_conflict_EvidenceToolPublication_Photo', N'DEVSERVER', N'EvidenceToolDatabase', N'EvidenceToolPublication')}
2006-12-26 11:47:07.364 Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSsetconflicttable (N'Photo', N'MSmerge_conflict_EvidenceToolPublication_Photo', N'DEVSERVER', N'EvidenceToolDatabase', N'EvidenceToolPublication')}
2006-12-26 11:47:07.442 Category:SQLSERVER
Source: JOHNSON-9400
Number: 102
Message: Incorrect syntax near 'PhotoID'.

Where Devserver is the server's name, EvidenceToolPublication is the name of the publication, and EvidenceToolDatabase is the name of the database.

|||I've just tried it this way, and it doesn't work. It still gives me a foreign key issue, since the data isn't being replicated.|||This seems like some issue when creating the conflict procs/tables. Would you be able to get a trimmed down version of the repro script and post it here?

Merge replication failure

After working for about 30 minutes (delivering the snapshot) the replication
failed
I checked the Session details of the subscription and I found Action Message
saying the 'The process could not deliver the snapshot to the Subscriber.'
In the error information under Data Source I found the following message
'General network error. Check your network documentation.'
Checking the network log I got the following message: SQL Server Scheduled
Job 'SERVER\SERVER1-MRN1-MRN1_rep-XYZSERVER.ABC.LOCAL-12'
(0x97E3EAE1F1106144B16C808A6492CB9C) - Status: Failed - Invoked on:
2006-09-28 20:23:00 - Message: The job failed. The Job was invoked by
Schedule 35 (Replication agent schedule.). The last step to run was step 3
(Detect nonlogged agent shutdown.).
Any clue?
Samuel
Samuel,
what happens if you restart the agent - normally this'll solve a network
outage.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It happens consistantly during the initial stage when the entire database
was replicated
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uN7b$g84GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Samuel,
> what happens if you restart the agent - normally this'll solve a network
> outage.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Samuel,
I'd enable logging to see if this gets more info
(http://support.microsoft.com/?id=312292), especially if the failure is at
the same point each time. Otherwise I'd look at monitoring the network for
outages - network connectivity problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Merge Replication Error - Failed to enumerate changes in the filtered article

Hi,
I am using Merge Replication and have filtered some of the tables for teh
subscriptions, however, everything was working fine, then all of a sudden, I
get this error. (See below)
Does anyone know what would cause this and how I can overcome it?
Thanks in Advance
Warren
************************************************** *
******************ERROR DETAILS******************
************************************************** *
Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
Error Message: Failed to enumerate changes in the filtered articles.
Error Details:
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error number: -2147200925)
Incorrect syntax near the keyword 'where'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
Incorrect syntax near the keyword 'and'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
************************************************** *
*******************END OF ERROR******************
************************************************** *
Could you provide more information on your database and replication filters
?
Are replicated objects owned by dbo ?
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using Merge Replication and have filtered some of the tables for teh
> subscriptions, however, everything was working fine, then all of a sudden,
I
> get this error. (See below)
> Does anyone know what would cause this and how I can overcome it?
> Thanks in Advance
> Warren
> ************************************************** *
> ******************ERROR DETAILS******************
> ************************************************** *
> Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
> Error Message: Failed to enumerate changes in the filtered articles.
> Error Details:
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error number: -2147200925)
> ----
--
> --
> Incorrect syntax near the keyword 'where'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> Incorrect syntax near the keyword 'and'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> ************************************************** *
> *******************END OF ERROR******************
> ************************************************** *
>
|||Hi,
Thanks for your reply.
The objects are owned by DBO.
We are using Merge replication will pull subscriber.
Some tables are filtered, in the publication properties, if I go to FILTER
ROWS, then FILTER CLAUSE column, some tables are filtered like so
depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
where x makes up the guid.
is that enough information? what else do you need?
Many thanks
"Kestas" <kicker.lt@.noospamm-tut.by> wrote in message
news:%232ddCNsKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Could you provide more information on your database and replication
filters[vbcol=seagreen]
> ?
> Are replicated objects owned by dbo ?
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
teh[vbcol=seagreen]
sudden,
> I
> ----
> --
> ----
> --
> ----
> --
>
|||Also would be good to know which exact SQL Server version you are runing
SELECT @.@.VERSION
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Thanks for your reply.
> The objects are owned by DBO.
> We are using Merge replication will pull subscriber.
> Some tables are filtered, in the publication properties, if I go to FILTER
> ROWS, then FILTER CLAUSE column, some tables are filtered like so
> depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
> where x makes up the guid.
> is that enough information? what else do you need?
> Many thanks
|||Hi,
Publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
Subscriber:
MSDE
Kind Regards
Warren
"Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Also would be good to know which exact SQL Server version you are runing
> SELECT @.@.VERSION
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
FILTER
>
|||Anyone have any ideas?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Publisher:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> Subscriber:
> --
> MSDE
>
> Kind Regards
> Warren
>
> "Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
> news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...
> FILTER
>
|||can you post your schema and publication script here?
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
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Anyone have any ideas?
>
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
runing
>
|||Hi Hilary,
By publication script, I assume you mean the script to create the
publication (right-click publication --> Generate SQL Script)?
And the schema? Do you want the published databases schema?
can you advise,
thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post your schema and publication script here?
> --
> 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
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...
> runing
to
>
|||Anyone able to help on this?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:OGkKj9FMFHA.3420@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> By publication script, I assume you mean the script to create the
> publication (right-click publication --> Generate SQL Script)?
> And the schema? Do you want the published databases schema?
> can you advise,
> thanks
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...
> to
so
>

merge replication error

Hi,

We have an HTTPS merge publication which has been working fine, but all of a sudden the subscription for a subscriber is failing with the following message at the publisher:

Error messages: The process could not read the request message due to OS error
10054. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147014842) Get help:
http://help/MSSQL_REPL-2147014842 The format of a message during Web
synchronization was invalid. Ensure that replication components are properly
configured at the Web server. (Source: MSSQL_REPL, Error number: MSSQL_REPL-
2147199374) Get help: http://help/MSSQL_REPL-2147199374 The subscription to
publication 'yarraman main' could not be verified. Ensure that all Merge Agent
command line parameters are specified correctly and that the subscription is
correctly configured. If the Publisher no longer has information about this
subscription, drop and recreate the subscription. (Source: MSSQL_REPL, Error
number: MSSQL_REPL-2147201019) Get help: http://help/MSSQL_REPL-2147201019

We have 2 subscribers to this publication and it is working fine for the other subscriber..

Help !

thanks
Bruce

Well

We rebooted their ADSL router and all is now working - strange - given the other subscriber on the same network was working fine. Who knows !

Thanks
bruce

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 corruption (system triggers and views)

All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:

Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?

-mikeI found another change.. we added a linked server using sp_addlinkedserver.. Any thoughts?|||Does your merge subset filter clauses or join filter clauses contain views that contain WITH CHECK OPTION, pointing to remote table?|||No filters are set for the publication.|||

ok, then you have to trace your steps to see exactly what changes were made that would cause this, and see if you can back them out one by one.

any idea what the linked server would have to do with regards to the views, triggers, or any of the published tables? are you making changes from a remote machine?

Merge replication columns limit?

Hi,

I am experiencing a wired problem withe merge replication and SQL 2000 SP4.

We had replication working for 4 years without a problem, one of the table that we replicate has been growing in columns, now it has 55 columns.

I have noticed a problem, when I update the last column on the table and waits for the change to be updated on the subscriber the change is not there. But if I replicate the first 40 columns it works.

Changes made:

1) Upgraded to SP4
2) Added some extra fields using sp_addreplcolumn

Is this a known bug? Any ideas?

Regards

It looks like the adding of the column is not regenerating the triggers correctly. Could you profile the repladdcolumn and see if the trigger generation has any issue there?

On a side note, there are known issues with SQL 2000 + column tracking + vertical partitioning + DDL.

Is this a production box? I would then recommend contacting CSS.

Also would this have happened on SQL 2000 SP3 too? If you can test that too, it would be great.

|||Forgot to add that 55 is definitely not the limit for number of columns. The limit I think is 246 for SQL 2005 and I think it should be the same for SQL 2000 too.

Merge replication between two publishers with dynamic filters

Hello,
I am working on a distributed database system in which each site is a
publisher of a filtered set of data. It is necessary that a publisher can
subscribe to another publisher.
I am using merge replication, dynamic filters and push subscriptions. Each
site publishes the same tables with another filter.
When I test this scenario, I notice that the merge agent replicates only the
changes of the publisher to the subscriber. A change on the subscriber (thas
has also a publication on the same tables) is not replicated to the publisher.
Does someone know a solution for this?
Is it possible to have (bidirectional) merge replication between two
publishers with dynamic filters?
I hope someone can help me with this.
thanks in advance!
Marco Broenink
Could you please describe in more detail your publisher subscriber
configurations and the filters.
If the table has a column that you are using to filter from Publisher to
Subscriber and then make this subscriber a republisher and then try to use
the same column as the filter column, there is only set of data at the
republisher/subscriber.
I am not clear on your setup. Could you please repost with more elaborate
setup steps?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> Hello,
> I am working on a distributed database system in which each site is a
> publisher of a filtered set of data. It is necessary that a publisher can
> subscribe to another publisher.
> I am using merge replication, dynamic filters and push subscriptions. Each
> site publishes the same tables with another filter.
> When I test this scenario, I notice that the merge agent replicates only
the
> changes of the publisher to the subscriber. A change on the subscriber
(thas
> has also a publication on the same tables) is not replicated to the
publisher.
> Does someone know a solution for this?
> Is it possible to have (bidirectional) merge replication between two
> publishers with dynamic filters?
> I hope someone can help me with this.
> thanks in advance!
> Marco Broenink
|||Thanks for your response!
I am using a dynamic filter. This filter uses a function. This function
needs the hostname and a filter-column to dermine if the row needs to be
filtered. The filter looks like:
SELECT <published_columns> FROM [dbo].[PublishedTable]
WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
The filterfunction uses a mapping table that maps the contents of the
[FilterColumn] to hostnames.
With this mapping table, each publisher publishes its own part of all data.
So the publications of two publishers do not overlap. But the publications
are on the same tables.
Problem with this configuration is that changes of a subscriber are not
replicated to the publisher. It looks like that the subscriber's own
publication is blocking this.
I hope you can help me with this.
greetings, Marco.
"Mahesh [MSFT]" wrote:

> Could you please describe in more detail your publisher subscriber
> configurations and the filters.
> If the table has a column that you are using to filter from Publisher to
> Subscriber and then make this subscriber a republisher and then try to use
> the same column as the filter column, there is only set of data at the
> republisher/subscriber.
> I am not clear on your setup. Could you please repost with more elaborate
> setup steps?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> the
> (thas
> publisher.
>
>
|||I have used 'global' subscriptions in stead of 'local' and this problem is
solved.
Now the changes are also replicated from subscriber to publisher.
Unfortunately, I have a new problem.
I use replication with dynamic filters. In my system it is possible that
data is added to the subscriber that doesnot pass the filter. When
replicating, this data is deleted at the subscriber and added to the
publisher.
How can I prevent this delete & insert ?
thanks in advance, Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Thanks for your response!
> I am using a dynamic filter. This filter uses a function. This function
> needs the hostname and a filter-column to dermine if the row needs to be
> filtered. The filter looks like:
> SELECT <published_columns> FROM [dbo].[PublishedTable]
> WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
> The filterfunction uses a mapping table that maps the contents of the
> [FilterColumn] to hostnames.
> With this mapping table, each publisher publishes its own part of all data.
> So the publications of two publishers do not overlap. But the publications
> are on the same tables.
> Problem with this configuration is that changes of a subscriber are not
> replicated to the publisher. It looks like that the subscriber's own
> publication is blocking this.
> I hope you can help me with this.
> greetings, Marco.
> "Mahesh [MSFT]" wrote:
|||Glad that you could work around your first problem, though to be frank, I am
still unclear of the setup.
Regarding your new problem,
If each subscriber inserts data that corresponds to only its subset of data
then you could try using a default of some kind to the tables. Like hostname
or something that will map appropriately to the filter condition and make it
pass. So everytime an insert happens at the subscriber, the filter condition
is met and then is successfully propagated to the publisher and does not get
deleted at the subscriber in turn.
Please note that this can work only if the subscriber always makes
"good" inserts, that is to say that the subscriber never expects to insert
data (that does not satisfy the filter) and then in turn expects the data to
be deleted by the publisher.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...[vbcol=seagreen]
> I have used 'global' subscriptions in stead of 'local' and this problem is
> solved.
> Now the changes are also replicated from subscriber to publisher.
> Unfortunately, I have a new problem.
> I use replication with dynamic filters. In my system it is possible that
> data is added to the subscriber that doesnot pass the filter. When
> replicating, this data is deleted at the subscriber and added to the
> publisher.
> How can I prevent this delete & insert ?
> thanks in advance, Marco
>
> "Marco Broenink" wrote:
data.[vbcol=seagreen]
publications[vbcol=seagreen]
to[vbcol=seagreen]
use[vbcol=seagreen]
elaborate[vbcol=seagreen]
message[vbcol=seagreen]
a[vbcol=seagreen]
publisher can[vbcol=seagreen]
subscriptions. Each[vbcol=seagreen]
only[vbcol=seagreen]
subscriber[vbcol=seagreen]
|||thanks again for the response.
In my topology, I have different publishers of the same table. These
publishers use different filters. A subscriber can be subscribed to different
publishers.
For example:
Site A publishes table1
Site B publishes table1
Site C is subscribed to Site A table1. This subscribtion is filtered with a
dynamic filter F1.
Site C is also subscribed to Site B table1. This subscribtion is filtered
with another dynamic filter F2.
The different dynamic filters make sure that the subscription to Site A do
not overlap the subscription to Site B.
Thus: The table1 of C contains a subset of table1 of A and a subset of
table1 of B.
So: table1 of C contains two types of data:
- data that meets filtercondition F1 and doesnot meet filtercondition F2.
- data that meets filtercondition F2 and doesnot meet filtercondition F1.
So the problem is: The subscriber will contain data that doesnot meet one of
the filterconditions. When replicating to Site A (filter F1), data of filter
F2 is deleted. When replicating to Site B (filter F2), data of filter F1 is
deleted.
So in this scenario, I think it is not possible to make only 'good' inserts
because it violates always one of the two filtersconditions.
I hope you know a solution. Or am I trying to do something impossible?
greetings, Marco Broenink
"Mahesh [MSFT]" wrote:

> Glad that you could work around your first problem, though to be frank, I am
> still unclear of the setup.
> Regarding your new problem,
> If each subscriber inserts data that corresponds to only its subset of data
> then you could try using a default of some kind to the tables. Like hostname
> or something that will map appropriately to the filter condition and make it
> pass. So everytime an insert happens at the subscriber, the filter condition
> is met and then is successfully propagated to the publisher and does not get
> deleted at the subscriber in turn.
> Please note that this can work only if the subscriber always makes
> "good" inserts, that is to say that the subscriber never expects to insert
> data (that does not satisfy the filter) and then in turn expects the data to
> be deleted by the publisher.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...
> data.
> publications
> to
> use
> elaborate
> message
> a
> publisher can
> subscriptions. Each
> only
> subscriber
>
>
|||Hi Marco,
Please correct me if I understood wrong:
So what you are saying is SiteA and SiteB are publishing the same tables,
but are not replicating to each other. Is that right?
But in turn are replicating that table to SiteC.
This is not supported.
In the first place, When SiteC subscribed to SiteA, it gets the table from
SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
configure? Did you configure a no-sync subscription? If not, and you used
all the default settings then actually you will not even be able to complete
the subscription because the table at SiteC (got from SiteA) will be
attempted to drop and recreate with the scripts from SiteB which will fail.
If you want to do what you are trying to do, one solution is to have table1
at SiteA and replicate it to SiteC with the proper filter. Have table2 at
SiteB and replicate that to SiteC with the proper filter.
On the subscriber you can have a view on those two tables that will give you
a combined view for the results. But you may not be able to make DMLs on the
view directly. You will still need to do the DMLs on the actual tables.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> thanks again for the response.
> In my topology, I have different publishers of the same table. These
> publishers use different filters. A subscriber can be subscribed to
different
> publishers.
> For example:
> Site A publishes table1
> Site B publishes table1
> Site C is subscribed to Site A table1. This subscribtion is filtered with
a
> dynamic filter F1.
> Site C is also subscribed to Site B table1. This subscribtion is filtered
> with another dynamic filter F2.
> The different dynamic filters make sure that the subscription to Site A do
> not overlap the subscription to Site B.
> Thus: The table1 of C contains a subset of table1 of A and a subset of
> table1 of B.
> So: table1 of C contains two types of data:
> - data that meets filtercondition F1 and doesnot meet filtercondition F2.
> - data that meets filtercondition F2 and doesnot meet filtercondition F1.
> So the problem is: The subscriber will contain data that doesnot meet one
of
> the filterconditions. When replicating to Site A (filter F1), data of
filter
> F2 is deleted. When replicating to Site B (filter F2), data of filter F1
is
> deleted.
> So in this scenario, I think it is not possible to make only 'good'
inserts[vbcol=seagreen]
> because it violates always one of the two filtersconditions.
> I hope you know a solution. Or am I trying to do something impossible?
> greetings, Marco Broenink
>
> "Mahesh [MSFT]" wrote:
I am[vbcol=seagreen]
data[vbcol=seagreen]
hostname[vbcol=seagreen]
make it[vbcol=seagreen]
condition[vbcol=seagreen]
get[vbcol=seagreen]
insert[vbcol=seagreen]
data to[vbcol=seagreen]
message[vbcol=seagreen]
problem is[vbcol=seagreen]
that[vbcol=seagreen]
function[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
not[vbcol=seagreen]
Publisher[vbcol=seagreen]
try to[vbcol=seagreen]
the[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
replicates[vbcol=seagreen]
the[vbcol=seagreen]
two[vbcol=seagreen]
|||thanks for the help!
Marco
"Mahesh [MSFT]" wrote:

> Hi Marco,
> Please correct me if I understood wrong:
> So what you are saying is SiteA and SiteB are publishing the same tables,
> but are not replicating to each other. Is that right?
> But in turn are replicating that table to SiteC.
> This is not supported.
> In the first place, When SiteC subscribed to SiteA, it gets the table from
> SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
> configure? Did you configure a no-sync subscription? If not, and you used
> all the default settings then actually you will not even be able to complete
> the subscription because the table at SiteC (got from SiteA) will be
> attempted to drop and recreate with the scripts from SiteB which will fail.
> If you want to do what you are trying to do, one solution is to have table1
> at SiteA and replicate it to SiteC with the proper filter. Have table2 at
> SiteB and replicate that to SiteC with the proper filter.
> On the subscriber you can have a view on those two tables that will give you
> a combined view for the results. But you may not be able to make DMLs on the
> view directly. You will still need to do the DMLs on the actual tables.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> different
> a
> of
> filter
> is
> inserts
> I am
> data
> hostname
> make it
> condition
> get
> insert
> data to
> message
> problem is
> that
> function
> to be
> the
> all
> not
> Publisher
> try to
> the
> no
> in
> is
> replicates
> the
> two
>
>