Francisco Tapia
fhtapia at gmail.com
Mon Mar 28 17:10:00 CST 2005
thinking outloud, it might be useful to have 3 dbs... db1 = company contact lists 1, db2 company2 contact lists 2. and db 3 would have all the comminallities between both in addition you can store all the sprocs/views that need to reach accross the dbs here. I'm more concerned in what you'll need to do for security tho for accross db owner chaining etc. often when you run a course like this you have to create views to tables and join the new user roles to that, that way users can still run the sprocs that touch other dbs. see database owner chaining in BOL. On Tue, 29 Mar 2005 10:54:30 +1200, David Emerson <newsgrps at dalyn.co.nz> wrote: > 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 > > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://pcthis.blogspot.com | PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More...