David Emerson
newsgrps at dalyn.co.nz
Mon Mar 28 16:54:30 CST 2005
Thanks again Arthur, Triggers is certainly one area I will look into. BOL seems to indicate that my situation is exactly one of their purposes. I am not sure that linked servers will help. Because the sprocs in question will be a mixture of local tables and the common ones I will need to keep separate copies of the sprocs in each database (no big deal because I can use extended referencing to include the database name and for the tables with different data I can leave the database name out so that the local table will be used). Replication is not required because we only want one set of tables - we don't need them replicated in two places. David At 29/03/2005, you wrote: >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 >> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >