Removing rows on subscribers, but not on publishers

Hello.
I'm looking for a way to remove data from a merge replicated database
that would be filtered out if it was synchronized for the first time
today. In addition, I don't want the new rows to be deleted from the
publisher's database.
Let me explain. We have several tables that look similar to the
following:
TableA:
Id uniqueidentifier
Value varchar(50)
TimeStamp datetime
We have a merge replication publication with filters that specify that
data with a TimeStamp older than 14 days should not be synchronized,
similar to the following:
exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
@.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
@.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
- 14 )', @.join_unique_key = 0
What ends up happening is that new rows with a recent TimeStamp is
synchronized to the client as desired. If the client syncs again after
14 days, however, this now "stale" data is still present on the client
-- it does not become deleted.
If we manually try to remove the stale data by running a query such as
"DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
deleted. But the next time the database is synchronized, the data is
also deleted from the publishing database. This is not acceptable in
our application.
Some of our clients use SQL CE on Pocket PC's with limitted storage
capacity and thus will not be able to continue to store this old data.
Any ideas on how to delete the old data from the SQL CE subscriber's
databases without having the side effect of removing the data from the
publishing database?
Thanks in advance for your time.
- Eli Tucker
I think what you want to do is to manipulate your agents so that they change
to download only (-ExchangeType download) when you delete rows on your
subscriber, and then both (-ExchangeType both) under normal operations.
This will only work if the deletes occur in isolation. For instance if the
only activity occuring on your subscriber was the delete, this would work.
If there were deletes and updates and inserts occuring on your subcriber
with the ExchangeType of download the inserts and deletes would not make it.
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105046696.732620.46610@.f14g2000cwb.googlegro ups.com...
> Hello.
> I'm looking for a way to remove data from a merge replicated database
> that would be filtered out if it was synchronized for the first time
> today. In addition, I don't want the new rows to be deleted from the
> publisher's database.
> Let me explain. We have several tables that look similar to the
> following:
> TableA:
> Id uniqueidentifier
> Value varchar(50)
> TimeStamp datetime
> We have a merge replication publication with filters that specify that
> data with a TimeStamp older than 14 days should not be synchronized,
> similar to the following:
> exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
> @.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
> @.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
> - 14 )', @.join_unique_key = 0
> What ends up happening is that new rows with a recent TimeStamp is
> synchronized to the client as desired. If the client syncs again after
> 14 days, however, this now "stale" data is still present on the client
> -- it does not become deleted.
> If we manually try to remove the stale data by running a query such as
> "DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
> deleted. But the next time the database is synchronized, the data is
> also deleted from the publishing database. This is not acceptable in
> our application.
> Some of our clients use SQL CE on Pocket PC's with limitted storage
> capacity and thus will not be able to continue to store this old data.
>
> Any ideas on how to delete the old data from the SQL CE subscriber's
> databases without having the side effect of removing the data from the
> publishing database?
> Thanks in advance for your time.
> - Eli Tucker
>
|||Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
ideal for us since the client application both adds and updates new
rows between syncs.
Does anyone have any other ideas of how to do this?
|||Eli,
Instead of synchronizing from your subscriber code, if you *reinitialize*
the subscription
with the option to upload your latest changes before refreshing the
subscription tables,
your subscriptions will always contain only the 14 days of data your
publication filter dictates.
Take a look at the boolean value that ReinitializeSubscription takes...
rep.ReinitializeSubscription(True)
-- Darren Shaffer
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
> Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
> ideal for us since the client application both adds and updates new
> rows between syncs.
> Does anyone have any other ideas of how to do this?
>
|||Thanks, Darren. I haven't looked at the ReinitializeSubscription
option before -- it very well could work for us.
Is it basically equivalent to the following?
1. Synchronizing upload only
2. Deleting the SDF file
3. Performing an initial sync
Or is less data passed when using the ReinitializeSubscription(true)
method?
Thanks again!
- Eli
Darren Shaffer wrote:
> Eli,
> Instead of synchronizing from your subscriber code, if you
*reinitialize*
> the subscription
> with the option to upload your latest changes before refreshing the
> subscription tables,
> your subscriptions will always contain only the 14 days of data your
> publication filter dictates.
> Take a look at the boolean value that ReinitializeSubscription
takes...[vbcol=seagreen]
> rep.ReinitializeSubscription(True)
> -- Darren Shaffer
>
> "Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
> news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
isn't[vbcol=seagreen]

removing rows from a replicated table

Hi
I have a subscription which has a table and some rows which I cannot
delete - they don't have a rowguid value set for them - when I try and
delete I get a message about 'cannot insert a null value into column 'guid'.
How do I tidy tihss up ?
thanks
Is this a merge subscription? Or Immediate Updating?
It sounds like the triggers are not executing correctly.
The best way for you to solve this would be to reinitialize and regenerate
and distribute your snapshot.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:%23yyb9VNaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a subscription which has a table and some rows which I cannot
> delete - they don't have a rowguid value set for them - when I try and
> delete I get a message about 'cannot insert a null value into column
'guid'.
> How do I tidy tihss up ?
> thanks
>
|||Bruce,
presumably you are using merge, and the insert failure is the insertion of a
row into MSmerge_tombstone.
I don't know how these rows have managed to be entered without having a GUID
as this is a default value. Either they were entered with an explicit null
or they were already there.
To remove them, it depends on if they exist on other servers or not.
The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername then
remove the rows then reenable the trigger. Obviously you must ensure there
are no other changes going on while you do this type of manipulation.
HTH,
Paul Ibison
|||Great idea but it says I can't alter the table 'cause it is being published
for replication....
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etvoroNaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> presumably you are using merge, and the insert failure is the insertion of
a
> row into MSmerge_tombstone.
> I don't know how these rows have managed to be entered without having a
GUID
> as this is a default value. Either they were entered with an explicit null
> or they were already there.
> To remove them, it depends on if they exist on other servers or not.
> The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername
then
> remove the rows then reenable the trigger. Obviously you must ensure there
> are no other changes going on while you do this type of manipulation.
> HTH,
> Paul Ibison
>
|||I altered the trigger so it didn't do anything, deleted the rows and altered
the trigger back to how it was. Thanks for the tip...
Bruce
|||Ah...then try editing the trigger to comment out the part that inserts into
the MSmerge_tombstone table. Can't test it here so please post back if there
are any problems.
Regards,
Paul Ibison

Removing replicated data on a remote device without deleting it on the server

In the replication scenario I am dealing with, a mobile device (PDA)
uses SQL Server Mobile to synchronize data with a SQL Server database
located on some server. The mobile user can enter new data on the PDA.
Data replicated from the server to the PDA cannot be modified.
However, after a long period of time, it is possible that the amount
of new data entered on the PDA could be potentially large.
Is it possible with replication that once new data is entered into the
PDA and then gets replicated back to the server, that it can be
removed from the PDA but without this removal causing the data to be
deleted on the server?
Thanks for your help
Polaris
You could try disabling the triggers before deleting the records. However
this means that modifications to these rows on the publisher will fail when
being applied to the subscriber. I'd archive the data on the publisher then
delete it on the publisher so the delete flows to the subscriber. If you
want to see the data on the publisher you could use a view which amalgamates
the data there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I think you need to use a technology like RDA for this.
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
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172129049.331836.154340@.a75g2000cwd.googlegr oups.com...
> In the replication scenario I am dealing with, a mobile device (PDA)
> uses SQL Server Mobile to synchronize data with a SQL Server database
> located on some server. The mobile user can enter new data on the PDA.
> Data replicated from the server to the PDA cannot be modified.
> However, after a long period of time, it is possible that the amount
> of new data entered on the PDA could be potentially large.
> Is it possible with replication that once new data is entered into the
> PDA and then gets replicated back to the server, that it can be
> removed from the PDA but without this removal causing the data to be
> deleted on the server?
> Thanks for your help
> Polaris
>

Removing msrepl_trans columns from db

Hello,
Is there a way to remove the msrepl_tran columns from
tables that have been marked for replication?
I've a backup taken from a replicated system that I want
to remove all the replicated tables and columns from. I
can go through and delete the msrepl_tran columns and
constraint from the tables manually but it is very time
consuming to do 100 tables.
Can any one help?
Thanks in advance.
Enterprise Manager is your friend. Right click on your tables and remove
away.
You might also try to run a script like this.
select 'alter table '+ object_name(parent_obj) +' drop constraint '+name+
char(13)+' go'
From sysobjects where name like '%msrepl%' and type ='D' and
object_name(parent_obj)
<>'MSreplication_subscriptions'
GO
select 'alter table '+object_name(ID) +' drop column msrepl_tran_version '+
char(13) +'GO' from syscolumns where name like 'msrepl_tran_version%' and
object_name(ID) not like 'conflict%'
GO
Copy what appears in the results pane and then paste it in the execution
window and run it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Harvey" <anonymous@.discussions.microsoft.com> wrote in message
news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
> Hello,
> Is there a way to remove the msrepl_tran columns from
> tables that have been marked for replication?
> I've a backup taken from a replicated system that I want
> to remove all the replicated tables and columns from. I
> can go through and delete the msrepl_tran columns and
> constraint from the tables manually but it is very time
> consuming to do 100 tables.
> Can any one help?
> Thanks in advance.
|||Thanks for the replys. I have over 100 tables and
dropping one at a time would take way to long.
I'll use the cursor method and see how I get on. Thanks
again

>--Original Message--
>Enterprise Manager is your friend. Right click on your
tables and remove
>away.
>You might also try to run a script like this.
>select 'alter table '+ object_name(parent_obj) +' drop
constraint '+name+
>char(13)+' go'
>From sysobjects where name like '%msrepl%' and type ='D'
and
>object_name(parent_obj)
><>'MSreplication_subscriptions'
>GO
>select 'alter table '+object_name(ID) +' drop column
msrepl_tran_version '+
>char(13) +'GO' from syscolumns where name
like 'msrepl_tran_version%' and
>object_name(ID) not like 'conflict%'
>GO
>Copy what appears in the results pane and then paste it
in the execution
>window and run it.
>
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>Now available for purchase at:
>http://www.nwsu.com/0974973602.html
>"Harvey" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
want[vbcol=seagreen]
I
>
>.
>

Removing clustered index on Primary key.

Hi,
I want to remove clustering of the Primary Key and make a foreign key in the
table the clustered index. Can I do this with a replicated database? I
suppose I hav to do this then on the Publisher and Subscriber database. Does
anybody has any experience with this?
TIA,
Stefan
You have to:
1. exclude this table from publication
2. do the required manipulations of table schema on publisher and
subscribers
3. add table back to publication.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:uA3bA6LZEHA.728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to remove clustering of the Primary Key and make a foreign key in
the
> table the clustered index. Can I do this with a replicated database? I
> suppose I hav to do this then on the Publisher and Subscriber database.
Does
> anybody has any experience with this?
> TIA,
> Stefan
>
|||Stefan,
when I tested this, I found it was possible in merge but not in
transactional replication. Using merge, you should be able to do it directly
on the publisher and using sp_addscriptexec on the subscribers. For
transactional, you'll need to drop then recreate the publication after
making the changes.
HTH,
Paul Ibison
|||I was able to do it by dropping the publication modifying the table and then
replicating it.
Not sure if this is what you want or not.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBMjuFQZEHA.3112@.tk2msftngp13.phx.gbl...
> Stefan,
> when I tested this, I found it was possible in merge but not in
> transactional replication. Using merge, you should be able to do it
directly
> on the publisher and using sp_addscriptexec on the subscribers. For
> transactional, you'll need to drop then recreate the publication after
> making the changes.
> HTH,
> Paul Ibison
>

Removing and adding articles by code

Hello there
On my live replication, I need, sometimes, to change schema of replicated
tables.
Because, as long as the table is replicated, i can't change the schema, I
simple do:
1. Remove the article from the parent table
2. change the schema of the current table
3. add it again to the schema
I would like to do this with code.
Is there a way to do this?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)