I've long used Access as a method of combining data from two sources.
Half of the data is in our SQL Server, the other half is in a PSQL
server that runs our accounting system. Using Access to combine these
sources is a problem; it means everything has to run client-side for
one thing, and it also means the data is going over the slowest part
of the network, the back-office to the front.

I've considered using SQL Server's ability to link to another server
as a way around this, replacing Access Queries with SQL storedprocs.
This would mean that the data would all be staying on the 1 GB
portions of the network in the back office, for one thing, and reading
over the SQL Server docs it also seems that SQLS's query logic might
provide better performance anyway?

However, I can't find much practical advice on this, and what little I
have found seems to suggest that the performance might be terrible.
Does anyone out there have experience with this?

Maury

There are no simple answers to this. I can understand your point about keeping the large data amount
on the high-speed network. How it all ends up depends on the queries being executed in the end (the
execution plan). My suggestion is that you give it a try and read the execution plan for one of
those queries. You will find a "Remote Query" operator (or similarly named) and this is the SQL
submitted to the other DBMS. If you find that suboptimal (not passing a WHERE clause, for instance),
then consider using OPENQUERY or OPENROWSET to take control over what is submitted.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Maury Markowitz" <maury.markowitz@gmail.com> wrote in message
news:0f8f6887-1c86-4ed8-9c2e-b788126f132a@z66g2000hsc.googlegroups.com...
> I've long used Access as a method of combining data from two sources.
> Half of the data is in our SQL Server, the other half is in a PSQL
> server that runs our accounting system. Using Access to combine these
> sources is a problem; it means everything has to run client-side for
> one thing, and it also means the data is going over the slowest part
> of the network, the back-office to the front.
>
> I've considered using SQL Server's ability to link to another server
> as a way around this, replacing Access Queries with SQL storedprocs.
> This would mean that the data would all be staying on the 1 GB
> portions of the network in the back office, for one thing, and reading
> over the SQL Server docs it also seems that SQLS's query logic might
> provide better performance anyway?
>
> However, I can't find much practical advice on this, and what little I
> have found seems to suggest that the performance might be terrible.
> Does anyone out there have experience with this?
>
> Maury
>