multi table multi database join?

[gabriel]

i'd like to execute a query that joins tables from
seperate databases. i think i could pull this off by
first running a query to each database into seperate
datatables, then adding joins to the tables, but this
seems like it may require stupidly huge memory as in some
cases i'm running a query on tables that store the medical
records for entire countys.
is there a better way with ado.net?

(i'm using obdc.net to access sybase ase 12.5 and sql 2000
thru preconfigured dsns)
thanks in advance
g

 

[Patrice]

Are they on the same serve r? Which server ?

Also it's quite unusual to retrieve this. Don't you have some kind of filter
? What do you do with all those records ?

Patrice

 

[gabriel]

do you always answer questions with questions? (löl, i
appreciate the help)
dont i have some kind of filter? a filter for what? i dont
understand your question.

Are they on the same server? no.., yes.., sometimes.
sybase runns on one machine mssql on another, what would
it change if they were runningon the same machine (aside
from degrading their performance while they are each under
load). we have several counties with this setup, each has
its own particular, ..mmm... particulars.

what will i do with all those records?, i dont think i'm
talking about a large set of records in output maybe 10 to
100 (that doesnt seem like much), i dunno what i'd do with
them. skip around them by the campfire at first, no
seriously, they'll be used in various billing, appointment
scheduling, medical treatment trending routines.

the setup is county wide health information. maybe 200
clinics per county, each clinic has its data in its own
(sybase) db, then the finiancial parts of the individual
sybase dbs are combined into a mssql db for each county.
(the other parts of the individual sybase dbs arent laid
out to be combined) some reports are run from the
aggregated db. more reports would be possible if some of
the data from the aggregate db could be used in a query to
a specific (sybase) db.

but this is just one example.,
cant you add tables from multiple sources into an MSaccess
project, then run a query that joins fields from each of
the those tables even if the tables are in databases on
seperate machines? (honest question, i dunno), this is
basically my question, is that possible in ado.net?

if its possible how would it be done without first pulling
over each of the tables into a datatable and then linking
the tables?

 

[gabriel]

i think what i'm describing can also be accomplished with
mssql's report services, but can it be done with ado.net
and c#/vb.net?

 

[Guest]

gabriel

The microsoft Jet engine supports joins across databases.

Search google for "heterogeneous join jet"

You should be able to find an example for ADO. I assume this would work with
the .Net oledb components too.

(From what I rememeber, the table names in the query are substituted with
ado connection strings..)

Mike.

 

[gabriel]

mike, appricate the reply, but i dont seem to find any
examples in ado or ado.net of heterogeneous joins,
(heterogeneous join ado, heterogeneous join ado.net,
heterogeneous join c#, heterogeneous join vb.net - maybe i
just dont use google as well as i think)
in fact outside of a couple of third party vendors google
seems to be telling me to go pound sand with my thoughts
of heterogeneous joins, and the general impression i'm
getting is that heterogeneous joins are reason for
(larger) third party query engines.
looks more and more like i'm going to have to pull data
from one (mayeb both) dbs into another and run the query
inside one db. seems like a more complex, network heavy
solution, but unless someone else has an idea

if you read this any even thought about replying,
thank you

 

[Patrice]

What I had in mind is :
- on a single server you can do a join between several tables in distinct
databases without any problem
- you could also do this using a "linked server" allowing to perform joins
between the target server and the linked server
- finally you could do get the data from each server seperately and
establish the relation in ADO.NET

You mentionned also you would require a huge amount of memory. Do you need
all those data at once ? Generally we attempt to load in the datatable the
minimal amount of data currently needed ie.e instead of loading all the
records for the whole county, the query should likely retrieve results based
on some kind of user criteria (the user is unlikely to browse all records, I
suppose he pick a record based on some ID or search criteria).

Hope it gives some ideas...

Patrice

--

"gabriel" <[email protected]> a écrit dans le message de
do you always answer questions with questions? (löl, i
appreciate the help)
dont i have some kind of filter? a filter for what? i dont
understand your question.

Are they on the same server? no.., yes.., sometimes.
sybase runns on one machine mssql on another, what would
it change if they were runningon the same machine (aside
from degrading their performance while they are each under
load). we have several counties with this setup, each has
its own particular, ..mmm... particulars.

what will i do with all those records?, i dont think i'm
talking about a large set of records in output maybe 10 to
100 (that doesnt seem like much), i dunno what i'd do with
them. skip around them by the campfire at first, no
seriously, they'll be used in various billing, appointment
scheduling, medical treatment trending routines.

the setup is county wide health information. maybe 200
clinics per county, each clinic has its data in its own
(sybase) db, then the finiancial parts of the individual
sybase dbs are combined into a mssql db for each county.
(the other parts of the individual sybase dbs arent laid
out to be combined) some reports are run from the
aggregated db. more reports would be possible if some of
the data from the aggregate db could be used in a query to
a specific (sybase) db.

but this is just one example.,
cant you add tables from multiple sources into an MSaccess
project, then run a query that joins fields from each of
the those tables even if the tables are in databases on
seperate machines? (honest question, i dunno), this is
basically my question, is that possible in ado.net?

if its possible how would it be done without first pulling
over each of the tables into a datatable and then linking
the tables?

 

[Guest]

...found this example for you...

SELECT * FROM OpenRowSet
('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\ramsql7',
'SELECT *
FROM [sheet1$]')
as a

I found this in thread
[http://groups.google.co.uk/[email protected]&rnum=1]

Try editing the connection string details to suit your own database then add
your join statements.

 

[Chris Priede]

mike, appricate the reply, but i dont seem to find any
examples in ado or ado.net of heterogeneous joins

This is a SQL Server feature and doesn't have much to do with ADO.NET
specifically. The following should explain the concept, how to set up the
link, and how to run queries on it (including joins with SQL Server's own
databases):

http://msdn.microsoft.com/msdnmag/issues/0800/cutting/

http://msdn.microsoft.com/library/en-us/tsqlref/ts_oa-oz_5xix.asp

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp


Hope that helps,