| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2007-04-24 : 04:12:29
|
| i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.is this possible? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-24 : 04:17:20
|
If the DB in the same server, you can just insert into db2.dbo.table( . . .) select . . . from db1.dbo.table where not exists . . . If it is on different server, setup linked serverinsert into db2.dbo.table( . . .) select . . . from [server1].db1.dbo.table where not exists . . .
KH |
 |
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-04-24 : 04:21:41
|
| Yes it is very much possible , you can do it with the help of Data Transfer system or Replication . |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-24 : 04:29:11
|
| What is the time delay allowed in this system for synchronization? Do you need real-time data insert/update? or you are going to periodically sync them?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-04-24 : 04:37:56
|
| what about permissions -- will i have to give both objects permissions of both users or if i do it in a stored procedure is it enough?am i better off just keeping the table in one db and using it from there? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-24 : 04:41:36
|
Or you can just create a cross-database view!Create View SomeViewasSelect * from Db1.dbo.TableWhere <some-condition> Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 04:45:41
|
| "am i better off just keeping the table in one db and using it from there?"Two thoughts:1) If the Maintenance Routines for the users can be restricted to only one database then remove the table from the other database. (Perhaps put a VIEW in the other database, replacing the original Table [View will have the same name] bu referencing the other database; and access to the User Table will continue to work as normal, via the View - but will need cross database chaining and/or Select Permissions on the other database)2) What is the issue with Backup and Disaster Recovery? If the user table is only in One database (lets call that MAIN), but you allow Maintenance from both databases (lets call the second one OTHER), there is a risk that a user added from the OTHER database, followed by a Crash and Restore of MAIN Database will then NOT have that data available, but it may already be referenced in other rows/tables in the OTHER Database?An alternative is to put Triggers on the User table, in both tables, which duplicate and local changes in the other database.Kristen |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-04-24 : 05:08:07
|
| thanks -- the view was a good idea and i think that will work well for me |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-04-24 : 05:09:22
|
| actaully not -- i did the view but when i try to access a qry that queries the view with another table i getThe server principal "admin" is not able to access the database "db1" under the current security context.do i have to give db1 the same login as db2 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 09:46:45
|
| Not something I know the answer to I'm afraid.Are both databases on the same server?If so I expect the user will need a database login on db2, and/or you may need to turn database chaining on [I don't know much about that either, but you might be able to find something out before someone turns up here with the correct answer!]Kristen |
|
|
|
|
synchronize 2 tables in 2 different databases