Deleting Rows from Publisher and from Subscriber

Hello
I have an issue on Deleting rows on Publisher.
I want to keep a 2 months old transaction data both on my Publisher
and on the Subscribers as well,
so eatch night a process is run on the publisher that deletes 2 months
old data, so actually eatch night
I have a deletion of 1 days data (the 61st day).
Now the problem is that the next day, in the morning synchronisation,
eatch of my subscriber gets about 100.000 or even more deletions and
that makes synchronization slow.Although i have filters on my
replication,somehow deletions are send to all subscribers!!!
Now I am looking for solutions!!!
One thought of mine is to delete 2 months old transactions from eatch
subscriber, but because some subscribers share data then I belive that
if a deletion occured in a subscriber then when it synchronises the
deletion is passed to the publisher and then this deletion again is
going to be transfered to other subscribers when they try to
syncronise after.Am I right? So the issue will still be an issue.
Any Ideas of how to delete records in my case?
Please help!
You could replicate the execution of a stored procedure which does the
delete, and this way only the stored proc will be executed on the subscriber.
Rgds,
Paul Ibison
|||Thanks for the quick answer ... can u please be more specific.
How can I do this?
And if the deletion ocurs on the sunscriber wont that be transfer to
the publisher and then to other subscribers
that share data?
Thanks
|||In sp_addarticle there is the option to use @.type = 'proc exec' and this
option also exists on the article properties when using the gui to replicate
a stored procedure using transactional replication. Note that this applies
only when running the stored proc on the publisher so you'd have to remove
the rows in this direction only.
Rgds,
Paul Ibison
|||I am using Merge Replication does this applies to Merge Replication as
well ?
|||No - the replication of the execution of stored procedures is a transactional
attribute.
Rgds,
Paul Ibison
|||Any Ideas about Merge Replication ?
Because I am using merge replication and my system is live, I cannot
change to other type of replication,
i am searching for a solution, something i can implement fast.
Thanks
|||There's nothing obvious because what you need is some way of bypassing the
normal functioning of merge. i suppose you could disable the merge triggers
then send down a delete command using sp_addscriptexec to remove the rows
then reenable the triggers. However then you'll still have the replication
metadata hanging around for these rows until it gets cleaned up. Also if
there are any concurrent updates to these rows then there'll be problems
because the merge agent will be looking for missing rows when it
synchrionizes.
There might be some way of modifying a column on the rows so they fall
outside of a filter you place on the article and are therefore not
replicated. Then you could manually remove them on the publisher and via a
script on the subscriber - this is what I'd investigate.
Rgds,
Paul Ibison
"savvaschr@.nodalsoft.com.cy" wrote:

> Any Ideas about Merge Replication ?
> Because I am using merge replication and my system is live, I cannot
> change to other type of replication,
> i am searching for a solution, something i can implement fast.
> Thanks
>
|||Thanks for your time and Answers,
Finaly I found the answer.
You can specify at the publisher the @.delete_tracking variable of
sp_addmergearticle to 'false'
By doing this the Publisher deletes records and does not send them for
synchronisation.
So in my case, I delete 2 months old rows from my transactions table
at the Publisher, those deletions
are not send in the Synchronisation, and then I delete individually 2
month old transactions from my subscribers.
In this way my synchronisation is faster because Subscribers dont have
to process 100.000 deletions or more
every morning that they Synchronise.
Regards
Savvas

Deleting duplicate entries in nightmare-table

Hello
I was given the task to "filter away" duplicate rows in a 10 million row
table with about 30 columns :S And the table has no primary key, no
constrains, nothing, and I need to find a way to clear that mess up, in a
way... sigh
You guys must think, alright, this is easy, just group by, well well, that
would be too easy, since each row isnt really "unique", lets continue the
mess....
Lets say the table contains 5 columns, A B C D E
In the new table there shall be a CHECK(A,B,C), that combination is unique.
But in the old table there are duplicates of that, and the values of D and E
may be any value on each row. (Headache yet?) Ppl who create these kinds of
heaptables should be lined up and shot :(
Lemme post some test DLL:
CREATE TABLE #Test (
A int,
B int,
C int,
D int,
E int
)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
SELECT * FROM #Test
/*
DESIRED RESULT
A B C D E
1,1,1,9,8
1,2,1,1,4
3,2,1,1,4
*/
DROP TABLE #Test
And everything is so screwed up it doesnt "matter" which values D and E has
(or the other 20 columns) as long as they had the value it had before. And
all columns are like varchar(x) in the table, so keep that in mind :S> And everything is so screwed up it doesnt "matter" which values D and E
> has
> (or the other 20 columns) as long as they had the value it had before.
This doesn't make sense to me, which value did it have before?
Anyway, I'll try, can you do this:
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
?
There is no ANY() function, so you either need to choose an aggregate, or
maybe you could use a correlated subquery with ORDER BY CHECKSUM(NEWID())
but I'm not clear that would work, nor without better requirements am I
inclined to try.
A|||Lasse Edsvik wrote:
> Hello
> I was given the task to "filter away" duplicate rows in a 10 million row
> table with about 30 columns :S And the table has no primary key, no
> constrains, nothing, and I need to find a way to clear that mess up, in a
> way... sigh
> You guys must think, alright, this is easy, just group by, well well, that
> would be too easy, since each row isnt really "unique", lets continue the
> mess....
> Lets say the table contains 5 columns, A B C D E
> In the new table there shall be a CHECK(A,B,C), that combination is unique
.
> But in the old table there are duplicates of that, and the values of D and
E
> may be any value on each row. (Headache yet?) Ppl who create these kinds o
f
> heaptables should be lined up and shot :(
> Lemme post some test DLL:
>
> CREATE TABLE #Test (
> A int,
> B int,
> C int,
> D int,
> E int
> )
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
>
> SELECT * FROM #Test
> /*
> DESIRED RESULT
>
> A B C D E
> 1,1,1,9,8
> 1,2,1,1,4
> 3,2,1,1,4
> */
> DROP TABLE #Test
>
> And everything is so screwed up it doesnt "matter" which values D and E ha
s
> (or the other 20 columns) as long as they had the value it had before. And
> all columns are like varchar(x) in the table, so keep that in mind :S
Always tell us what version of SQL Server you are using.
In SQL Server 2005:
WITH T (row_num) AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c ORDER BY a,b,c,d,e)
FROM #Test)
DELETE FROM T
WHERE row_num > 1;
Google for "delete duplicates" and you'll find lots of other solutions
in the archives of this group.
Test it out and make sure you have a current backup first :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
sry, forgot that :) I'm using SQL 2000
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147360308.771189.249980@.i40g2000cwc.googlegroups.com...
> Lasse Edsvik wrote:
a
that
the
unique.
and E
of
has
And
> Always tell us what version of SQL Server you are using.
> In SQL Server 2005:
> WITH T (row_num) AS
> (SELECT ROW_NUMBER() OVER
> (PARTITION BY a,b,c ORDER BY a,b,c,d,e)
> FROM #Test)
> DELETE FROM T
> WHERE row_num > 1;
> Google for "delete duplicates" and you'll find lots of other solutions
> in the archives of this group.
> Test it out and make sure you have a current backup first :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi Aaron,
consider raw data like this:
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,8)
then the query
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
will produce a row
1,1,1,9,8
which is not present in the original data. Does it make sence?|||alter table #test add f timestamp
go
select * from #test
go
select A,B,C,D,E from #test where not exists(select 1 from #test t1
where t1.a=#test.a
and t1.b=#test.b
and t1.c=#test.c
and t1.f>#test.f)
A B C D E
-- -- -- -- --
1 1 1 -1 6
1 2 1 1 4
3 2 1 3 4
(3 row(s) affected)|||> which is not present in the original data. Does it make sence?
I don't know, I don't think the requirements were specific enough to make
you right or to make me wrong. I was just offering one possible solution.

DELETE statement conflicted

Hello

I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.

However, when I tried to delete a row in the first table I saw this error:

DELETE FROM [dbo].[Names_DB]
WHERE [LName_Name]=N'andrews'

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.

I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.

Please advise.

I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.

Thanks

Regards

Lynn

Have you enabled the Cascade Delete Related Records for all the relations of the maintable and related tables etc? I think you have missed it somewhere.|||

Hello Fredrik

Thanks for the swift reply.

No I have not enabled the Cascade Delete Related Record I didn't know this was required, I am still a novice. Yes I have certainly missed this.

Now that I understand that this is required, I need to learn how to do this. Can you please direct me to a suitable turorial or perhaps explain how this is carried out and what is the required code for this process?

Thanks

Lynn

|||

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

|||

Fredrik N:

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

Hello Fredrik

Within table properties where do I enable the delete to the existing tables. Can I add update also?

Thanks

Lynn

|||

Hi

You can add constrains to a table it could be something like this:

CREATE TABLE Books ( BookIDINTNOT NULLPRIMARY KEY, AuthorIDINTNOT NULL, BookNameVARCHAR(100)NOT NULL, PriceMONEYNOT NULL)GOCREATE TABLE Authors ( AuthorIDINTNOT NULLPRIMARY KEY,Name VARCHAR(100)NOT NULL)GOALTER TABLE BooksADD CONSTRAINT fk_authorFOREIGN KEY (AuthorID)REFERENCES Authors (AuthorID)

ON DELETE CASCADE

ON UPDATE CASCADEGOYou can take a look atDatabase Objects: Constraints for more

Hope this helps.|||

Hi Thanks for the post and the info.

As I have already created tables I am a little wary about altering tables in case I loose the data.

The tables I have already have foreign keys and primary keys.

I have a parent table called: DomNames

Primary Key = DomNamesID

I have recently inserted a foreign key:

Foreign Key = CatA_ID (taken from the Catagory A child table)

Then I have child category tables from A - Z

In category table A

Primary Key = CatA-ID

Foreign Key = DomNamesID (taken from the DomNames table)

In category table B

Primary Key = CatB-ID

Foreign Key = CatA_ID (taken from the previous A category table)

Each following table uses the Category ID alphabetical letter as a primary key and the previous tables Category ID as the foreign key, each table has a DomNameID column.

All tables contain data.

Do I miss out the first section of the code you mentioned and just put this:

ALTER TABLE DomNames
ADD CONSTRAINT fk_CatA_ID
FOREIGN KEY (CatAID)
REFERENCES DomNames (DomNamesID)

ON DELETE CASCADE

ON UPDATE CASCADE

I would be grateful if you would confirm before I alter my database.

Thanks

Regards

Lynn

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?
Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the servers
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>
|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the serve
rs
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>