sql server replication: Table relationship problem -- sql server replication

You're in the

sql server replication

group:

Table relationship problem

Re: Table relationship problem

Paul Ibison
7/20/2005 12:00:00 AM

sql server replication: JC,

I'm wondering about other integrity issues here: if a record on the
publisher is deleted and there are different relationships to the article on
the subscriber, wouldn't this cause an error on the subscriber? Or are the
relationships identical to the FK tables and these tables are also
replicated?

Triggers are OK and quick, but I wouldn't use them to maintain archives
based on the production database (if this is the case). If the data change
on the subscriber fails, an error is raised on the publisher's data change,
which you probably won't want. Also, I wouldn't hurt publisher performance
this way. As well as covering these issues, replication also gives you some
degree of control as to when the data is replicated.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Table relationship problem

JC
7/20/2005 2:34:05 AM

I've searched the forum as well as the books I have but haven't found an
answer to this.

I have a table that needs to be replicated to other databases on the same
server.

The subscribing databases has tables that needs to keep relationships to
certain data in the replicated table.
That is, there are columns in the replicated table thats being referenced as
foreign keys by other tables at the subscribers.

This as you all know does not work. The subscriber cannot drop the table and
replace it when it needs to update.

The real problem is that I need these relationships.

Re: Table relationship problem

JC
7/20/2005 5:41:02 AM

Thanks.
But what do you think of that solution compared to a trigger just updating
the table at the (subscriber). IYO is one better than the other?

[quoted text, click to view]

Re: Table relationship problem

JC
7/20/2005 7:01:05 AM

Relationships are not identical or even similar. But it works to use triggers.

And yes you are right, there is a problem of cascading errors from bottom up
using triggers. That's what made me want another solution.

I will consider your approach of scripting. It seems to me that this is more
for heavy workloads of replication and the trigger approach is more for small
scale replication.

Thanks for your help and input.
// JC

[quoted text, click to view]

Re: Table relationship problem

Paul Ibison
7/20/2005 12:23:36 PM

JC,
you can use pre and post-snapshot scripts to drop then recreate the FK
relationships as necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.

View Other Months
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn