Multiple SQL Databases

[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

 

[Ignacio Machin \( .NET/ C# MVP \)]

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,

 

[Gary]

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

 

[Wim Hollebrandse]

As long as your 'initial catalog' database server contains the linked server entry - which can be configured with pre-defined login credentials, then you're fine.

Cheers,
Wim Hollebrandse
http://www.wimdows.net
http://www.wimdows.com

 

[JKJ]

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

contains the linked server entry - which can be
configured with pre-defined login credentials, then
you're fine.

 

[Ignacio Machin \( .NET/ C# MVP \)]

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,