Union Query Between SQL and Oracle

[DeeRuff1]

Hi There,
I have a problem. I am working in VB.NET and I am doing a call where I have to create a union query that will combine results from a SQL Server database and an Oracle database. These results are populated into a grid. The problem is I am not sure how to create two different connections and then use them to execute the union query and then fill the same grid. Here is an example of the query - the top half comes from a SQL Server database and the bottom half comes from the ORACLE database.

select
s.name, g.interfacecode
from
DSDB2.dbo.GenericHistory g,
DSDB2.dbo.sites s,
DSDB2.dbo.domains d
where
s.site_id = g.site_id
and d.domain_id = g.domain_id
and g.interfacecode = '11'

union

select
DCM.SITE_NAME as name,
DCM.INTERFACE_CODE as interfacecode
from
DC_MASTER DCM,
DC_T1 DCT
where
DCM.PARENT = DCT.GROUP_ID
and DCM.INTERFACE_CODE = '11'

 

[vongrunt]

With pure T-SQL: try OPENQUERY() to get Oracle part of query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DeeRuff1]

Thanks but we tried linked servers and it is way too slow for our solution. Can you think of another option?

 

[vongrunt]

How many rows returns each SELECT individually?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DeeRuff1]

There are not that many rows (100) but it is killing our system.... But is there a way I can merge 2 datasets together? Thanks so much for you help.

 

[vongrunt]

> But is there a way I can merge 2 datasets together?

From SQL, you can use UNION or FULL OUTER JOIN trick. But... both queries are too simple (and return too few rows) to run slowly. Perhaps problem is somewhere else - too slow auth/connection or something. Personally I'd check that first.

FYI, VB.NET has some client-side merging capabilities.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)