[dba-SQLServer] transactional SP involving tables from two SQL Db - Web Service

Gustav Brock Gustav at cactus.dk
Thu Jun 19 03:15:44 CDT 2008


Hi Borge

You can't do an engine level transaction that spans both SQL Server and JET (your local database). So you will have to write your code like a higher level transaction and catch lost connections and the like during an update.

If the tables at the SQL Server are not too big, an ODBC connection and linked tables are fine and, indeed, very simple to handle as you can use normal Access queries.
However, if you have an ODBC connection you can also run a pass-through query which - as you probably know - is run on the SQL Server. Very handy and speedy for the update and insert of data on the server.

I see no reason why to move further creating stored procedures for the simple tasks you describe but it can be done, of course. Again, you can call these via ODBC.

I'm not sure how to interface a Web Service with Access 2003. With .Net it is "piece of cake". I see no advantages in this for you, so unless management decide for this I would stick with ODBC and/or ADODB.

/gustav

>>> pcs.accessd at gmail.com 19-06-2008 02:13 >>>
Hi,

I am in desperate need for some input advice as to the following:

Background - simplified:
Access 2003 application with SQL2005 Db backend
The application is processing requests for short term vacancies (Booking) on
behalf of a remote Client, identifying suitable Person to fill the vacancy
(Placement).
Bookings are placed - via an application on the remote Client's distributed
intranet - into a table on Client's SQL2005 Server.
Our application is querying this table throughout the day in order to bring
new Bookings into our SQL db table
We also perform updates on a Status Field
Once a Placement has been identified (via text to speech automated phone
calls to potential Person filling the vacancy - but that's a different
story) we write the data to a new record on the remote Client's Placement
table

on remote client SQL Server:
tblremoteBooking
tblremotePlacement

on our local SQL Server:
tblBooking
tblPlacement

We have been given access to the tables on the remote Client Server via a
userprofile/login that gives us read,insert and update rights to these two
tables

We access the tblremoteBooking table via ODBC linked table to the Access
database window.
We then do query join between tblremoteBooking and tblBooking to get all new
bookings from the client into our table

Other updates and inserts happens via ADODB connection

Issues / Challenges:

1)
There is currently a push from Client's IT people to revoke the direct table
access and give us a web service for interacting with the tables - I don't
like that because I know very little about web services.


2)
I'd like to be able to perform joins etc between the remote booking table
and our local one in a SP on the sql db - rather than doing it in an access
query on odbc linked tables in the Access database window.

Similarly I'd like to be able to create a transactional SP that involves
update / insert to both remote and local tables with rollback so that if one
of the update / insert queries fail we can roll back the whole transaction
and give the data the remote and local tables in sync.

 What's required to access tables on a remote SQL Db inside a local SQL Db
in a stored procedure ?

3)
Another developer has created a web portal for our casual relief staff to go
online and have a look at Bookings on the web portal  and accept a suitable
booking.
All is tested and ready to go except for the web app to have acces to the
client's table for doing an insert to the placement table and an update to
the booking table.
Again, here we are inserting and update to both our 'local' tables and the
client's table so we want the same stored procedures put in place to ensure
data is in sync....


What to do?

Assuming that the Client would give us access the the remote tables, can
someone give pointers to how to establish connection to these tables from
our local SQL Db so we can manipulate these tables in a Stored Procedure.

Assuming that we are going with a web service for reading and writing to the
remote tables - can we then still access these tables in a SP - or will we
have to do this elsewhere (other coding environment) .... and remember we
are doing this in VBA !

Any comments / advice appreciated

Regards
borge





More information about the dba-SQLServer mailing list