Hello Everyone,
I am running SQL2K with sp3, continuous merge replication, only one
subscriber.
My current setting for Subscription expiration is the default of 7, I
have just recently changed this from the default of 14 days, as our
Subscriber is basically always available.
I am still trying to figure out how to cleanup my merge metadata. From
what I have read and from what I understand ,
sp_mergemetadataretentioncleanup is automatically run, if the agent
running Merge has the Parameter for MetadataRententionCleanup set to 1
I am under the assumption that if I have has the Parameter for
MetadataRententionCleanup set to 1 it should automatically be cleaning
up my metadata. Even though I have it set to 1 it does not appear
like it is actually cleaning out MSmerge_contents or MSmerge_tombstone.
These tables just keep growing.
Now if I had this parameter set to 0 then I would need to use
sp_mergecleanupmetadata to clean up MSmerge_contents or
MSmerge_tombstone.
Would there ever be a reason for running both?
I did go to BOL but I read the following about using
sp_mergecleanupmetadata:
If you want to run sp_mergecleanupmetadata without the subscriptions
being marked for reinitialization:
Stop all updates to the publication and subscription databases.
Unfortunately, I cannot stop all updates as our servers need to be
available 24/7.
Does anyone run this without stopping replication first? And if you do
run it do you have MetadataRententionCleanup set to 0?
If anyone can help I truly would appreciate it.
Thanks,
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
are you using anonymous subscribers? Merge replication does not clean
up the metadata as efficiently with anonymous subscrptions as opposed
to named subscriptions.
|||Thanks Hilary.
Unfortunately no, we do not have any anonymous subscriptions only one
named subscriptions.
Any other ideas?
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
|||I believe I have my answer:
1) sp_mergecleanupmetadata
2) sp_mergemetadataretentioncleanup
The First should be used for topologies with SQL Server 2000 SP1 or less
(which is not my case)
The Second one is the one to control the process manually for SQL Server
> SP1 which is your case and when the MetadataretentionCleanup is set to
0 (not automatic)
There is also a catch though the second sp is executed automatically by
merge Agent at at startup! when MetadataretentionCleanup is set to 1
therefore when in continuous mode it will be called only once.
So I'll need to manually run sp_mergemetadataretentioncleanup or setup a
job that will do it for me on a scheduled basis.
Thanks for your help Hilary.
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label subscription. Show all posts
Showing posts with label subscription. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Merge doesn't re-propagate changes arrived from Transaccional repl
I have a the following scenario:
- A transactional publicaction "TRN2000" (in SrvSQLServer2000 server)
- A push subscription to "TRN2000" publication(in SrvSQLServer2005 server)
- A merge publication "Merge2005" defined in SrvSQLServer2005 over the same
Database/tables
that transactional subscription replicate
- Subscriptions to "Merge2005" publication
Then, this is the behavior:
- Data changes in SrvSQLServer2000 are replicated to SrvSQLServer2005 through
the TRN2000 subscription (OK)
- When I directly modify data(update, insert,) in the SrvSQLServer2005,
merge susbscriptions
receives data changes (OK)
But the problem is that data changes received in the SrvSQLServer2005
database
through the "Trn2000" replication..are not propagated to the "Merge2005"
subscriptions..
I suspect that the problem is that Transactional Push subscription use Bulk
Operations over
SrvSqlServer2005 and these are being ignored by the "Merge2005" replication
triggers.
some idea? workaround?
thanks in advance,
Alexander.
If the problem occurs during initialization, have a look at the
@.fire_triggers_on_snapshot parameter in sp_addarticle.
HTH,
Paul Ibison
|||Thanks Paul for the answer.
Unfortunately, the problem happens not only at initialization. It happens
when propagating any change on the data..
some idea?
"Paul Ibison" wrote:
> If the problem occurs during initialization, have a look at the
> @.fire_triggers_on_snapshot parameter in sp_addarticle.
> HTH,
> Paul Ibison
|||In that case I'm confused. The commands replicated are sent down as calls to
insert/update/delete stored procedures on the subscriber. This isn't like a
bulk insert or a fast load which themselves don't fire triggers. Is your
transactional setup one that involves updating subscribers ie has triggers of
its own? Are there any other triggers on the table? Are any/all triggers
marked as NOT FOR REPLICATION?
Rgds,
Paul Ibison
|||Table “T” on which the Transaction replication records the data (same table
“T” that also is published through the Merge replication) doesn’t have other
triggers.
OK, the use of “Bulk Insert” by the Transactional replication was only a
supposition.
I thought that insert could be doing “bulk insert” because if I execute:
sp_addtabletocontents @.table_name='T'
Merge replication replicate pending changes (changes that became on table
“T” through the Transaccional replication) to its subscribers.
(I could send you by mail my replication scripts if you think that they can
clarify something)
"Paul Ibison" wrote:
> In that case I'm confused. The commands replicated are sent down as calls to
> insert/update/delete stored procedures on the subscriber. This isn't like a
> bulk insert or a fast load which themselves don't fire triggers. Is your
> transactional setup one that involves updating subscribers ie has triggers of
> its own? Are there any other triggers on the table? Are any/all triggers
> marked as NOT FOR REPLICATION?
> Rgds,
> Paul Ibison
>
|||I'll have a go at reproducing and then post back...Meanwhile can you confirm
that the changes are definitely coming from transactional replication and not
being bulk loaded into the republished table directly.
Paul Ibison
|||Surely,
I have verified that making data modifications (through sql standard
sentences insert, update, delete)
on the database that has the transaccional publication, the changes arrive
correctly at table T and these
same changes are not re-propagated to merge subscriptions.
I thank for your dedication in this subject
Alexander Wolff
|||Hello Alexander & Paul,
I'm having exactly the same problem,
I wonder if you've found a solution (or explanation) for this issue.
Thanks,
Vladimir Kofman
> Surely,
> I have verified that making data modifications (through sql standard
> sentences insert, update, delete)
> on the database that has the transaccional publication, the changes
> arrive
> correctly at table T and these
> same changes are not re-propagated to merge subscriptions.
> I thank for your dedication in this subject
> Alexander Wolff
>
- A transactional publicaction "TRN2000" (in SrvSQLServer2000 server)
- A push subscription to "TRN2000" publication(in SrvSQLServer2005 server)
- A merge publication "Merge2005" defined in SrvSQLServer2005 over the same
Database/tables
that transactional subscription replicate
- Subscriptions to "Merge2005" publication
Then, this is the behavior:
- Data changes in SrvSQLServer2000 are replicated to SrvSQLServer2005 through
the TRN2000 subscription (OK)
- When I directly modify data(update, insert,) in the SrvSQLServer2005,
merge susbscriptions
receives data changes (OK)
But the problem is that data changes received in the SrvSQLServer2005
database
through the "Trn2000" replication..are not propagated to the "Merge2005"
subscriptions..
I suspect that the problem is that Transactional Push subscription use Bulk
Operations over
SrvSqlServer2005 and these are being ignored by the "Merge2005" replication
triggers.
some idea? workaround?
thanks in advance,
Alexander.
If the problem occurs during initialization, have a look at the
@.fire_triggers_on_snapshot parameter in sp_addarticle.
HTH,
Paul Ibison
|||Thanks Paul for the answer.
Unfortunately, the problem happens not only at initialization. It happens
when propagating any change on the data..
some idea?
"Paul Ibison" wrote:
> If the problem occurs during initialization, have a look at the
> @.fire_triggers_on_snapshot parameter in sp_addarticle.
> HTH,
> Paul Ibison
|||In that case I'm confused. The commands replicated are sent down as calls to
insert/update/delete stored procedures on the subscriber. This isn't like a
bulk insert or a fast load which themselves don't fire triggers. Is your
transactional setup one that involves updating subscribers ie has triggers of
its own? Are there any other triggers on the table? Are any/all triggers
marked as NOT FOR REPLICATION?
Rgds,
Paul Ibison
|||Table “T” on which the Transaction replication records the data (same table
“T” that also is published through the Merge replication) doesn’t have other
triggers.
OK, the use of “Bulk Insert” by the Transactional replication was only a
supposition.
I thought that insert could be doing “bulk insert” because if I execute:
sp_addtabletocontents @.table_name='T'
Merge replication replicate pending changes (changes that became on table
“T” through the Transaccional replication) to its subscribers.
(I could send you by mail my replication scripts if you think that they can
clarify something)
"Paul Ibison" wrote:
> In that case I'm confused. The commands replicated are sent down as calls to
> insert/update/delete stored procedures on the subscriber. This isn't like a
> bulk insert or a fast load which themselves don't fire triggers. Is your
> transactional setup one that involves updating subscribers ie has triggers of
> its own? Are there any other triggers on the table? Are any/all triggers
> marked as NOT FOR REPLICATION?
> Rgds,
> Paul Ibison
>
|||I'll have a go at reproducing and then post back...Meanwhile can you confirm
that the changes are definitely coming from transactional replication and not
being bulk loaded into the republished table directly.
Paul Ibison
|||Surely,
I have verified that making data modifications (through sql standard
sentences insert, update, delete)
on the database that has the transaccional publication, the changes arrive
correctly at table T and these
same changes are not re-propagated to merge subscriptions.
I thank for your dedication in this subject
Alexander Wolff
|||Hello Alexander & Paul,
I'm having exactly the same problem,
I wonder if you've found a solution (or explanation) for this issue.
Thanks,
Vladimir Kofman
> Surely,
> I have verified that making data modifications (through sql standard
> sentences insert, update, delete)
> on the database that has the transaccional publication, the changes
> arrive
> correctly at table T and these
> same changes are not re-propagated to merge subscriptions.
> I thank for your dedication in this subject
> Alexander Wolff
>
Monday, March 26, 2012
merge agent status
Hello All -
I have a push merge subscription using Sql Server 2000. The publisher &
distributor reside on the same host.
I would like to be able for clients to determine the status of their merge
agent at the publisher. This would be used as a verification at the client,
that their merge agent is properly running and there is no error condition.
In the ReplicationMonitor/Merge Agent display, the status "No data needs to
be merged" displays when everything is functioning properly. I figure this
status must be available in some system table for the given merge agent.
Anyone know of the field / table to retrieve, in order to determine the
status of a merge agent? Is there some system sp to run?
Thanks in advance.
Regards,
bruce
its in the distribution database, query the run_status column on
MSmerge_history
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:V8ebc.3251$WA4.2734@.twister.nyc.rr.com...
> Hello All -
> I have a push merge subscription using Sql Server 2000. The publisher &
> distributor reside on the same host.
> I would like to be able for clients to determine the status of their merge
> agent at the publisher. This would be used as a verification at the
client,
> that their merge agent is properly running and there is no error
condition.
> In the ReplicationMonitor/Merge Agent display, the status "No data needs
to
> be merged" displays when everything is functioning properly. I figure
this
> status must be available in some system table for the given merge agent.
> Anyone know of the field / table to retrieve, in order to determine the
> status of a merge agent? Is there some system sp to run?
>
> Thanks in advance.
> Regards,
> bruce
>
>
sql
I have a push merge subscription using Sql Server 2000. The publisher &
distributor reside on the same host.
I would like to be able for clients to determine the status of their merge
agent at the publisher. This would be used as a verification at the client,
that their merge agent is properly running and there is no error condition.
In the ReplicationMonitor/Merge Agent display, the status "No data needs to
be merged" displays when everything is functioning properly. I figure this
status must be available in some system table for the given merge agent.
Anyone know of the field / table to retrieve, in order to determine the
status of a merge agent? Is there some system sp to run?
Thanks in advance.
Regards,
bruce
its in the distribution database, query the run_status column on
MSmerge_history
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:V8ebc.3251$WA4.2734@.twister.nyc.rr.com...
> Hello All -
> I have a push merge subscription using Sql Server 2000. The publisher &
> distributor reside on the same host.
> I would like to be able for clients to determine the status of their merge
> agent at the publisher. This would be used as a verification at the
client,
> that their merge agent is properly running and there is no error
condition.
> In the ReplicationMonitor/Merge Agent display, the status "No data needs
to
> be merged" displays when everything is functioning properly. I figure
this
> status must be available in some system table for the given merge agent.
> Anyone know of the field / table to retrieve, in order to determine the
> status of a merge agent? Is there some system sp to run?
>
> Thanks in advance.
> Regards,
> bruce
>
>
sql
Merge agent start failure
Hello All -
I have a problem with push merge subscription. The publisher/distributer
reside on same host with several clients connecting for merge replication.
The startup of one of the merge agents fails. The error message "Login
failed for user 'dbApp'. Reason: not associated with a trusted SQL server
connection. Error 18452".
I am able to connect to the client via QueryAnalyer using the 'dbApp' login.
I've done A/B checking with the security info for the bad client with a good
client and can not see any diffs.
I don't know what has happened. This client machine did work several weeks
ago. I suspect maybe some software installation munged the settings
somewhere but am at a loss as to where to check for this.
Any clues?
Thanks,
bruce
can you check the SQL error log for any information?
I have encountered intermittent memory problems of the form - could not
reserve contiguous memory blocks around the time I experienced these errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:N4fDd.37555$kq2.26384@.twister.nyc.rr.com...
> Hello All -
> I have a problem with push merge subscription. The publisher/distributer
> reside on same host with several clients connecting for merge replication.
> The startup of one of the merge agents fails. The error message "Login
> failed for user 'dbApp'. Reason: not associated with a trusted SQL server
> connection. Error 18452".
> I am able to connect to the client via QueryAnalyer using the 'dbApp'
login.
> I've done A/B checking with the security info for the bad client with a
good
> client and can not see any diffs.
> I don't know what has happened. This client machine did work several
weeks
> ago. I suspect maybe some software installation munged the settings
> somewhere but am at a loss as to where to check for this.
> Any clues?
> Thanks,
> bruce
>
>
I have a problem with push merge subscription. The publisher/distributer
reside on same host with several clients connecting for merge replication.
The startup of one of the merge agents fails. The error message "Login
failed for user 'dbApp'. Reason: not associated with a trusted SQL server
connection. Error 18452".
I am able to connect to the client via QueryAnalyer using the 'dbApp' login.
I've done A/B checking with the security info for the bad client with a good
client and can not see any diffs.
I don't know what has happened. This client machine did work several weeks
ago. I suspect maybe some software installation munged the settings
somewhere but am at a loss as to where to check for this.
Any clues?
Thanks,
bruce
can you check the SQL error log for any information?
I have encountered intermittent memory problems of the form - could not
reserve contiguous memory blocks around the time I experienced these errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:N4fDd.37555$kq2.26384@.twister.nyc.rr.com...
> Hello All -
> I have a problem with push merge subscription. The publisher/distributer
> reside on same host with several clients connecting for merge replication.
> The startup of one of the merge agents fails. The error message "Login
> failed for user 'dbApp'. Reason: not associated with a trusted SQL server
> connection. Error 18452".
> I am able to connect to the client via QueryAnalyer using the 'dbApp'
login.
> I've done A/B checking with the security info for the bad client with a
good
> client and can not see any diffs.
> I don't know what has happened. This client machine did work several
weeks
> ago. I suspect maybe some software installation munged the settings
> somewhere but am at a loss as to where to check for this.
> Any clues?
> Thanks,
> bruce
>
>
Merge Agent fails to connect to mdb
Hi all,
Here is a challenge - and I'm loosing ...
I'm trying to get a push subscription to work to an Access mdb file from
SQLServer.
I get the following message when running the Merge Agent:
'S:\network\share\access.mdb' is not a valid path. Make sure that the path
name is spelled correctly and that you are connected to the server on which
the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
I'm successfully using the subscription's Linked Server definition via QA.
So I conclude that the .mdb path is valid.
I've successfully test the push replication with the .mdb file as a local
file.
My guess is that it is a security context issue for the Merge Agent -
however I don't know how to correct it!!!
I've checked the Agent definition - and it is running with the owner set to
the local admin account.
Both my SQLServer Instance and Agent service use the same local
administrator account (not LocalSystem). This account has got access to the
share and can open the mdb file via Access).
Environment:
SQLServer PE V7.0 with SP3a
Windows 2003 Server with SP1
Hope that someone has been here, and got the t-shirt...
Peter
Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
everytime the agent runs it creates a new copy of the database,
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
> Hi all,
> Here is a challenge - and I'm loosing ...
> I'm trying to get a push subscription to work to an Access mdb file from
> SQLServer.
> I get the following message when running the Merge Agent:
> 'S:\network\share\access.mdb' is not a valid path. Make sure that the
> path
> name is spelled correctly and that you are connected to the server on
> which
> the file resides.
> (Source: MS.Jet.4.0 (Agent); Error number: -1023)
> I'm successfully using the subscription's Linked Server definition via QA.
> So I conclude that the .mdb path is valid.
> I've successfully test the push replication with the .mdb file as a local
> file.
> My guess is that it is a security context issue for the Merge Agent -
> however I don't know how to correct it!!!
> I've checked the Agent definition - and it is running with the owner set
> to
> the local admin account.
> Both my SQLServer Instance and Agent service use the same local
> administrator account (not LocalSystem). This account has got access to
> the
> share and can open the mdb file via Access).
> Environment:
> SQLServer PE V7.0 with SP3a
> Windows 2003 Server with SP1
> Hope that someone has been here, and got the t-shirt...
> --
> Peter
|||Hi Hilary,
Yes S is a mapped network drive.
I have created a new Linked Server def using the UNC reference to the mdb.
Again the linked server def works fine when used in Query Analyser to select
data from the mdb.
However, I again get the msg below in my Merge Agent when using the Linked
Server def in a push subscription:
'\\server\path1\path2\access.mdb' is not a valid path. Make sure that the
path name is spelled correctly and that you are connected to the server on
which the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
------
Any ideas?
Curious about your second comment about creating a new copy of the database
everytime the agent runs - I did not see this when running the agent against
my local test copy of the mdb.
Regards,
Peter
Peter
"Hilary Cotter" wrote:
> Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
> everytime the agent runs it creates a new copy of the database,
> --
> 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
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
>
>
Here is a challenge - and I'm loosing ...
I'm trying to get a push subscription to work to an Access mdb file from
SQLServer.
I get the following message when running the Merge Agent:
'S:\network\share\access.mdb' is not a valid path. Make sure that the path
name is spelled correctly and that you are connected to the server on which
the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
I'm successfully using the subscription's Linked Server definition via QA.
So I conclude that the .mdb path is valid.
I've successfully test the push replication with the .mdb file as a local
file.
My guess is that it is a security context issue for the Merge Agent -
however I don't know how to correct it!!!
I've checked the Agent definition - and it is running with the owner set to
the local admin account.
Both my SQLServer Instance and Agent service use the same local
administrator account (not LocalSystem). This account has got access to the
share and can open the mdb file via Access).
Environment:
SQLServer PE V7.0 with SP3a
Windows 2003 Server with SP1
Hope that someone has been here, and got the t-shirt...
Peter
Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
everytime the agent runs it creates a new copy of the database,
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
> Hi all,
> Here is a challenge - and I'm loosing ...
> I'm trying to get a push subscription to work to an Access mdb file from
> SQLServer.
> I get the following message when running the Merge Agent:
> 'S:\network\share\access.mdb' is not a valid path. Make sure that the
> path
> name is spelled correctly and that you are connected to the server on
> which
> the file resides.
> (Source: MS.Jet.4.0 (Agent); Error number: -1023)
> I'm successfully using the subscription's Linked Server definition via QA.
> So I conclude that the .mdb path is valid.
> I've successfully test the push replication with the .mdb file as a local
> file.
> My guess is that it is a security context issue for the Merge Agent -
> however I don't know how to correct it!!!
> I've checked the Agent definition - and it is running with the owner set
> to
> the local admin account.
> Both my SQLServer Instance and Agent service use the same local
> administrator account (not LocalSystem). This account has got access to
> the
> share and can open the mdb file via Access).
> Environment:
> SQLServer PE V7.0 with SP3a
> Windows 2003 Server with SP1
> Hope that someone has been here, and got the t-shirt...
> --
> Peter
|||Hi Hilary,
Yes S is a mapped network drive.
I have created a new Linked Server def using the UNC reference to the mdb.
Again the linked server def works fine when used in Query Analyser to select
data from the mdb.
However, I again get the msg below in my Merge Agent when using the Linked
Server def in a push subscription:
'\\server\path1\path2\access.mdb' is not a valid path. Make sure that the
path name is spelled correctly and that you are connected to the server on
which the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
------
Any ideas?
Curious about your second comment about creating a new copy of the database
everytime the agent runs - I did not see this when running the agent against
my local test copy of the mdb.
Regards,
Peter
Peter
"Hilary Cotter" wrote:
> Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
> everytime the agent runs it creates a new copy of the database,
> --
> 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
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
>
>
Merge Agent Error: invalid column 'rowguid'
I get this error when I Push New subscription and indicate NO, to initialize
schema and data.
the wizard dialog is: Initial Subscription, and I am choosing: No,
subscriber already has schema and data
Does the subscriber have an identical table ther already (including the guid
column)?
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No. I just have four tables in my publication and checked for that first.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23W53vgb%23EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Does the subscriber have an identical table ther already (including the
> guid
> column)?
> Regards,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Robert,
the table must already be there in a nosync initialization, in identical
format to the publisher one.
If you are saying it isn't there, then you'll need to use backup/restore or
DTS or some other means to transfer it.
Rgds,
Paul Ibison
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OoAgSmb%23EHA.2032@.tk2msftngp13.phx.gbl...
> No. I just have four tables in my publication and checked for that first.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23W53vgb%23EHA.2608@.TK2MSFTNGP10.phx.gbl...
>
|||Thank you Paul. I feel special today with all your attention.
I meant the column rowguid; I checked that it was not present as I assume
the PushNewSubscription process will create the column in the table at the
subscriber.
What I really am trying to accomplish, and forgive me, is how do I "turn on
replication" at the publisher-machine when my subscriber machine is already
in a working-production environment?
My thinking is that I start with a blank database at the publisher, schema
matching the subscriber, and enable publishing and push a new subscription
but NOT intializing schema and data. Because I'm guessing the blank
database at the publisher will destroy all records at the live subscriber,
making it blank also. Am I correct and what is the solution?
Thank you very, very much.
Bob
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23dAW%23ub%23EHA.2804@.TK2MSFTNGP15.phx.gbl.. .
> Robert,
> the table must already be there in a nosync initialization, in identical
> format to the publisher one.
> If you are saying it isn't there, then you'll need to use backup/restore
> or
> DTS or some other means to transfer it.
> Rgds,
> Paul Ibison
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:OoAgSmb%23EHA.2032@.tk2msftngp13.phx.gbl...
>
|||Robert,
please keep the questions coming - this is my great
hobby
The initialization process can do one of two things:
(a) if you choose a synch initialization, then the
complete table will be BCPd out into the repldata share.
In that share there'll be several textfiles - for
creating the schema, the indexes, one containing data,
but the initial text file contains simply a 'drop table'
statement. That means that it really doesn't matter what
is on the subscriber - there can be a table of the same
name or not. if there is, it'll be deleted anyway.
(b) you select to do a nosync initialization. In this
case, the exact ssame table (inc guid) must exist on the
subscriber. The initialization process will create
metadata tables on the subscriber, but it won't touch the
replicated table at all.
There are further variations if we consider other options
for @.pre_creation_cmd, where you can select to leave the
table there if it already exists and such like, but these
are for specialist scenarios.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
schema and data.
the wizard dialog is: Initial Subscription, and I am choosing: No,
subscriber already has schema and data
Does the subscriber have an identical table ther already (including the guid
column)?
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No. I just have four tables in my publication and checked for that first.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23W53vgb%23EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Does the subscriber have an identical table ther already (including the
> guid
> column)?
> Regards,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Robert,
the table must already be there in a nosync initialization, in identical
format to the publisher one.
If you are saying it isn't there, then you'll need to use backup/restore or
DTS or some other means to transfer it.
Rgds,
Paul Ibison
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OoAgSmb%23EHA.2032@.tk2msftngp13.phx.gbl...
> No. I just have four tables in my publication and checked for that first.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23W53vgb%23EHA.2608@.TK2MSFTNGP10.phx.gbl...
>
|||Thank you Paul. I feel special today with all your attention.
I meant the column rowguid; I checked that it was not present as I assume
the PushNewSubscription process will create the column in the table at the
subscriber.
What I really am trying to accomplish, and forgive me, is how do I "turn on
replication" at the publisher-machine when my subscriber machine is already
in a working-production environment?
My thinking is that I start with a blank database at the publisher, schema
matching the subscriber, and enable publishing and push a new subscription
but NOT intializing schema and data. Because I'm guessing the blank
database at the publisher will destroy all records at the live subscriber,
making it blank also. Am I correct and what is the solution?
Thank you very, very much.
Bob
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23dAW%23ub%23EHA.2804@.TK2MSFTNGP15.phx.gbl.. .
> Robert,
> the table must already be there in a nosync initialization, in identical
> format to the publisher one.
> If you are saying it isn't there, then you'll need to use backup/restore
> or
> DTS or some other means to transfer it.
> Rgds,
> Paul Ibison
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:OoAgSmb%23EHA.2032@.tk2msftngp13.phx.gbl...
>
|||Robert,
please keep the questions coming - this is my great
hobby
The initialization process can do one of two things:
(a) if you choose a synch initialization, then the
complete table will be BCPd out into the repldata share.
In that share there'll be several textfiles - for
creating the schema, the indexes, one containing data,
but the initial text file contains simply a 'drop table'
statement. That means that it really doesn't matter what
is on the subscriber - there can be a table of the same
name or not. if there is, it'll be deleted anyway.
(b) you select to do a nosync initialization. In this
case, the exact ssame table (inc guid) must exist on the
subscriber. The initialization process will create
metadata tables on the subscriber, but it won't touch the
replicated table at all.
There are further variations if we consider other options
for @.pre_creation_cmd, where you can select to leave the
table there if it already exists and such like, but these
are for specialist scenarios.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Friday, March 23, 2012
Merge 2005 - A row from this table will go to only one subscription
Hello everyone.
We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.
We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.
I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.
Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.
Any helps would be appreciated. Thanks.
BUMP!
Merge 2005 - A row from this table will go to only one subscription
Hello everyone.
We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.
We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.
I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.
Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.
Any helps would be appreciated. Thanks.
BUMP!
sql
Subscribe to:
Posts (Atom)