Joining tables in different databases
in [Using MS SQL Server]
|
From: Captain Nemo on 24 Jan 2006 08:58 Hi I'm working on an ASP project where the clients want to be able to effectively perform SELECT queries joining tables from two different databases (located on the same SQL-Server). Does this involve creating virtual tables that link to another database, or am I completely on the wrong track? Any hints as to where I might find more information (buzz-words, etc.) would be most appreciated. Thanks
From: MC on 24 Jan 2006 09:20 Try 'four part names' :). server.database.owner.table MC "Captain Nemo" <nemo(a)nospam.com> wrote in message news:6oqBf.8267$wl.3901(a)text.news.blueyonder.co.uk... > Hi > > I'm working on an ASP project where the clients want to be able to > effectively perform SELECT queries joining tables from two different > databases (located on the same SQL-Server). > > Does this involve creating virtual tables that link to another database, > or > am I completely on the wrong track? > > Any hints as to where I might find more information (buzz-words, etc.) > would > be most appreciated. > > Thanks > >
From: Tom Moreau on 24 Jan 2006 09:03 Use 3-part naming: select * from dbo.MyTable l join OtherDB.dbo.OtherTable o on o.PK = l.PK -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com "Captain Nemo" <nemo(a)nospam.com> wrote in message news:6oqBf.8267$wl.3901(a)text.news.blueyonder.co.uk... Hi I'm working on an ASP project where the clients want to be able to effectively perform SELECT queries joining tables from two different databases (located on the same SQL-Server). Does this involve creating virtual tables that link to another database, or am I completely on the wrong track? Any hints as to where I might find more information (buzz-words, etc.) would be most appreciated. Thanks
From: Tom Moreau on 24 Jan 2006 09:11 The OP said the two DB's were on the same server. Therefore, 3-part naming is sufficient. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com "MC" <marko_culo#@#yahoo#.#com#> wrote in message news:dr5cc8$bue$1(a)magcargo.vodatel.hr... Try 'four part names' :). server.database.owner.table MC "Captain Nemo" <nemo(a)nospam.com> wrote in message news:6oqBf.8267$wl.3901(a)text.news.blueyonder.co.uk... > Hi > > I'm working on an ASP project where the clients want to be able to > effectively perform SELECT queries joining tables from two different > databases (located on the same SQL-Server). > > Does this involve creating virtual tables that link to another database, > or > am I completely on the wrong track? > > Any hints as to where I might find more information (buzz-words, etc.) > would > be most appreciated. > > Thanks > >
From: Captain Nemo on 24 Jan 2006 09:46
"Tom Moreau" <tom(a)dont.spam.me.cips.ca> wrote in message news:LBqBf.4368$ft2.109590(a)news20.bellglobal.com... > The OP said the two DB's were on the same server. Therefore, 3-part naming > is sufficient. > > -- > Tom It sure is! I've just tried it out. Where I went wrong was thinking that 2-part naming would do it (omitting the 'dbo'). Thanks, Tom |