how to connect to multiple database?

Question

how to connect to multiple database?

cipcip

Old Hand

Points: 348
More actions
May 16, 2006 at 2:00 pm

#366199

Is it possible to connect to 3 database and execute a cross db query?
I usually use SqlConnection(connectionString) for a single connection

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dan Murray Right there with Babe Points: 779 More actions · Answer 1

There's not much information to go on. In the rare instances I've had to do this myself I found that the most efficient way is to create a view to the outside database tables needed in the database that hosts the main data.

First link the other server to the server that will host the query then use four part naming convention in the view ie,

Select data1,data2,data3

from

LinkedServer.Database.owner.table L, HostServerTable H

where

H.Value = L.Value

Good Hunting!

cipcip Old Hand Points: 348 More actions · Answer 2

I mean that i have 3 db sql server 2005 with the same table and the same rows,

i need to perform the same select in these db

So the question is:

can i connect to all 3 db and perform a unique SELECT or i have to connect separately, build 3 dataset and then merge them in a unique dataset and then sort it?

i want to do something like this:

connect db1 + db2 + db3

select row1 from table1

I want to avoid this:

connect db1

select row1 from table1

make dataset1

connect db2

select row1 from table1

make dataset2

connect db3

select row1 from table1

make dataset3

marge dataset1+dataset2+dataset3

sort dataset

robertk SSCommitted Points: 1608 More actions · Answer 3

if they're on the same server, that's easy. You just need to use the following naming convention:

select * from

database1.dbo.table1

select * from

database2.dbo.table1

You can even join the values together...

select *

from database1.dbo.table1 tab1

inner join database2.dbo.table1 tab2

on tab1.field = tab2.field

Ronzo Hall of Fame Points: 3917 More actions · Answer 4

Try a variation on Dan's response:

select x, y, z

from server1.db1.owner1.tableA

UNION

select x, y, z

from server2.db2.owner2.tableA

UNION

select x, y, z

from server3.db3.owner3.tableA

If you have linked the SQL Servers to each other, then connecting to one and executing the query above should return what you are looking for.

Have Fun!
Ronzo