David Emerson
newsgrps at dalyn.co.nz
Mon Mar 28 15:57:31 CST 2005
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 > >