Fog Creek Software
Discussion Board




Good tips on downsizing stored procedures for Jet?

Hi All,

It looks like we're going to have to add support for Jet/Access into our app that currently supports SQL Server and uses stored procedures with return values and @xxx parameters for all data interaction. I have a number of articles, but thought it would be good to ask if there are any good tips from the trenches on downsizing SQL Server stored procedures for use with Jet.

Thanks,

  --Josh

JWA
Wednesday, August 11, 2004

This might sound like left field, but rather than Jet you could think of using a Visual Foxpro database as the file sharing database.  With the OLE DB provider you can pretty much do just as you did with SQL Server/MSDE.  And no, you don't have to use VFP as the client.

However, there's no real alternative to re-writing any stored procedures (I can't remember if Jet really has them), since your existing code is T-SQL and whatever you move it to it won't be the same.

Simon Lucy
Wednesday, August 11, 2004

There is no way to automatically downsize SQL Server Stored Procedures to the Jet Engine.  The problem is that they are two different variants of SQL.  TSQL for SQL Server and Jet SQL for the Jet Engine.

In a normal front end for a Jet Engine based application you would have embedded SQL, that is an SQL statement directly in the program itself or you would create a query in MS-Access that you could call from the source code through Connection.Execute.

At any rate, your best bet is to create the database in MS-Access and use the Jet 4.0 Engine.  It has the ability to get newly inserted ID's using the statement "@@IDENTITY" among other improvements.

The bottom line is that you will have to rewrite or rework some if not most of the SQL.

One way to organize the embedded SQL is to use "entity classes" as Joel calls them.


Wednesday, August 11, 2004

With Jet you would most likely want to use ADO, too.  There is a (little known, I think) facility in Jet for stored procedures via ADO.  They're limited; you can return only a dataset and they're basically just like a view with parameters, no procedural code within a query with Jet (though you can write UDF's in VB for Jet). 

But the Jet stored procedure support is good to know about:

http://support.microsoft.com/default.aspx?scid=kb;en-us;201493

http://support.microsoft.com/default.aspx?scid=KB;en-us;q202116

You should also find some sort of reference on other differences between Jet and T-SQL.  Not sure of one at the moment; I would assume they have something pretty good on MSDN somewhere.

Herbert Sitz
Wednesday, August 11, 2004

Why change to Jet?  The MSDE is free and fully compatible with SQl server (it IS SQL Server, but a desktop version)

Chris Peacock
Wednesday, August 11, 2004

Danger in this conversion; likely provides fulfillment of immediate short term needs ("SQL Server too 'hard' or 'costly') ... but you should be aware SQL Server 2005 MSDE ("Express") has made "leaps" and "bounds" in the "too hard" department. You can try out those leaps/bounds right now via beta.

Do check it out before downsizing:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

Likely, you're already on the "right" track by supporting SQL Server ... if I were you, i'd seriously consider not diverting. Especially w/ 2005 on the horizon.

Anon
Wednesday, August 11, 2004

How about a solution where MSDE is used locally on the users' machines to facilitate the usage of the existing procedures?

You could have the local MSDE engine (running against a local 'temp' database) import whatever data is needed from the central .MDB Access data source, run the procedure over it, then put the result back in the .MDB.

It adds another layer, and it certainly isn't the most efficient in terms of bandwidth and such. However, if the data being dealt with is sufficiently small, it may be worth it.

Doing it that way, you at least wouldn't have to maintain two sets of db interface code meant to accomplish the same things, and you wouldn't have to modify your procedures at all.

Chance Govar
Wednesday, August 11, 2004

>> This might sound like left field, but rather than Jet you could think of using a Visual Foxpro <

Interesting, two questions:  does Foxpro support SQL 92, and what are the redistribution run-time royalties like?

Gunnar Skogsholm
Wednesday, August 11, 2004


The latest version of mySQL (5.0) is beginning to offer support for Stored Procedures.  I think that's how I'm going to be able to convince my boss to give it a try...

KC
Wednesday, August 11, 2004

Foxpro? Blech!
mySQL? Blech! Wow, just starting support for SPROCs. Stop the presses.

MSDE!
MSDE!
MSDE!

All the SQL Server without all the benjamins. Life is good, folks.

Anon
Wednesday, August 11, 2004

Had a nap since my last comment and a thought hit me...

I believe you may be able to do sort of what I was saying before but much easier if you can link the .MDB as a another server with something like:

EXEC sp_addlinkedserver
  @server = 'SEATTLE Mktg',
  @provider = 'Microsoft.Jet.OLEDB.4.0',
  @srvproduct = 'OLE DB Provider for Jet',
  @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

Could you then run the stored procedures against the data without any sort of import/export thing going on?

I'm not sure if MSDE supports linked servers though.

Chance Govar
Wednesday, August 11, 2004

Although you could do it, I agree with Anon above that create a "downsized" version that runs with Access sounds like a bad idea.

What exactly is the reason for wanting to move to Access?  Is it just a conceptual thing for the users?  I.e., that you think users will understand the operation of a filesharing database better than a database server?  Or something else?

Whatever it is, I'd suggest looking into MSDE or the new even easier to use SQL Server Express.

Herbert Sitz
Wednesday, August 11, 2004

Hi All,

Thanks for the comments and ideas.

Chance - those are some very good possibilities that I'm looking into now. MSDE does support linked servers, and it appears that may be the easiest/best option. It would require changing the table names to include the fully qualified four component name, but that's about it.

For everyone saying to use MSDE, please refer to the post immediately below this one (http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=173466&ixReplies=14) where I explained the desired use scenario and we basically determined that MSDE alone won't work. The bottom line is that we need to allow the users to point to a  network share rather than a DB Server, even if it is just MSDE. Chance's idea of using MSDE on the clients as the access layer connecting to a shared .MDB is on the right track and just might work perfectly.

Thanks again, and I'd welcome any further ideas/comments.

  --Josh

JWA
Wednesday, August 11, 2004

"Is it just a conceptual thing for the users?  I.e., that you think users will understand the operation of a filesharing database better than a database server? "

Yep - exactly. We will package the app to be used in a standard client/server config supporting SQL Server or MSDE/SQL Server Express as the main config, but we also need to offer a configuration where users can A) install it on their single computer and run alone, which MSDE is perfect for and B) easily* share data between three to four installs on a simple workgroup network. This is where things get tricky.

  --Josh

* "Easily" here means from the average user's perspective, which by definition means without the need to get the IT department involved, if they even have one. It doesn't have to pass the grandmother test, but at least the Mom/Dad test.

JWA
Wednesday, August 11, 2004

If ease of use is the issue, I'm betting it would be far easier to build a little shell application that the users can use to direct their application at the local server or at a server on another computer, than to retool the application to use MSAccess.

In addition to that, file corruption and locked file problems caused by novice users accidentally turning off machine with the application runnning without going through the shutdown process (or having their machine or the application itself crash) can be a big usability problem with Access.

For a small database like the one it sounds like you'd be using, SQL Server/MSDE is far more bulletproof and doesn't require much administration at all.

Yes, Access would work, but deciding to use it merely to enhance ease of use for some novice users sounds like a bad choice to me.  Bad because (1) it's an expensive choice in retooling the existing application, (2) you can build functionality into the existing app that guides the user through setting which database they're going against (and it doesn't have to be conceptually difficult), and (3) you're likely to have more reliablity problems with Access than with SQL Server/MSDE.  And (4), depending on how the existing app is designed you may also have poor performance on Access clients when they're accessing the data in a file on a network share.

Herbert Sitz
Wednesday, August 11, 2004

"I'm betting it would be far easier to build a little shell application that the users can use to direct their application at the local server or at a server on another computer"

But there won't be a local server.

True, it is going to be a fair amount of work to support this config, but we're talking about a barrier to entry here. If the sales force can't install it let alone the potential customers because none of them understand the client/server paradigm then it just flat isn't going to get sold.

This standalone workgroup level app is not insignificant, in fact it is very important. The vast majority of our customers begin with one department buying the lower level app and their positive experience results in the company buying and deploying the enterprise level version.

Users don't understand client/server but they do understand files & folders. No matter the technical merits, if the product doesn't fit the user's mental model it will not sell.

I'd be very happy to be able to say that the only options are a single install working from MSDE or a full client/server, but that is just not an option.

  --Josh

JWA
Wednesday, August 11, 2004

"(2) you can build functionality into the existing app that guides the user through setting which database they're going against (and it doesn't have to be conceptually difficult)"

We discussed this a bit in the other thread. Making it simple to set up is not the main problem to this approach - availability is. What happens when the first guy to install it and who everyone is pointing to decides to go on vacation and bring his laptop with him? Everyone is down - obviously a bad design choice.

Replication doesn't work either, because each successive install requires settings changes on every other install.

I may be wrong, but the way I see it the only solution will have to be based on multiple users accessing a file/files on a network share.

  --Josh

JWA
Wednesday, August 11, 2004

"What happens when the first guy to install it and who everyone is pointing to decides to go on vacation and bring his laptop with him? Everyone is down - obviously a bad design choice."

What happens when the guy sharing the file goes on vacation?

Chris Altmann
Thursday, August 12, 2004

Chris -- My question exactly.  The whole scenario sounds strange.  Sounds like something isn't being explained clearly.  What is it, exactly, that makes this setup so different from any other networked database setup that's ever existed?  Makes me wonder.

Herbert Sitz
Thursday, August 12, 2004

If, as I'm beginning to suspect, this setup is supposed to work in a peer-to-peer environment (i.e., no single designated server or fileserver for the database) where the peers are all laptops so on any given day one of the peers may not be there, then you're going to big problems no matter whether you use a database server or a filesharing database. 

Is that what it is?

Sounds like a recipe for disaster to me if you're not willing to require them to designate one computer as the permanent server, even in a peer-to-peer setup. 

The usability issues of database server vs. fileserving database pale in comparison to issues of trying to cope with a database that is hopping from computer to computer.  In my opinion, that way lies madness, both for the users and for whoever is going to be supporting this thing.

Herbert Sitz
Thursday, August 12, 2004

As I suggested in the tread below (discussing the same thing in two treads is a bitch, isn't it), it is not all that bleak.
There is a common, permanent point: the File share.
The thrick is to not put the DB on there, but a shared transaction log. This can be just a text or XML file.
Each "install" keeps its local MSDE, but publishes to and periodically polls the shared file for updates.
This "synch" process can even be kept mostly separate from the main code, so as to less "pollute" the main code base with this special "trickery".

Just me (Sir to you)
Thursday, August 12, 2004

Thanks JWA, I got my ego boost for the day :)

I'm glad to hear that sounds promising to you. I look forward to trying to be as useful in the future.

As an aside, hello to the people that are regulars here. I stumbled on your site here googling for something and I liked many of the posts and such I read. You guys seem to have a really good bunch of folks here.

Chance Govar
Thursday, August 12, 2004

It occurs to me that if server status is given to one of those pesky users with a taskbar that makes minesweeper look like it could use some extra buttons, your performance may not be too optimal. 

Chance Govar
Thursday, August 12, 2004

*  Recent Topics

*  Fog Creek Home