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.o wner.Table_Name

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

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gary" <ga******@NOOOO SPAM.virtium.co m> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[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
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us > wrote
in message news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

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.o wner.Table_Name

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

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gary" <ga******@NOOOO SPAM.virtium.co m> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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

[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

---
Posted using Wimdows.net Newsgroups - http://www.wimdows.net/newsgroups/

[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].[table] a,
[database].[owner].[table] 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.
Cheers,
Wim Hollebrandse
http://www.wimdows.net
http://www.wimdows.com

---
Posted using Wimdows.net Newsgroups - http://www.wimdows.net/newsgroups/.

[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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gary" <ga******@NOOOO SPAM.virtium.co m> wrote in message
news:eh******** *****@TK2MSFTNG P10.phx.gbl...

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
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us > wrote in message news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

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.o wner.Table_Name

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

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gary" <ga******@NOOOO SPAM.virtium.co m> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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