Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PITU
Starting Member
4 Posts |
 Posted - 2009-10-16 : 18:56:35
|
| Hello everyone,I am new to SQL Server but not to SQL. I have done some searching and have looked at the FAQ but can't find the answer I'm looking for. I'll try to explain it as clearly as possible. Thanks in advance for looking.I have two MS Access databases:X:\User.mdb X:\Department.mdbThese are separate databases stored in the same folder/server. User.mdb contains 2 tables, [user] and [department]. [user] is a local table to X:\User.mdb and [department] is a linked table that points to the [department] table located inside X:\Department.mdb.Do you know where I'm going with this yet? So now it's time to upgrade to SQL Server and for the life of me, I can't figure out how to model this relationship using SQL Server 2008. I have created the User and Department databases on the same SQL Server - they just need to be linked.I am aware of loads of examples of how to us sp_addlinkedserver to create a pointer to an external data source. But I need to link 2 databases that are located on the same instance of SQL Server. My question is, then, how do I do this?I am also aware of the ability of SQL Server 2008 to do loopback connections, but this still does not sound like the solution.I believe I am overlooking something very simple. I know I can query any database/table on the same instance by just specifically crafting the query using the correct server.database.table convention, and this is fine and useful. But what I want is to be able to define and save a table relationship that references the foreign key of a table located in another database on the same SQL Server instance.The main purpose of why I am doing this is so that I don't have to duplicate tables. Without knowing much at all about triggers, I assume I could probably set up a trigger that when one table is changed, it mirrors to another table, and I could therefore have duplicate tables and still have some database integrity, but this seems like a kludgy way to go about it. Also, to my knowledge, table relationships only work with other tables, right? Otherwise, I could simply create a view and link the user table to a view of the department table.Without going further, can someone point me in the right direction?Thanks |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-17 : 06:15:57
|
quote:
But what I want is to be able to define and save a table relationship that references the foreign key of a table located in another database on the same SQL Server instance.
Foreign keys can only establish a link between two tables in the same database in SQL Server.Do you really need two databases in SQL Server? Why?I'd suggest you create one database in SQL Server that stores the tables from both your MS Access databases. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-10-17 : 10:33:28
|
| I would have to agree with YellowBug - use a single database. Lookup SCHEMA in Books Online and see if you can setup separate schemas for each Access databases objects. Or, if the tables are not the same in both databases - you can just move the tables into a single database.FWIW - accessing data in another database is simple in SQL Server. All you have to do is use three-part naming to identify the object. For example:SELECT {columns}FROM db1.dbo.Users uJOIN db2.dbo.Department d ON d.Key = u.KeyWHERE {filter}But, as you have already found - setting up DRI is not possible between separate databases unless you use triggers. You could set this up with triggers, but that is going to take a lot more work. |
|
|
|
PITU
Starting Member
4 Posts |
Posted - 2009-10-19 : 10:26:05
|
| Right now there is nothing constraining me from combining tables into one database, so that's what I'll do. Thanks for the replies. |
|
|
|
|
|
|
|
|
How to link Server1.DB1.Tbl1 to Server1.DB2.Tbl2?