Joining tables from two databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble figuring out how I might do the following, if I can do it
at all. I have two databases, each with their own separate authentication
details.

I have a select statement that gets the data from one table based on
existence in another table in another database. This works fine when using
the windows authentication. What I don't understand how to do, is to connect
to both databases in a web project where it is using the webuser name and
password. Could someone steer me in the right direction on doing this.

I hope my explanation is somewhat clear.

Thanks
 
T McDonald,

I would like to know the following
1. Are these 2 databases are SQL Server ?
2. Are these 2 databases living in the same instance of SQL Server ?

You should not connect to these two databases using Web User Name and
Password. This is not the recommended approach.

Your data-access code (assembly) should be living in the
middle tier of the Server Farm. You should setup a SERVICE ACCOUNT to access
the data from these 2 databases. The SERVICE ACCOUNT should have enough
privileges on those 2 databases.
 
Both are SQL Server databases on the same server. This is an asp.net
application, each database has it's own accounts.

I'm not really following how you are solving it using a service account.

As I understand, to create a connection I need to use a connection string
that indicates the db and credentials I'm using. If I have more than one db,
how would I connect to both in order to run a select statement over both
databases.
 
If both databases are on the same server (and the web account has rights to
both) you can simply reference the database table using the syntax:

databasename.tablename.owner.column

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
There are two different web accounts. In this case I can't have the same web
account with rights to both tables. Would there be some way to perhaps read
data from both tables into the application as DataTables and then do the
select command against the results? Or some other way to use both web
accounts?

Thanks for the help
 
While ADO can't do a "JOIN" it can draw data from multiple sources (via
several connections) into DataTables and you can search those tables using a
number of built-in methods. I would experiment with a special account that
had the correct (joint) rights to do it on the server.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top