Batch Queries in a Pass-Through

[Guest]

Is it possible to batch multiple queries into one pass-through query?

One table is local the other on sql server

ie
Select a.id from table a inner join table b on a.id = b.id where a.timestamp
<> b.timestamp
select a.id from table a inner join table b on a.id = b.id where a.id is null
select a.id from table a inner join table b on a.id = b.id where b.id is null

to get all record ids on the server which are new, deleted, changed in one
returned query.

anyone have the syntax if it is possible?
On SQL server I can use a ; to run to queries at once, no so thru a pass
through.

Thanks,

-David

 

[John Spencer]

Is this really a pass-through query or are you just using a link to the
database on the SQL server? I'm not sure you can use a local table in a
passthrough query. I think you can if you have things set up correctly on
the SQL server side, but your posted syntax doesn't appear to do so.

You might want to consider using a UNION query to get all the information in
one query. Also, your 2nd and 3rd queries are not going to work with Inner
Joins if you are trying to detect new and deleted records.

Select a.id from table a inner join table b on a.id = b.id where a.timestamp
<> b.timestamp
UNION ALL
select B.id from table B LEFTjoin table A on B.id = A.id where a.id is null
UNION ALL
select a.id from table a LEFT join table b on a.id = b.id where b.id is null

 

[Guest]

Is this really a pass-through query or are you just using a link to the
database on the SQL server? I'm not sure you can use a local table in a
passthrough query. I think you can if you have things set up correctly on
the SQL server side, but your posted syntax doesn't appear to do so.

Yeah I did ask the question incorrectly... I am running a pass through
query, then comparing the recordset from that with the recordset from the
local table. So essentially comparing two local tables... Good catch thanks.

You might want to consider using a UNION query to get all the information in
one query. Also, your 2nd and 3rd queries are not going to work with Inner
Joins if you are trying to detect new and deleted records.

doh! I always forget about UNION queries, thanks and yeah I meant left join
and wrote inner join. I blame my wife shooing me out the door

Thanks,

-David