[dba-SQLServer] Tables in Other Databases

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
>
>




More information about the dba-SQLServer mailing list