By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

Joining tables in different databases

P: n/a
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
Jan 24 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
MC
Try 'four part names' :).
server.database.owner.table
MC
"Captain Nemo" <ne**@nospam.com> wrote in message
news:6o****************@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

Jan 24 '06 #2

P: n/a
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" <ne**@nospam.com> wrote in message
news:6o****************@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

Jan 24 '06 #3

P: n/a
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:dr**********@magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table
MC
"Captain Nemo" <ne**@nospam.com> wrote in message
news:6o****************@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


Jan 24 '06 #4

P: n/a
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:LB*******************@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
Jan 24 '06 #5

P: n/a
MC
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" <to*@dont.spam.me.cips.ca> wrote in message
news:LB*******************@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:dr**********@magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table
MC
"Captain Nemo" <ne**@nospam.com> wrote in message
news:6o****************@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


Jan 24 '06 #6

P: n/a
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:dr**********@magcargo.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" <to*@dont.spam.me.cips.ca> wrote in message
news:LB*******************@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:dr**********@magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table
MC
"Captain Nemo" <ne**@nospam.com> wrote in message
news:6o****************@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



Jan 24 '06 #7

P: n/a
MC
Do you mean that actually specifing servername slows down the query? Could
you explain why?

MC

"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:dR*******************@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:dr**********@magcargo.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" <to*@dont.spam.me.cips.ca> wrote in message
news:LB*******************@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:dr**********@magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table
MC
"Captain Nemo" <ne**@nospam.com> wrote in message
news:6o****************@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



Jan 25 '06 #8

P: n/a
MC (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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 25 '06 #9

P: n/a
MC
Thank you for that, I assumed engine would 'optimize' that and never
checked. Just when I think I actually know something ;).....

MC
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
MC (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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 26 '06 #10

P: n/a
MC (marko_culo#@#yahoo#.#com#) writes:
Thank you for that, I assumed engine would 'optimize' that and never
checked. Just when I think I actually know something ;).....


As I mentioned, it does optimize when the server is @@servername, which
it can recognize. But it does not analyse connection strings.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.