My first DTS package? Server linking?

Maury Markowitz

I have been struggling with a number of performance problems that stem from
using an MDB database to keep two SQL databases "in sync". One of these runs
on SQL Server, the other on PervasiveSQL. We do extensive queries between the
two databases, which leads to various performance problems that are almost
random -- one query against a table set will work fine, the next will be
excruciatingly slow. Sometimes a pass-through will fix this, other times make
it worse.

I'm thinking that the proper solution to these problems is to invest some
time in looking at SQL Servers's built-in options for this sort of problem.
One is to use SQL Server's capabilities to link to another data source and
pretend that these are tables in SQL Server. The other is to use a DTS
Package to copy over the data, perhaps at night. I'd like to hear any options
you all might have on these options.

One question about linking: forgive my newbieness about this, but how
exactly does one do a query against two databases?

And one about DTS: is there a way to fire up DTS via a "query" from within a
SQL statement? Depending on the performance, perhaps I could copy the data in
"on demand" (as opposed to a nightly process) and then run all the expensive
queries in the local database. The overall time to do this might be shorter?

Maury