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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-20 : 09:24:01
|
| Ross writes "Is there a way to use the INNER JOIN function to combine two tables that live in different databases? Is it possible to do this with ASP, given that ASP can only set a connection string to one database at a time?" |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2001-12-20 : 09:28:35
|
| You can INNER JOIN between two different databases, but this is going to have to happen in your SQL code in your ASP, so that the INNER JOIN executes on the server side. If your 2 databases are on the same server, then you can refer to the external database as DBName.dbo.tblName. If your external database resides on another server, you will have to link your servers and reference the external database like this: LinkedServerName.DBName.dbo.tblName*************************Just trying to get things done |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-20 : 14:48:45
|
| Note that to access the table in the other database your connection will need a user in that database with permission on the table.This applies even if you put the code in a stored procedure (which I would advise anyway).==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
need_some_help
Starting Member
5 Posts |
Posted - 2001-12-21 : 05:24:07
|
| SELECT T1.* FROM [database1]..Table1 T1 INNER JOIN [database2]..Table2 T2 ON T1.ID = T2.IDWHERE T2.Name = 'John'The full cross database select should be [database].[user].[table]But in my example I've not included the user. This defaultly uses the currently logged in user. I guess you could do this:SELECT T1.* FROM [database1].[user1].Table1 T1 INNER JOIN [database2].[user2].Table2 T2 ON T1.ID = T2.IDWHERE T2.Name = 'John'So the two databases have different users, and you declare which one you want to use through the statement. Don't know how you'd log in as that user though? Maybe you don't need to? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-21 : 12:04:08
|
quote:
So the two databases have different users, and you declare which one you want to use through the statement. Don't know how you'd log in as that user though? Maybe you don't need to?
OK, logins are not my area of expertise, but as I understand it, you don't LOGIN to a database, but rather you LOGIN to the Server and your login id may have permissions to different databases on that server. So if the two databases you're using are on the same server, it shouldn't be a problem. I don't know what happens when they are on different servers...-------------------It's a SQL thing... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-22 : 19:43:45
|
| True. You login to a server. That login maps to a user in a database which has permissions on objects. Permissions on tables will be granted to any stored procedures but only within that database - any calls to other databases will cause permissions to be checked again.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
JOINing tables in different databases