Arthur Fuller
artful at rogers.com
Mon Mar 28 15:31:58 CST 2005
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 > >