Exposing data to clients

[Jo Blow]

Hi folks,

I have a client that has a distributed access disaster that I want to move
to a central ASP.NET system. I'm new to web services but is it possible to
expose the raw database tables (subset off) in a web service that they can
link to to query from Excel/access/whatever.

The problem is that they don't want to move to web-based as they are worried
they won't be able to do ad-hoc querying and reporting. (as they can in MS
Access)

If it is if someone can point me at a good book/reference I can purchase
else a good develper I can use.

Thanks all
ABold (sorry for anonymity trying to avoid more spam) post to the group
please as email is bogus.

 

[Dino Chiesa [Microsoft]]

NO,
I think web services is not what you want.
Think of web services as a way to enable an application for remote
invocation.
The web service publishes a set of message endpoints, and then developers
can create new apps that tickle those endpoints.

if you have an existing app (like MS Access, or Excel) then it will not
"know" how to utilize these newly-defined endpoints. Instead they "know"
how to contact databases, via db-optimized protocols and interfaces.

Maybe something like Data Transformation Services (included free in SQL
Server) could do what you want. You could use DTS to grab a subset of data
and then make that subset downloadable or remotely-accessible for ad-hoc
querying to each client app.

Another option to consider is SQL Server Replication. It could do
basically the same thing.

And finally you could write your own simple data transformation code that
might run every hour, or every day, every week, whatever schedule you need.

None of this solves the distributed access disaster. if you want to do
ad-hoc queries, you still need to expose the data to those apps.

----

Now I could imagine a new webservice that exposes a "DoQuery" endpoint. The
endpoint might accept T-SQL, and then might run that query on the server.
But,
- be careful of security.
- MS-Access and MS-Excel don't know how to tickle that endpoint! you'd
have to teach them to do so.

-Dino