More on merge replication an FK constraints

So, let me relate to my earlier post in this group about merge replication
and foreign keys.
As I've mentioned, I have one publication that pushes 'customers' table to
the subscribers.
I have another publication that pushes 'invoices' and 'invoices_det' tables
to the subscribers. In table invoices there is cust_id field wich is foreign
key to the customers table primary key 'cust_id'. I have created all
publications like so that first table is DROPed and then recreated. I have
'checked' options for creating primary keys and referential integrity.
FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
table is created on the subscriber, primary key is set, everything is ok.
Then I push INVOICES publication to the subscriber. That's not ok. I don't
have foreign key to customers table in the table invoices.
Enterprise manage wizzard suggests that I include customers table in
INVOICES publication to avoid things like mentioned above. But, I have
problem with that approach. That would mean that I'd have only as many
publications as I have subscribers (one subscriber - one publication). That
means that I'd have to have, for instance, table 'customers' published dozen
times, if I have dozen subscribers.
How do I solve this one?
As far as I see it, i could:
1) drop the foreign key constraints
- I'd hate to do this!
2) set up publication snapshot so it 'delete the data on the subscriber':
This seems ok, but! If I, for any reason, need to break replication,
when setting it back agan, I'll still have data on the subscriber. I then
push CUSTOMERS publication, the snapshot tries to DELETE data from
'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
up an error.
3) set up publication snapshot so it 'leaves the existing table unchanged'.
This is the best bet I could have, as far as I see. I only need to
make sure that the database on the remote is 'fresh and clear'. Wich means,
do the script to do the DDL on the subscriber (with all the constraints!),
and then push the snapshot in 'right' order (meaning, first the customers,
then the invoices, in the above example).
Any help here would be much appreciated.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
For foreign keys can you not set them all to "Not for replication", combine
CUSTOMERS, INVOICES and INVOICES_DET in one publication?
Jim.
"Mario Splivalo" wrote:

> So, let me relate to my earlier post in this group about merge replication
> and foreign keys.
> As I've mentioned, I have one publication that pushes 'customers' table to
> the subscribers.
> I have another publication that pushes 'invoices' and 'invoices_det' tables
> to the subscribers. In table invoices there is cust_id field wich is foreign
> key to the customers table primary key 'cust_id'. I have created all
> publications like so that first table is DROPed and then recreated. I have
> 'checked' options for creating primary keys and referential integrity.
> FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
> table is created on the subscriber, primary key is set, everything is ok.
> Then I push INVOICES publication to the subscriber. That's not ok. I don't
> have foreign key to customers table in the table invoices.
> Enterprise manage wizzard suggests that I include customers table in
> INVOICES publication to avoid things like mentioned above. But, I have
> problem with that approach. That would mean that I'd have only as many
> publications as I have subscribers (one subscriber - one publication). That
> means that I'd have to have, for instance, table 'customers' published dozen
> times, if I have dozen subscribers.
> How do I solve this one?
> As far as I see it, i could:
> 1) drop the foreign key constraints
> - I'd hate to do this!
> 2) set up publication snapshot so it 'delete the data on the subscriber':
> This seems ok, but! If I, for any reason, need to break replication,
> when setting it back agan, I'll still have data on the subscriber. I then
> push CUSTOMERS publication, the snapshot tries to DELETE data from
> 'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
> up an error.
> 3) set up publication snapshot so it 'leaves the existing table unchanged'.
> This is the best bet I could have, as far as I see. I only need to
> make sure that the database on the remote is 'fresh and clear'. Wich means,
> do the script to do the DDL on the subscriber (with all the constraints!),
> and then push the snapshot in 'right' order (meaning, first the customers,
> then the invoices, in the above example).
> Any help here would be much appreciated.
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> For foreign keys can you not set them all to "Not for replication", combine
> CUSTOMERS, INVOICES and INVOICES_DET in one publication?
I use 'NOT FOR REPLICATION' for foreign keys.
Combining all those into one publication now seems like a good thing. But, I
have dozen of publication of wich 8 are non-filtered publications. Customers
is an example of such publication. I have 30 stores around the country, and
I need to be able for all the stores to enter new customers, and that new
customer should be visible in all locations. Invoices, for instance, is
exmaple of filtered publication (i have 4 like those), only the cetnral
database (the publisher) needs to see all the invoices (for the reporting
purposes, and financial stuff). Other subscribers don't see other invoices.
Same goes for inventory lists, some other financial stuff I don't know
english names, and so on.
So, your approach leaves me with ONE publication for each subscriber! I
don't like that approach, it just creates mess. I'd like if someone could
tell me now 'stop, you're wrong here'.
My database well normalized (is that the term when you reference data trough
foreign keys?), for instance, in invoices I have payement methods (cash,
credit card,...) wich is FK to the table wich holds payement methods. That
table is updated ONLY at the publisher. Another example is stocks. Each
invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
Then tax rates. Then a lot of other things. All those tables are updated at
the publisher only, so I created snapshot replication to push that data to
subscribers. You suggest I put all that tables into merge publication, just
because they're referenced by FK from tables invoices and such? What is
wrong with my understanding of replication if I belive that is not the way?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||If you have a set of tables that rely on each other using foreign keys and
they are likely to be updated together then they should all be in the same
publication.
As I understand it, you have them separated and are getting foreign key
errors because items are not present when you need them.
You may have very good reasons for splitting them that I am not aware of but
I see no other solution to your problem.
Jim.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> If you have a set of tables that rely on each other using foreign keys and
> they are likely to be updated together then they should all be in the same
> publication.
Yes. That goes for master/detail tables. But, what if I have a table with,
let's say, tax rates, and in invocie table I have reference via foreign key
to the tax rates table? I'm publishing tax rates table (along with others)
via snapshot publication. So, why would I include those in merge
publication? And tables in merge publication still reference those fields in
tax rates table. So, when deploying initial snapshot the invoices table
WON'T have FK constraint to tax rates table. How do I deal with that?

> As I understand it, you have them separated and are getting foreign key
> errors because items are not present when you need them.
Initial snapshot won't create foreign key constraints for tables that are
not in that publication. That is my problem.

> You may have very good reasons for splitting them that I am not aware of but
> I see no other solution to your problem.
Do you have any reference on the web where I could read more about this? I
see no point in puting all the database tables in one publication just
because almost all tables are FKey with each other. Or, to put it more
simple, how do I establish snapshot and merge publication so that tables in
merge publication have foreign keys to tables in snapshot publication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Yes, what you are doing is logical ok - I just don't know of a way to prevent
FK errors when you split things like that.
For me, even if the table rarely changes, I include everything in a
publication. The only price I pay for that is a larger initial snapshot.
The benefit I get is simplicity and easier administration.
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> Yes, what you are doing is logical ok - I just don't know of a way to prevent
> FK errors when you split things like that.
> For me, even if the table rarely changes, I include everything in a
> publication. The only price I pay for that is a larger initial snapshot.
> The benefit I get is simplicity and easier administration.
So, you'd generaly be ok with this: put rarely changed tables as articles
into many publications, although you could have only one snapshot
publication for those?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||I try to keep it one publication, one snapshot. I then have many subscribers
- it is very easy to administer.
"Mario Splivalo" wrote:

> On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> So, you'd generaly be ok with this: put rarely changed tables as articles
> into many publications, although you could have only one snapshot
> publication for those?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-08, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I try to keep it one publication, one snapshot. I then have many subscribers
> - it is very easy to administer.
>
Thnx. I'll see with what i'll come up at the end, and post here...
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Mario,
I'm looking for the same answer as you, I have basically the same problem
with inicial snapshot with transactional replication, as you, I have also
Unchecked the enforce for replication in the relationship's dialog for the
foreign keys.
If I have some answers or more info I will post it here...
Adrian Parra.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>

More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.
Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?have you consider building your databases from source code. This will find a
ll breakages.
check out www.dbghost.com|||kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google
.com>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?
Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.
You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.
Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.
Simon|||I thought of that only moments after posting and that's exactly what I
did in fact do.
I found out about DBGhost in the process though so it was still worth
asking
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Indeed. Just when I thought I'd nailed all dependencies I found some
implicit dependencies in EXEC statements run within some stored
procedures. They won't be picked up either.
"sp_depends" ain't all that. There's "sp_MSdependencies" which goes
beyond first level dependencies.
http://www.microsoft.com/sql/techin...splayingdepende
ncies.asp
Unit tests would be ideal but there are just way too many units and I
almost certainly wouldn't be able to get the go-ahead.
Cheers
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.

Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google.com>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?

Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.

You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.

Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.

Simon

More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.
Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?
have you consider building your databases from source code. This will find all breakages.
check out www.dbghost.com
|||kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google.c om>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?
Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.
You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.
Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.
Simon
|||I thought of that only moments after posting and that's exactly what I
did in fact do.
I found out about DBGhost in the process though so it was still worth
asking
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indeed. Just when I thought I'd nailed all dependencies I found some
implicit dependencies in EXEC statements run within some stored
procedures. They won't be picked up either.
"sp_depends" ain't all that. There's "sp_MSdependencies" which goes
beyond first level dependencies.
http://www.microsoft.com/sql/techinf...playingdepende
ncies.asp
Unit tests would be ideal but there are just way too many units and I
almost certainly wouldn't be able to get the go-ahead.
Cheers
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.
Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google.com>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?
Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.
You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.
Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.
Simon

More efficient than LEFT JOIN

I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. I'm looking for an
efficient way to select only the active data.

Currently I use:

SELECT ...
FROM DataTable AS D
LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
WHERE D.Key IS NULL

However I am not convinced that this is the most efficient, or the most
intuitive method of acheiving this.

Can anyone suggest a more efficient way of getting this information
please.

Many thanks.

*** Sent via Developersdex http://www.developersdex.com ***Hi, Brian

I think that you wanted to write "WHERE I.Key IS NULL" (instead of
"WHERE D.Key IS NULL"). In this case, you can use something like this:

SELECT ...
FROM DataTable WHERE Key NOT IN (
SELECT Key FROM InactiveTable
)

Razvan

PS. I assume that the "Key" column does not allow NULL-s.|||On Wed, 15 Feb 2006 12:41:09 GMT, Brian Wotherspoon wrote:

>
>I have a table with data that is refreshed regularly but I still need to
>store the old data. I have created a seperate table with a foreign key
>to the table and the date on which it was replaced. I'm looking for an
>efficient way to select only the active data.
>Currently I use:
>SELECT ...
>FROM DataTable AS D
>LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
>WHERE D.Key IS NULL
>However I am not convinced that this is the most efficient, or the most
>intuitive method of acheiving this.
>Can anyone suggest a more efficient way of getting this information
>please.
>Many thanks.

Hi Brian,

The most intuitive way, IMO, is

SELECT ...
FROM DataTable AS D
WHERE NOT EXISTS
(SELECT *
FROM InActiveTable AS I
WHERE I.Key = D.Key)

The most efficient is either the above or your own LEFT OUTER JOIN query
(but do change D.Key to I.Key in the IS NOT NULL check!!) - but it'll be
only efficient if the I.Key column is indexed.

--
Hugo Kornelis, SQL Server MVP|||try to bring it up a level.

for instance, you are probably creating a temp table? Perhaps create
the temp table with closer to the data you really need.
if you are looking for only one cusotmer, then only pull that one
customer. or, for a specfiic time period, then only that time period.

also, make sure you have an index on inactivetable.key.

if you knew that inactivetable started at some timeframe for all
records, then you could create a composite index on
inactivedata.timestamp plus key.

would it be worth putting an "inactive data datestamp" at the customer
level? perhaps if you have enough rows.