Arthur Fuller
artful at rogers.com
Mon Mar 28 16:27:57 CST 2005
I think you can do this with triggers. I haven't actually tried it but I see no apparent reason why triggers wouldn't work. You need to reference the second db in your trigger of course. You could also link the servers, but I think that for your app as described that is not necessary. (To check this out, search for Linked Servers in BOL.) Linked Servers might be your solution. You might also look at the docs on Replicaton in BOL. A. David Emerson wrote: > Thanks Arthur, > > That might come in useful. However, most of the data is the same. > Both databases are on the same server - the company has two sub > companies that have separate clients and need to be kept physically > separate for company structure reasons, but many of the tables have > identical information (for example supplier details and costs). What I > am in the process of doing is changing the sprocs etc so that both > databases use the same data from the main database (the second > database will then not need the tables). > > Q1. Is there any way to ensure referential integrity is maintained > between the two databases? If not, is the only solution to prevent > deletions so that there is no risk of orphaned records in the second > database? > > Q2. Are there any other things I should be aware of with this > arrangement? > > David > > At 29/03/2005, Arthur Fuller wrote: > >> I recently purchased software from Red-Gate Software, including SQL >> Compare, SQL Data Compare, and DTS Compare. Perhaps you were looking >> for a free solution. I have tried to write same several times, with >> some success but not bulletproof and also problematic. In the end I >> opted for Red-Gate's solutions. >> >> As it happens, I have three remote clients to whom I connect directly >> using EM (i.e. I set up a new server, enter the IP -- which requires >> that they set me up as a Windows user with appropriate permissions), >> then I load and go. Red-Gate's software works superbly. I can connect >> to Vancouver or Kingston or two sites in Toronto and compare the >> structs and data with my local development copy. Typically, the case >> is that I have made changes locally, while they have entered data >> into their version of the db. Red-Gate handles both situations >> magnificently. >> >> I have no relationship with Red-Gate except as a satisfied customer. >> Compared to the hours I might have spent rolling my own version of >> their stuff, the few hundred dollars and the instant download and >> install were well worth the money. >> >> To cite just one among many cool features, when you do a SQL Compare >> it shows you the tables, sprocs and views that differ, in two >> windows. It's like grep, showing you that two sprocs are identical >> except for these three lines in db A as compared with db B. You can >> update in one direction or both. >> >> Splendid software. I don't say that often. >> >> Arthur >> >> David Emerson wrote: >> >>> Access XP ADE, SQL2000. >>> >>> I have two databases that are identical in table structure. Some of >>> the tables also have identical data in them. I am in the process of >>> changing the sprocs so that the identical data tables are only >>> included in one database and both databases use these (so that the >>> data only needs to be updated in one place). Updating of data in >>> these identical tables needs to be able to be done from either front >>> end. >>> >>> I understand that to reference the tables in the first database from >>> the second one all I need to do is prefix the table name with the >>> database name (eg database1.dbo.table). >>> >>> Q1. Is there any way to ensure referential integrity is maintained >>> between the two databases? If not, is the only solution to prevent >>> deletions so that there is no risk of orphaned records in the second >>> database? >>> >>> Q2. Are there any other things I should be aware of with this >>> arrangement? >>> >>> _______________________________________________ >>> dba-SQLServer mailing list >>> dba-SQLServer at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>> http://www.databaseadvisors.com >>> >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >