Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Joining tables in different databasesHi 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
Post Follow-up to this message
Re: Joining tables in different databasesTry 'four part names' :). server.database.owner.table MC "Captain Nemo" <nemo@nospam.com> wrote in message news:6oqBf.8267$wl.3901@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 > >
Post Follow-up to this message Re: Joining tables in different databasesUse 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@nospam.com> wrote in message news:6oqBf.8267$wl.3901@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
Post Follow-up to this message Re: Joining tables in different databasesThe 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@ma gcargo.vodatel.hr... Try 'four part names' :). server.database.owner.table MC "Captain Nemo" <nemo@nospam.com> wrote in message news:6oqBf.8267$wl.3901@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 > >
Post Follow-up to this message Re: Joining tables in different databases"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:LBqBf.4368$ft2.109590@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
Post Follow-up to this message Re: Joining tables in different databasesAgreed, but why not provide a more complete info since theres a little difference? He may need to pull data from two servers tomorrow... MC "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:LBqBf.4368$ft2.109590@news20.bellglobal.com... > 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@ma gcargo.vodatel.hr... > Try 'four part names' :). > server.database.owner.table > > > MC > > > "Captain Nemo" <nemo@nospam.com> wrote in message > news:6oqBf.8267$wl.3901@text.news.blueyonder.co.uk... > > >
Post Follow-up to this message Re: Joining tables in different databasesThere's often a performance difference. -- 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:dr5eu2$s5q$1@ma gcargo.vodatel.hr... Agreed, but why not provide a more complete info since theres a little difference? He may need to pull data from two servers tomorrow... MC "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:LBqBf.4368$ft2.109590@news20.bellglobal.com... > 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@ma gcargo.vodatel.hr... > Try 'four part names' :). > server.database.owner.table > > > MC > > > "Captain Nemo" <nemo@nospam.com> wrote in message > news:6oqBf.8267$wl.3901@text.news.blueyonder.co.uk... > > >
Post Follow-up to this message Re: Joining tables in different databasesDo you mean that actually specifing servername slows down the query? Could you explain why? MC "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:dRsBf.4433$ft2.115520@news20.bellglobal.com... > There's often a performance difference. > > -- > 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:dr5eu2$s5q$1@ma gcargo.vodatel.hr... > Agreed, but why not provide a more complete info since theres a little > difference? He may need to pull data from two servers tomorrow... > > > MC > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:LBqBf.4368$ft2.109590@news20.bellglobal.com... > > > >
Post Follow-up to this message Re: Joining tables in different databasesMC (marko_culo#@#yahoo# .#com#) writes: > Do you mean that actually specifing servername slows down the query? Could > you explain why? If the server name use is @@servername, SQL Server will shortcut, and there is no overhead. But if the linked server is defined a true loopback, so that there is a new connection made, there is obviously an overhead, as data is first passed to SQL Server, to the OLE DB provider on one connection, and then the OLE DB provider passes the data back to another connection. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pr...oads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodin...ions/books.mspx
Post Follow-up to this message Re: Joining tables in different databasesThank you for that, I assumed engine would 'optimize' that and never checked. Just when I think I actually know something ;)..... MC "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns9756F1F84A0F 4Yazorman@127.0.0.1... > MC (marko_culo#@#yahoo# .#com#) writes: > > If the server name use is @@servername, SQL Server will shortcut, and > there is no overhead. > > But if the linked server is defined a true loopback, so that there is a > new connection made, there is obviously an overhead, as data is first > passed to SQL Server, to the OLE DB provider on one connection, and then > the OLE DB provider passes the data back to another connection. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/pr...oads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodin...ions/books.mspx
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
