Greg,
It was 6 or 7 seven years ago, on a single IBM Mainframe using DB2 as the database. I was doing other work at the time and was not involved with the fine details so I don't have all the answers.
What I do remember was that data integrity was a problem. It was a financial services company and we had to keep detailed transaction data (i.e. application data) for years and years so the issue of data integrity (i.e. delete corporate data such as names and addresses) never really came up. On the few occaisions that it did, I think that each application area would provide the equivalent of a stored procedure to say whether they used that piece of corporate data or not - if they did then the company could not delete the corporate data. As I said, when I was there we didn't do this very often!!
Within SQL Server, I regulary execute stored procedures in other databases and ones on other servers:
- @db_name.@db_owner.@sp_name @parm1 etc
- @linked_server.@db_name.@db_owner.@sp_name @parm1 etc
It's a nice principle but without having gone through the problems I cannot offer any more advice.
Jeremy