Operating across multi SQL Server databases

[Br]

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one for
each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global information. What
method(s) would people recommend that would work best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

[Larry Linson]

I am not aware of the details of how to accomplish it, but you can link
tables in different databases in SQL Server, just as you can in Jet. If you
are going to do something "global" with disparate databases, that would seem
to be the first approach to consider.

I might suggest that you consider redesigning the database so that only one
instance would have to be run, but the tables have "user" identification for
the data. However, as that may not be a common requirement (e.g, just this
one "outsourcer" or a few), that might just be unused, extra data, for most
of your clients.

Other than this, I am not sure what recommendation you are seeking.

Larry Linson
Microsoft Access MVP
"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one for
each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global information.
What method(s) would people recommend that would work best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

[Graham Charles]

I've done something similar in Access; I coded a routine to dyamically
link the tables I needed and pull in their data, using a local table to
keep a list of the database locations. I don't think an ADP would be a
good choice, since you can only connect to one database at a time.

If you're comfortable with SQL data warehousing, that's probably the
best bet, though... that's what it sounds like you're doing.

[Jerry Boone]

What about finding a SQL Server hosting company?

If the SQL Server happened to be hosted in one place and the connection
strings all pointed there (IP over the web - or - VPN and connect) then your
in business. Multiple users anywhere could connect and operate from a
single datasource. Of course depending on the efficiency of the client
application and how much it utilizes the server itself for processing
(particularly on report generation), performance could be an issue. However
I would think it is certainly worth a shot before recoding or messing with
replication. Could be another profit center for you as well whether you
host the db yourself or outsource that. Especially if the client sites have
a good dedicated internet connection.

--
Jerry Boone
"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one for
each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global information. What method(s) would people recommend that would work best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

[Br]

Larry Linson wrote:

I am not aware of the details of how to accomplish it, but you can
link tables in different databases in SQL Server, just as you can in
Jet. If you are going to do something "global" with disparate
databases, that would seem to be the first approach to consider.
Thought of that but we're using an ADP...
I might suggest that you consider redesigning the database so that
only one instance would have to be run, but the tables have "user"
identification for the data.
Yes, this is the way I'd like to have done it but I didn't design it and
there is not the time/resources to redesign it at the moment. There is also
some benefit to having the data seperate.
However, as that may not be a common
requirement (e.g, just this one "outsourcer" or a few), that might
just be unused, extra data, for most of your clients.

Other than this, I am not sure what recommendation you are seeking.

Larry Linson
Microsoft Access MVP
"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one
for each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global
information. What method(s) would people recommend that would work
best? (Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

--
regards,

Br@dley

[Br]

Graham Charles wrote:

I've done something similar in Access; I coded a routine to dyamically
link the tables I needed and pull in their data, using a local table
to keep a list of the database locations. I don't think an ADP would
be a good choice, since you can only connect to one database at a
time.
Unfortinately it's not a choice... it's what the system has been written in.

I could maybe write an external interface MDB to do the processing...
If you're comfortable with SQL data warehousing, that's probably the
best bet, though... that's what it sounds like you're doing.

--
regards,

Br@dley

[Br]

Jerry Boone wrote:

What about finding a SQL Server hosting company?

If the SQL Server happened to be hosted in one place and the
connection strings all pointed there (IP over the web - or - VPN and
connect) then your in business. Multiple users anywhere could
connect and operate from a single datasource. Of course depending on
the efficiency of the client application and how much it utilizes the
server itself for processing (particularly on report generation),
performance could be an issue. However I would think it is certainly
worth a shot before recoding or messing with replication. Could be
another profit center for you as well whether you host the db
yourself or outsource that. Especially if the client sites have a
good dedicated internet connection.
All the databases are on the same local server so none of this is required:)
"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one
for each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global
information. What method(s) would people recommend that would work
best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

--
regards,

Br@dley

[Lyle Fairfield]

"Br@dley" <do***********@google.com> wrote in
news:dv**********@news-02.connect.com.au:

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one
for each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global
information. What method(s) would people recommend that would work
best?

(Access2000 ADP, SQL Server 2000)

Are the dbs on different servers? Do they have different names?

If they're not on different servers on can just access them using their
names? eg: SELECT * FROM FFDBA_ESO_LOCAL.dbo.tblSchools runs fine when my
ADP is linked to a db named Temp but which is on the same server as the db
FFDBA_ESO_LOCAL (assuming I have permissions of course).

And if they are on different servers the servers can be linked (BOL has
mucho info about linking servers; we can even link to an mdb if it resides
on the same machine as the SQL server), and the dbs can be accessed by
servername.dbname.owner.object?
eg SELECT * FROM Server2.FFDBA_ESO_Remote.dbo.tblSchools

But perhaps I am not understanding. Perhaps you need to access tblSchools
on several different dbs on several different servers. I don't know if a
Union will work in those circumstances or not, but I'd give it a shot.

--
Lyle Fairfield

[Br]

Lyle Fairfield wrote:

"Br@dley" <do***********@google.com> wrote in
news:dv**********@news-02.connect.com.au:

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one
for each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global
information. What method(s) would people recommend that would work
best?

(Access2000 ADP, SQL Server 2000)
Are the dbs on different servers? Do they have different names?

Same server.
If they're not on different servers on can just access them using
their names? eg: SELECT * FROM FFDBA_ESO_LOCAL.dbo.tblSchools runs
fine when my ADP is linked to a db named Temp but which is on the
same server as the db FFDBA_ESO_LOCAL (assuming I have permissions of
course).

Perhaps. Ideally I'd need to make it as dynamic as possible (ie. 'register'
the database into a table and insert the table names into the SQL
statement).

<>
--
regards,

Br@dley

[Jerry Boone]

Ahh...

Interesting, so you need to combine all the databases together for a single
source solution.

You could make a database called "globaldb" or something and make views
named for each table object you have in the other databases...

use globaldb
create view 'table1'
as
select * from db1.dbo.table1
union
select * from db2.dbo.table1
union
select * from db3.dbo.table1

create view 'table2'
as
select * from db1.dbo.table2
union
select * from db2.dbo.table2
union
select * from db3.dbo.table2

and so on...

This gives you a single db to connect to that combines data from the other
db's. In just about any sql server scenario view objects are completely
interchangeable with table objects. I always use views to "pull" in data
from other databases. Then if the source database changes you have a "shim"
layer where to can adjust such changes to keep from having to re-code your
application.

--
Jerry Boone
"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

Jerry Boone wrote:

What about finding a SQL Server hosting company?

If the SQL Server happened to be hosted in one place and the
connection strings all pointed there (IP over the web - or - VPN and
connect) then your in business. Multiple users anywhere could
connect and operate from a single datasource. Of course depending on
the efficiency of the client application and how much it utilizes the
server itself for processing (particularly on report generation),
performance could be an issue. However I would think it is certainly
worth a shot before recoding or messing with replication. Could be
another profit center for you as well whether you host the db
yourself or outsource that. Especially if the client sites have a
good dedicated internet connection.
All the databases are on the same local server so none of this is

required:)

"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one
for each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global
information. What method(s) would people recommend that would work
best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

--
regards,

Br@dley

[Br]

Jerry Boone wrote:

Ahh...

Interesting, so you need to combine all the databases together for a
single source solution.

You could make a database called "globaldb" or something and make
views named for each table object you have in the other databases...

use globaldb
create view 'table1'
as
select * from db1.dbo.table1
union
select * from db2.dbo.table1
union
select * from db3.dbo.table1

create view 'table2'
as
select * from db1.dbo.table2
union
select * from db2.dbo.table2
union
select * from db3.dbo.table2

and so on...

This gives you a single db to connect to that combines data from the
other db's. In just about any sql server scenario view objects are
completely interchangeable with table objects. I always use views to
"pull" in data from other databases. Then if the source database
changes you have a "shim" layer where to can adjust such changes to
keep from having to re-code your application.
Thanks.

The reason I need to work across the databases is because there is an import
transaction file that contains data for all the databases. So this needs to
be imported and reconciled.

"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...

Jerry Boone wrote:

What about finding a SQL Server hosting company?

If the SQL Server happened to be hosted in one place and the
connection strings all pointed there (IP over the web - or - VPN and
connect) then your in business. Multiple users anywhere could
connect and operate from a single datasource. Of course depending
on the efficiency of the client application and how much it
utilizes the server itself for processing (particularly on report
generation), performance could be an issue. However I would think
it is certainly worth a shot before recoding or messing with
replication. Could be another profit center for you as well
whether you host the db yourself or outsource that. Especially if
the client sites have a good dedicated internet connection.

All the databases are on the same local server so none of this is
required:)

"Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au...
We have a product that is being used by a client to outsource
salary packaging. They therefore run several instances of our
database, one for each of their clients.

There is now a requirement to run operations globally across all
the databases including a central database holding some global
information. What method(s) would people recommend that would work
best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

--
regards,

Br@dley

--
regards,

Br@dley