Multiple SQL Databases

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I'm working on an app where I need to access a table from 2 different
databases (same MS-SQL box).

What I need is to have a SELECT string with an INNER JOIN based on these 2
tables. If they were in the same DB this would be no problem, but I can't
figure out how to get this via C#.

Any help would be greatly appreciated, thx!

-Gary
 
Hi Gary,

This is a 100% SQL question :)


This is how you can access a table from another DB in the same DB-server, if
the BDs are in different servers then you need to create a link server.

select * from OTHER_DB_NAME.owner.Table_Name

where owner is the owner of the table, by default dbo

Cheers,
 
But doesn't the connection string have the "initial catalog" set to one DB?
Then how would that select call be able to access the second DB?

-Gary
 
Linked server isn't necessary in this particular case -
he states that the databases are hosted by same SQL
Server instance on the same machine (at least that is how
I understood the question). Fully qualifying the table
names should suffice.

(ansi92 sql)
SELECT [some stuff]
FROM [database].[owner].
a,
[database].[owner].
b
WHERE
a.[field] = b.[field]

Even if the databases are on two distinct machines,
linked servers aren't always necessary. Simply use the
OPENROWSET or OPENDATASOURCE function where you'd use a
linked server. But I would agree that if you are joining
these tables all the time, or executing distributed
transactions, linking the servers is probably the best
approach.

Overall, I agree with Ignacio, though, this has nothing
to do with C# ;-) Hope we helped anyway.

JKJ, MCSD.NET
-----Original Message-----
As long as your 'initial catalog' database server
Click to expand...
contains the linked server entry - which can be
configured with pre-defined login credentials, then
you're fine.
 
Hi Gary,

Yes, of course you have an initial catalog, but it does not imply you
cannot access others DBs in the same server ( or in others servers using
linking ).

Anyway try it and see if it works

Cheers,
 
Back
Top