Hello,
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>
Showing posts with label subscription. Show all posts
Showing posts with label subscription. Show all posts
Thursday, March 29, 2012
Tuesday, March 20, 2012
Addign Column to a replicated Table
Hello,
Is there a way to add a column to a replicated table
without deleting the subscription and recreating it?
Thanks,
niv
Niv,
have a look at sp_repladdcolumn in BOL.
HTH,
Paul Ibison
|||Paul,
Am I correct to assume that I will have to run this stored
procedure on both the publisher and the subscriber.
niv
>--Original Message--
>Niv,
>have a look at sp_repladdcolumn in BOL.
>HTH,
>Paul Ibison
>
>.
>
|||Niv,
just the publisher will do and synchronization will propagate it down to the
subscriber, and then update the relevant objects.
Regards,
Paul Ibison
Is there a way to add a column to a replicated table
without deleting the subscription and recreating it?
Thanks,
niv
Niv,
have a look at sp_repladdcolumn in BOL.
HTH,
Paul Ibison
|||Paul,
Am I correct to assume that I will have to run this stored
procedure on both the publisher and the subscriber.
niv
>--Original Message--
>Niv,
>have a look at sp_repladdcolumn in BOL.
>HTH,
>Paul Ibison
>
>.
>
|||Niv,
just the publisher will do and synchronization will propagate it down to the
subscriber, and then update the relevant objects.
Regards,
Paul Ibison
Friday, February 24, 2012
Add Disclaimer Text to the RS report Subscription
I want to know , how to add Company Disclaimer text whenever Report is
Subscribed for Email. Either directly From Report Manager subscription / using
Create Subscription Web Methods. I don't want to put it as Report Footer.
Thanks!Hi,
It is not possible to put it in the email. Moreover you dont wont it in the
report as well. So no way, probabily you can think off at mail server end
again it will be difficult to identify each and every mail for the report. :-(
Just an idea. Its a very round about method and dont think it is advisable.
You need to write a small utility to pick up mails from the mailbox of the
server which should not have the gateway. If you dont give the gateway, what
happens is that the mails in .eml format will be at outbox at
inetpub\mailroot. your utility should pick these mails and send it through a
gateway. while sending it you may add the disclaimer.
I have not tried on adding something. But I have done this utility to pick
this mails and send it via other SMTP.
Amarnath
"RAGHAVAN JAYARAMAN" wrote:
> I want to know , how to add Company Disclaimer text whenever Report is
> Subscribed for Email. Either directly From Report Manager subscription / using
> Create Subscription Web Methods. I don't want to put it as Report Footer.
> Thanks!|||Thanks Amarnath.What if i want to add a disclaimer as Page Footer and want to
show it for only Report subscription (Email /MHTML ) or may be enable through
hidden parameter which sets to show & hide footer.
"Amarnath" wrote:
> Hi,
> It is not possible to put it in the email. Moreover you dont wont it in the
> report as well. So no way, probabily you can think off at mail server end
> again it will be difficult to identify each and every mail for the report. :-(
> Just an idea. Its a very round about method and dont think it is advisable.
> You need to write a small utility to pick up mails from the mailbox of the
> server which should not have the gateway. If you dont give the gateway, what
> happens is that the mails in .eml format will be at outbox at
> inetpub\mailroot. your utility should pick these mails and send it through a
> gateway. while sending it you may add the disclaimer.
> I have not tried on adding something. But I have done this utility to pick
> this mails and send it via other SMTP.
> Amarnath
>
> "RAGHAVAN JAYARAMAN" wrote:
> > I want to know , how to add Company Disclaimer text whenever Report is
> > Subscribed for Email. Either directly From Report Manager subscription / using
> > Create Subscription Web Methods. I don't want to put it as Report Footer.
> >
> > Thanks!|||Hi,
Thats the solution I thought first, but you had given that email should have
disclaimer and not on the report.
Again. You can do what you have mentioned but not in email. Other solution
also I thought of but didn't post it because it is something to do with
manipulating the report server tables.
It goes like this.
As you know that "catalog" table keeps report info and "subscription" has
subscription what you can do is to check any report has an entry in the
subscription table with ID from catalog. If exists then check for delivery
extension if it is email then enable the footer other wise no.
something like that . But haven;t tried.
Amarnath.
"RAGHAVAN JAYARAMAN" wrote:
> Thanks Amarnath.What if i want to add a disclaimer as Page Footer and want to
> show it for only Report subscription (Email /MHTML ) or may be enable through
> hidden parameter which sets to show & hide footer.
> "Amarnath" wrote:
> > Hi,
> >
> > It is not possible to put it in the email. Moreover you dont wont it in the
> > report as well. So no way, probabily you can think off at mail server end
> > again it will be difficult to identify each and every mail for the report. :-(
> >
> > Just an idea. Its a very round about method and dont think it is advisable.
> >
> > You need to write a small utility to pick up mails from the mailbox of the
> > server which should not have the gateway. If you dont give the gateway, what
> > happens is that the mails in .eml format will be at outbox at
> > inetpub\mailroot. your utility should pick these mails and send it through a
> > gateway. while sending it you may add the disclaimer.
> >
> > I have not tried on adding something. But I have done this utility to pick
> > this mails and send it via other SMTP.
> >
> > Amarnath
> >
> >
> > "RAGHAVAN JAYARAMAN" wrote:
> >
> > > I want to know , how to add Company Disclaimer text whenever Report is
> > > Subscribed for Email. Either directly From Report Manager subscription / using
> > > Create Subscription Web Methods. I don't want to put it as Report Footer.
> > >
> > > Thanks!
Subscribed for Email. Either directly From Report Manager subscription / using
Create Subscription Web Methods. I don't want to put it as Report Footer.
Thanks!Hi,
It is not possible to put it in the email. Moreover you dont wont it in the
report as well. So no way, probabily you can think off at mail server end
again it will be difficult to identify each and every mail for the report. :-(
Just an idea. Its a very round about method and dont think it is advisable.
You need to write a small utility to pick up mails from the mailbox of the
server which should not have the gateway. If you dont give the gateway, what
happens is that the mails in .eml format will be at outbox at
inetpub\mailroot. your utility should pick these mails and send it through a
gateway. while sending it you may add the disclaimer.
I have not tried on adding something. But I have done this utility to pick
this mails and send it via other SMTP.
Amarnath
"RAGHAVAN JAYARAMAN" wrote:
> I want to know , how to add Company Disclaimer text whenever Report is
> Subscribed for Email. Either directly From Report Manager subscription / using
> Create Subscription Web Methods. I don't want to put it as Report Footer.
> Thanks!|||Thanks Amarnath.What if i want to add a disclaimer as Page Footer and want to
show it for only Report subscription (Email /MHTML ) or may be enable through
hidden parameter which sets to show & hide footer.
"Amarnath" wrote:
> Hi,
> It is not possible to put it in the email. Moreover you dont wont it in the
> report as well. So no way, probabily you can think off at mail server end
> again it will be difficult to identify each and every mail for the report. :-(
> Just an idea. Its a very round about method and dont think it is advisable.
> You need to write a small utility to pick up mails from the mailbox of the
> server which should not have the gateway. If you dont give the gateway, what
> happens is that the mails in .eml format will be at outbox at
> inetpub\mailroot. your utility should pick these mails and send it through a
> gateway. while sending it you may add the disclaimer.
> I have not tried on adding something. But I have done this utility to pick
> this mails and send it via other SMTP.
> Amarnath
>
> "RAGHAVAN JAYARAMAN" wrote:
> > I want to know , how to add Company Disclaimer text whenever Report is
> > Subscribed for Email. Either directly From Report Manager subscription / using
> > Create Subscription Web Methods. I don't want to put it as Report Footer.
> >
> > Thanks!|||Hi,
Thats the solution I thought first, but you had given that email should have
disclaimer and not on the report.
Again. You can do what you have mentioned but not in email. Other solution
also I thought of but didn't post it because it is something to do with
manipulating the report server tables.
It goes like this.
As you know that "catalog" table keeps report info and "subscription" has
subscription what you can do is to check any report has an entry in the
subscription table with ID from catalog. If exists then check for delivery
extension if it is email then enable the footer other wise no.
something like that . But haven;t tried.
Amarnath.
"RAGHAVAN JAYARAMAN" wrote:
> Thanks Amarnath.What if i want to add a disclaimer as Page Footer and want to
> show it for only Report subscription (Email /MHTML ) or may be enable through
> hidden parameter which sets to show & hide footer.
> "Amarnath" wrote:
> > Hi,
> >
> > It is not possible to put it in the email. Moreover you dont wont it in the
> > report as well. So no way, probabily you can think off at mail server end
> > again it will be difficult to identify each and every mail for the report. :-(
> >
> > Just an idea. Its a very round about method and dont think it is advisable.
> >
> > You need to write a small utility to pick up mails from the mailbox of the
> > server which should not have the gateway. If you dont give the gateway, what
> > happens is that the mails in .eml format will be at outbox at
> > inetpub\mailroot. your utility should pick these mails and send it through a
> > gateway. while sending it you may add the disclaimer.
> >
> > I have not tried on adding something. But I have done this utility to pick
> > this mails and send it via other SMTP.
> >
> > Amarnath
> >
> >
> > "RAGHAVAN JAYARAMAN" wrote:
> >
> > > I want to know , how to add Company Disclaimer text whenever Report is
> > > Subscribed for Email. Either directly From Report Manager subscription / using
> > > Create Subscription Web Methods. I don't want to put it as Report Footer.
> > >
> > > Thanks!
add column to updateable subscription
sql2k sp3
Ive got a Publication with a Subscription that is
Immediate Updating with Queued for Failover. I tried to
run sp_repladdcolumn and got:
Server: Msg 21381, Level 16, State 1, Procedure
sp_repldropcolumn, Line 277
Cannot add (drop) column to table 'transdtl' because the
table belongs to publication(s) with an active updatable
subscription. Set @.force_reinit_subscription to 1 to force
reinitialization.
Server: Msg 21284, Level 16, State 1, Procedure
sp_repldropcolumn, Line 637
Failed to drop column 'test' from table 'transdtl'.
So how do you gurus add a column in this scenario? Do I
actually need to re snapshot the table? The only other
option I can think of would be:
Add them to the tables without sp_repladdcolumn. Add them
as an Article. Generate the Procs and Triggers for them.
Hmmm. That would suck.
TIA, Chris
I take it you are dropping a column. Your post is a little unclear to me.
do a sp_repdropcolumn with @.force_invaldiate_snapshot=1,
@.force_reinit_subscription=1
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:76ea01c47679$327475b0$a601280a@.phx.gbl...
> sql2k sp3
> Ive got a Publication with a Subscription that is
> Immediate Updating with Queued for Failover. I tried to
> run sp_repladdcolumn and got:
> Server: Msg 21381, Level 16, State 1, Procedure
> sp_repldropcolumn, Line 277
> Cannot add (drop) column to table 'transdtl' because the
> table belongs to publication(s) with an active updatable
> subscription. Set @.force_reinit_subscription to 1 to force
> reinitialization.
> Server: Msg 21284, Level 16, State 1, Procedure
> sp_repldropcolumn, Line 637
> Failed to drop column 'test' from table 'transdtl'.
>
> So how do you gurus add a column in this scenario? Do I
> actually need to re snapshot the table? The only other
> option I can think of would be:
> Add them to the tables without sp_repladdcolumn. Add them
> as an Article. Generate the Procs and Triggers for them.
> Hmmm. That would suck.
>
> TIA, Chris
Ive got a Publication with a Subscription that is
Immediate Updating with Queued for Failover. I tried to
run sp_repladdcolumn and got:
Server: Msg 21381, Level 16, State 1, Procedure
sp_repldropcolumn, Line 277
Cannot add (drop) column to table 'transdtl' because the
table belongs to publication(s) with an active updatable
subscription. Set @.force_reinit_subscription to 1 to force
reinitialization.
Server: Msg 21284, Level 16, State 1, Procedure
sp_repldropcolumn, Line 637
Failed to drop column 'test' from table 'transdtl'.
So how do you gurus add a column in this scenario? Do I
actually need to re snapshot the table? The only other
option I can think of would be:
Add them to the tables without sp_repladdcolumn. Add them
as an Article. Generate the Procs and Triggers for them.
Hmmm. That would suck.
TIA, Chris
I take it you are dropping a column. Your post is a little unclear to me.
do a sp_repdropcolumn with @.force_invaldiate_snapshot=1,
@.force_reinit_subscription=1
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:76ea01c47679$327475b0$a601280a@.phx.gbl...
> sql2k sp3
> Ive got a Publication with a Subscription that is
> Immediate Updating with Queued for Failover. I tried to
> run sp_repladdcolumn and got:
> Server: Msg 21381, Level 16, State 1, Procedure
> sp_repldropcolumn, Line 277
> Cannot add (drop) column to table 'transdtl' because the
> table belongs to publication(s) with an active updatable
> subscription. Set @.force_reinit_subscription to 1 to force
> reinitialization.
> Server: Msg 21284, Level 16, State 1, Procedure
> sp_repldropcolumn, Line 637
> Failed to drop column 'test' from table 'transdtl'.
>
> So how do you gurus add a column in this scenario? Do I
> actually need to re snapshot the table? The only other
> option I can think of would be:
> Add them to the tables without sp_repladdcolumn. Add them
> as an Article. Generate the Procs and Triggers for them.
> Hmmm. That would suck.
>
> TIA, Chris
Sunday, February 19, 2012
Add article to subscription
Transactional Replication - SQL 2000 - remote distributor
Have publication replicating 300 tables and I want to add 1 table.
sp_addarticle adds to publication, how do I add to each subscription (4)? Can
add schema to subscribers - new table so no rows exist anywhere.
How to add to subs in order to start replicating w/out re-synching each sub?
Thanks
Run the snapshot agent. This should generates a mini snapshot with only the
new article. The next time the distribution agent runs it should pick up the
new mini snapshot and distribute 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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Transactional Replication - SQL 2000 - remote distributor
> Have publication replicating 300 tables and I want to add 1 table.
> sp_addarticle adds to publication, how do I add to each subscription (4)?
Can
> add schema to subscribers - new table so no rows exist anywhere.
> How to add to subs in order to start replicating w/out re-synching each
sub?
> Thanks
|||And if my table has 5 million rows - am I forced to do a snapshot. Can I do
a similar alternative init like making sure new column is out at subscriber
and then telling SQL that the subscriber does not need initialization?
Thanks,
Chris
"Hilary Cotter" wrote:
> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute 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
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
|||Oh - and don't I have to tell SQL to add it to one or all subscription(s)?
Thanks Hillary,
Chris
"Hilary Cotter" wrote:
> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute 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
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
Have publication replicating 300 tables and I want to add 1 table.
sp_addarticle adds to publication, how do I add to each subscription (4)? Can
add schema to subscribers - new table so no rows exist anywhere.
How to add to subs in order to start replicating w/out re-synching each sub?
Thanks
Run the snapshot agent. This should generates a mini snapshot with only the
new article. The next time the distribution agent runs it should pick up the
new mini snapshot and distribute 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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Transactional Replication - SQL 2000 - remote distributor
> Have publication replicating 300 tables and I want to add 1 table.
> sp_addarticle adds to publication, how do I add to each subscription (4)?
Can
> add schema to subscribers - new table so no rows exist anywhere.
> How to add to subs in order to start replicating w/out re-synching each
sub?
> Thanks
|||And if my table has 5 million rows - am I forced to do a snapshot. Can I do
a similar alternative init like making sure new column is out at subscriber
and then telling SQL that the subscriber does not need initialization?
Thanks,
Chris
"Hilary Cotter" wrote:
> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute 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
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
|||Oh - and don't I have to tell SQL to add it to one or all subscription(s)?
Thanks Hillary,
Chris
"Hilary Cotter" wrote:
> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute 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
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
Add article to Merge Repl. Publication
I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
Your help is greatly appreciated.
GaryHi, don't add the table to both sides. Just add the table at the publisher and include it in the publication using the ARTICLES tab of Replication-Properties in EM. Start the Snapshot-Agent. The merge agent would apply only the new table to the subscriber. It's for the subscribers running SQL2000.|||Thanks for the prompt reply. The scripts will also modify some stored procedures - is that handled by the snapshot, or are the changes just replicated?|||Hi, the merge-agent applies the new tables to the subscriber.
About stored-procedures i am not sure, can u modify the stored-procedure if it's being published? Published tables can't be modified unless we use sp_repladdcolumn/sp_repldropcolumn to add or drop columns within the published tables. See if u can modify a published stored procedure at the publisher.
Howdy!
Your help is greatly appreciated.
GaryHi, don't add the table to both sides. Just add the table at the publisher and include it in the publication using the ARTICLES tab of Replication-Properties in EM. Start the Snapshot-Agent. The merge agent would apply only the new table to the subscriber. It's for the subscribers running SQL2000.|||Thanks for the prompt reply. The scripts will also modify some stored procedures - is that handled by the snapshot, or are the changes just replicated?|||Hi, the merge-agent applies the new tables to the subscriber.
About stored-procedures i am not sure, can u modify the stored-procedure if it's being published? Published tables can't be modified unless we use sp_repladdcolumn/sp_repldropcolumn to add or drop columns within the published tables. See if u can modify a published stored procedure at the publisher.
Howdy!
Subscribe to:
Posts (Atom)