Session E-TRAN

MS Transaction Server and VFP

Eldor Gemst
ELGEM Associates


First of all folks, you can all relax.  The stuff I’m going to show you is really a lot easier than you probably imagine.  Before I started working in-depth with Microsoft Transaction Server, I feared this was going to be a horrible new technology that I would have to learn (and I’m sometimes a slow learner) and I didn’t even understand why I’d have to learn it.  The surprise is that this really is easy, and very much worth learning.

I guess we should clear up right at the start, exactly what Microsoft Transaction Server (MTS) is, and why should you, as a Visual FoxPro programmer, care about it.  That’s going to take a bit of discussion.  In a nutshell, MTS takes much of the hard work (the “grunt” work) out of building large-scale client/server applications. MTS certainly helps us manage transactions (hence the name) but it does a lot more than just manage transactions.  Probably we would do better to think of MTS more as a component manager than just a transaction server.

It can manage components, resources, deployment, security and distributed transactions, even across multiple servers or across different databases (such as a mixture of Oracle and SQL Server).

But there is more to it than just that.  We’re dinosaurs, if we continue to build the kind of huge monolithic database applications we used to build.  Those days are gone in the overall scheme of things and if we want to move our skills forward and continue to survive… no, to thrive, in the future, we need to keep up with what is going on.

The problem with those large monolithic applications we used to build was that any required changes meant that our entire application needed to be changed.  It would be like having a flat tire and then needing to rebuild the entire car to solve the problem.  In our world we’d then need to update all the client sites with the new application, which could be rather difficult and time-consuming.

Have you heard of “Total Cost of Ownership” (TCO)?  The idea is to reduce the costs associated with computer solutions, both from a development and ongoing maintenance point-of-view.  This is one reason Microsoft has been promoting COM technology (“Component Object Model”) in a big way… even OLE (OLE2 to be more precise) took advantage of COM technology to do the things it did.  Pure COM is even more powerful, and as I work with it and learn more about it, I’m getting more and more excited about software development and the future.

Ok, let’s put this into context… The main idea behind COM is that the developer (or team of developers) can and should separate various parts of the overall software solution into manageable chunks.  Think of it as being similar to what most of you have heard of when talking about Client/Server applications.  It’s fairly common to have a “3-Tier Architecture” which separates the User Interface from the Business Rules from the actual Data Storage. (See figure 1.)  With proper design, any one of those tiers can be replaced with little or no changes to the other tiers.  That greatly helps maintenance, scalability and reliability.  Each tier is tested and can really stand alone, or be plugged into another scenario.  So if the business rules in your application change, you don’t need to change anything but the middle-tier.  Much easier maintenance.  COM components also have language independence, meaning that they can be written in many different languages, and once written can be used by many different systems.  You can also mix and match components from several languages within the same system.  Any application that conforms to the COM standard can use these components, so you could have an Internet application using ASP pages, a FoxPro, Visual Basic or C++ app using the components, etc.  This really helps leverage your efforts and reduces your Total Cost of Ownership.  COM components can be located and run almost anywhere on the system, including remote machines thanks to Distributed COM (called DCOM).

<Fig. 1. 3-Tier Architecture allows more flexibility and better scalability without replacing entire application.

Originally MTS was a separate Microsoft product, costing big bucks (about $2,000).  Recently Microsoft made it available through the Windows NT 4.0 Option Pack for free.  (If you don’t have the Option Pack through MSDN or similar, you can download it from .)  And with Windows NT 5.0 (just renamed to Windows 2000), MTS will actually be built-in, as part of the operating system.  This shows the importance Microsoft is placing on MTS in the overall scheme of application development and operating systems.

Understanding Transactions

In order to understand what MTS is and how it fits into the picture, it is first necessary to be comfortable with the concept of “Transactions”.  It’s very simple, really.  If we simply edit a customer’s telephone number and no other files/records are affected, that isn’t really a transaction.  But what if we have a sale?  That would normally require several edits, perhaps to the invoice header file, line-items, inventory and maybe even the customer files.  If we talk about a “transaction” here, the idea is that all of these edits/updates should either succeed or fail.  There should be no partial updates possible.  The whole process is considered to be one unit.  One of the most common examples used to illustrate the importance of transactions is that of a banking application.  It should not be possible to transfer funds from one account to another unless both the deposit and withdrawal operations succeed.

Fig. 2. With a transaction, all must succeed or all must fail.

Visual FoxPro finally gives us transactional processing, by way of the BEGIN TRANSACTION and END TRANSACTION (or ROLLBACK if something could not be successfully updated).  This is a terrific improvement.  MTS allows us to build on what we now have natively in VFP and to go even further, with believe it or not, even less effort required by the programmers.  Those of you who know me, know that I believe strongly in quality results and quality software, but if there’s an easier way to get to the same (or better) result I’ll use it.  I don’t want to work any harder than I have to.  (But I’ll go the distance if required, to deliver reliable and efficient software.)  Using MTS makes my life easier and the result more powerful.

Transactions, in order to be considered valid, must meet the ACID test requirements as outlined in the following table:
ACID Test for Transactions


Ensures that entire transaction commits or nothing


A transaction is a correct transformation of the system state


Protects concurrent transactions from seeing each other’s partial and uncommitted results


Committed updates to managed resources can survive failures

Advantages of Using MTS?

There are numerous issues which should be considered and planned for in client/server application development.  Besides the transactions we just spoke about, here are some other areas where MTS shows particular strengths:

Component Transactions

MTS keeps track of the transactional state of all components running under its control, even if the transactions span several databases (even different databases such as a mixture of Oracle and SQL Server) and/or several servers.  There might be many different components, each one designed for a specific operation or specific server/database.  This allows the developer to more easily build the system in the first place, as he or she can concentrate on smaller aspects of the program at one time.  Each component can then be combined in a “package” under MTS control.  Version 2.0 of MTS currently only supports component transactions against SQL Server and Oracle.  Development is underway to support other databases.

Resource Pooling

It’s pretty obvious that we all want the most return for our development effort and if we end up with more users on the system than we expected, or more data/transactions, then we need to be able to handle the new demands without, hopefully, having to redo everything.  Look at a simple thing like connections.  Many server products are licensed on a per connection basis, and connections can be used up pretty quickly.  More users?  Just add more connections?  It’s not quite that simple.  Adding extra connections can be expensive, in terms of money and resources. Every connection takes up memory on the server and affects performance.

Fig. 3. Traditionally every client has a connection to the data

Let’s say you have a fabulous pizza shop, and only one cook and one oven.  You can only make one pizza at a time, and as your fame spreads and you get more clients, you find there is too much wait time before the next pizza can be made and delivered to the customer.  Imagine if you solved the problem the same way we’ve been doing in the computer business…  You hire more cooks and buy more ovens (that’s like getting more and more connections) and now you’ve got 10 cooks and 10 ovens.  That’s ok, except for the fact that much of the time many of the cooks and ovens are sitting there idle, not being used.  But they still cost money… the cooks need to be paid and the ovens need to be financed.  We haven’t even talked about the extra space all those cooks and ovens require.  The better solution would be to hire only one or two extra cooks and to give the next pizza order to the first cook/oven available.  Sort of like pooling your cooks and ovens.  MTS helps us by pooling connections and allowing you to service your users without requiring a connection for each and every user.

Fig. 4. MTS allows connections to be pooled, resulting in far less connections required

Object Brokering

Instead of the programmer needing to create objects for every user of the system, MTS looks after this task for us.  MTS takes care of creating the COM object when needed, maintaining it, and cleaning up afterwards.  This greatly reduces the programmer’s workload.  Performance is excellent too, as once an object has been created, even if it isn’t being used anymore, it’s kept in memory for a time (configurable by the developer) so that if needed again, it is available much more quickly than it would be if more traditional programming were used.  The only time a slight performance hit might be noticed is the very first time the component is needed.  This is often called “Just In Time Activation” (JIT).  MTS might actually destroy the instance of the component while the client application isn’t using it, even thought the client might maintain a reference to the object.  This is pretty cool as MTS actually fools the application into thinking the object still exists and when the application needs the object again, MTS creates a new component instance and presents that.

It’s the object brokering feature which allows location independence of the component. The client application doesn’t care where (on which server or even on a workstation) the component is running.  MTS locates the component using COM and perhaps DCOM (Distributed COM) and instantiates it.  And without having to change the application code you can even change the location of the object as your needs change.  MTS does this for you.

A future version of MTS will also support object pooling, which means rather than giving every client their own instance of a component, a fixed number of components will be instantiated and then shared among the clients.


MTS can make security management much easier than with more traditional approaches.  Just about all security requirements of a client/server system can be handled by MTS.  Two modes of security are available: Declarative and Programmatic.

With Declarative Security instead of having to set security access to the database and/or the server resources, security is configured at the MTS level.  It’s then the component (under MTS control) which has access to the databases and server resources.  This is much easier to accomplish, especially as users (and groups) on the system change.  Each component under MTS control can be assigned a “role” and then NT accounts can be mapped to that role.  Roles are basically lists of NT user groups/accounts that should have access to the component.  We can use MTS declarative security features to map multiple users to one or more NT accounts created just for the component to be able to communicate with “downstream” components and resources.

Programmatic Security allows you to handle the exceptions to the rule, where Declarative Security doesn’t go far enough.  Perhaps your component needs to behave differently, depending on who the client is.  When a component asks MTS who is using the component, it is the role that the user is operating under and not the actual user ID that is returned.  This makes the component much more self-contained, and does not tightly couple the code of the component to the users on the system.


Changing configurations of your client/server system usually involves making changes to much of the code of the system, but with MTS very much of this can now be done more easily.  MTS allows you to easily configure components under its control, specify where components run (either on the MTS server or a remote machine), monitor transactions to allow you to diagnose potential problems more easily, and create and delete roles under which the components run to configure security. The tool used for much of this is the Transaction Server Explorer.

How to Use MTS?

You can create a COM object with your favorite product, be it Visual Basic, Visual J++, C++ or even my personal favorite, Visual FoxPro 6.0.  In a nutshell, here are the steps required to use a COM object with MTS:

Now we will look at these operations in more detail…

How to Create the COM DLL in Visual FoxPro

When you create a COM DLL using VFP, you end up with two files; a DLL and a TLB. (The TLB file is the type library file containing properties and methods of your server.)  This is actually an “in-process” DLL and in order to work with MTS, a component must be in-process.

The object reference to the MTS component is obtained very simply.  All you need to do is something like:

Once you have the object reference to the MTS component, you next need to get a reference to the “context” of the object.  This is really a transactional context and you use that to commit or rollback the transactions.  You get the context reference with the following line of code:

So, where’s the trick?  This looks very simple.  Well guess what?  It is simple!

For the purpose of this session I am using the PUBS database that ships with SQL Server.  I build two classes, Eldor and Eldor2 (how’s that for creative names?  <Grin>) and they have SetAuthContract and CountAuthors methods respectively.  In the Authors table of the Pubs database there is a list of authors with a field indicating whether or not the author has a contract.  SetAuthContract allows us to set the contract for an author to true or false, depending on a parameter we pass.  In the SQL Server table, the Contract field really is a “bit” field, but in VFP we look at it as a logical field.  To make our lives easier, we use a remote view to that table.  The database containing the view is marked as “included” in the VFP project as there are no tables there that will be written to, which makes the resulting DLL much more portable.  The database is simply the container for the view.  Here is the code we are using:


In order to make our COM object available to MTS we need to mark the class as being “OLE Public”.  This is done for every class, by clicking on the Class Info menu pad.  (See Figure 5.)

Fig. 5. In the Class Info dialog, mark your class as being “OLE Public”.

MTS also requires that your connection to the data source be made without a login dialog.  If using a connection to a View in a DBC, make sure that the “Display ODBC login prompt” is set to Never.  If using SQL Pass-Through issue the following command:

Registering your COM DLL

The easiest way to register your COM DLL is to do it when you build it, from right within Visual FoxPro.  This is done automatically for you the first time you build it, but you have to specifically ask to have it done for you on subsequent builds by selecting “Regenerate Component Ids”:

Fig. 6. On subsequent builds, specify to Regenerate Component IDs

If you don’t regenerate the component IDs, your COM object will no longer work because the CLSID (meaning Class ID – that long ID number that looks something like 8E27C92B-1264-101C-8A2F-040224009C02) will no longer match the original.  The other way to register your COM object is manually, by using RegSvr32.exe from the Start|Run menu in Windows:

In addition, you will have to wake up MTS to the fact that the CLSID has changed.  This is most easily accomplished by removing the component and then adding it back. I have also (on occasion) been successful by simply selecting the “package” in MTS, right-mouse clicking and choosing Refresh

If you’re at all like I am, you’ll experiment a lot with this stuff and will end up with all kinds of COM DLLs  scattered throughout your machine(s) and perhaps you’d now like to clean things up.  Well, there isn’t an automatic way to remove DLLs that you no longer need, but you can manually clean up one at a time.  (I keep a text file list of all the DLLs I’ve built and where the DLL file is located.)  You’re just going to have to find them all and then edit the Registry.  The easiest way is to use RegSvr32.exe again:

Setting Up MTS

Next you need to start the MTS Explorer (it’s in the Windows NT 4.0 Option Pack program group by default) and drill down by clicking on My Computer until you see the “Packages” you have installed.  Right-mouse click on Packages and choose to add a new package (see figure 7).

Fig. 7.  Adding a new package to MTS contrrol

From the next dialog, choose to create an Empty Package.  Leave the Set Package Identity at the default setting of Interactive User – The current logged on user.  Double-click on your new package, and then on Components.  Finally right-mouse click on Components and choose to add a new component (figure 8).

Fig. 8. Choose Install New Components instead of those already registered.

I haven’t seen a real explanation of the differences between installing a new component instead of adding components which are already registered, except that I have seen the advice to always choose new components.  And I’ve also had trouble when I tried to add a component already registered.  Maybe it’s something wrong on my machine, but my advice here would be to stick with adding only new components.

In the next step, you must add both the resulting DLL and TLB files to the Package.  The TLB file is a Type Library  that VFP produces when building a COM DLL file and it contains information to allow other programs to understand what our DLL does.  (For example, if you were to use a VFP DLL directly in VB, or Visual InterDev, you will now get IntelliSense and Command Completion, just as if it were a native component.  It’s unfortunate that we ourselves do not yet have IntelliSense, but at least the other “camps” should be more willing to use VFP components now than they were in the past.)

Figure 9 shows the dialog after adding both the DLL and TLB files.  MTS found two COM components within the DLL and displays them in the bottom panel of the display.

Fig. 9. You must add both the DLL and TLB files

After adding the DLL, MTS will show the package with the COM objects it found within the DLL and each object shows up as a ball in the right-side window.  (It’s interesting to watch these balls when actually using the component under MTS as the balls spin.  Not much value in that display, but it’s pretty cool to see the balls spinning.)  You now have to set properties for the COM objects you added.  Right-click on each one in turn, select Properties, and you will see a pageframe, with General, Transaction and Security pages.  You are concerned with the Transaction and Security pages.  Figure 10 shows the Transaction page and here you should pick the “Requires Transactions” setting.  This means when the object is used, MTS will put it under transactional control and if there isn’t already a transaction in effect MTS will start one.  How could one already be in effect? Well, what if you have one component calling another?  Instead of starting a new transaction, MTS will put the new component under control of the same transaction.

The other settings, Requires a New Transaction, will start a new transaction whether or not one is already in effect.  This would allow you to have sub-transactions if needed.  Supports Transactions means simply that it supports them, and with this setting, the object will participate in a transaction started on the client machine, but otherwise will not start one.  Does not Support Transactions means the object will be instantiated without any transactional context.

Fig. 10. Setting transactional requirements

Testing your MTS Components

Now that you’ve created the COM object and placed it into a Package under MTS control, you need to test it (or use it).  This can be done from any application which supports creating COM objects, such as VB, Visual InterDev, C++, FoxPro or several others.  Since this is a session about VFP and MTS, I’ll simply test this from the VFP Command Window:

That’s really all there is to it.  You can create your object from any app and use it, without any thought that it was actually created in VFP and your app doesn’t even have to care that transactions are involved. MTS takes care of it all for you.

Configuring Client Machines

You use DCOM on client machines to access MTS on the server.  That means you need to be running Windows 95/98 or Windows NT (Workstation or Server) on the client machines.  You also need to make sure DCOM is actually installed on the client machine. 

Once you have DCOM installed on your client machines you need to register the COM components you intend to use on them.  The MTS Explorer allows you to create a client installation executable which does this for you.  In MTS Explorer choose the package you intend to use and click Action|Export Package from the toolbar.  Follow the directions, giving a temporary directory on your local hard drive and a filename for the resulting EXE.  You will end up with two files (a .PAK and .DLL) and a subdirectory called CLIENTS.  The CLIENTS directory will contain the EXE file that you should copy to and run on your client machines.  Do not run this EXE on your MTS server machine or you will really mess things up in the Registry.  This file is only for your client machine.  You will not need either of the other two files.

Uninstalling a remote configuration is even easier than uninstalling on the server.  We don’t even need to use regsvr32.  Go to the Control Panel and click on Add/Remove Programs.  All remote installations start with the prefix “Remote Application”.  Find your installation and click Add/Remove to remove it.


This session is not meant to be a tutorial on using ADO/RDS (especially since others are presenting it), but I introduce the technology here to help you get started in using it.  RDS is the Remote Data Services of ActiveX Data Objects.  The reason we talk about ADO/RDS here is because if you want to run your application over the Internet, the http protocol does not allow sending VFP data in tables.  There needs to be some sort of “common” method for sending the data.  This common method is Microsoft’s ADO technology. And it’s not only for the Internet… ADO can work with many different products. Visual FoxPro developers would do well to become comfortable with ADO.  (Did you know that ADO is based on the FoxPro Cursor Engine?)

In my sample code, I am actually doing things the hard way in terms of coding the translation to/from ADO.  What you really should do is get on Ken Levy’s web site ( and download the routines he makes available for free to work with ADO.  His code automates much of the process.  (Even though his stuff is free, I didn’t feel right using it for this presentation and sample code.). See the sample code for details of how this is implemented.

Available Tools

As you build more and more COM objects, and/or you install more and more commercial software, you will find COM objects all over the place.  How do you make sense of them?  It’s pretty much of a challenge, but if you have Visual Studio 6.0 (I think any version – not only Enterprise) you will have a tool in the Visual Studio 6.0 Tools group called “OLE View”.  In days before VS 6.0, this tool was included with Visual C++ but could also be downloaded from the Microsoft web site at

By using the OLE View tool, you can examine what COM (and other) objects are installed on your system.

Limitations and the Future

Unfortunately, it’s not a perfect world… At least not yet.  Currently when you use VFP to talk to MTS, only one user can be using any method of the COM object at any given moment.  This is described as “VFP blocks at the method level”.  Let’s say one user tries to execute a particular method which takes 10 seconds to execute.  Assuming no other user is currently using any method of the COM object, this user is able to execute the method.  But if during the time this method is executing, another user tries to execute the same or a different method, the second user is blocked from doing so and the user must wait until the other method finishes executing.

This will be addressed, and we have to wait for the next Service Pack for this to be fixed.  (Note that this will be Service Pack 2, as SP1 does not contain any fixes for VFP.)

You can expect to see VFP continue to evolve and improve especially in regard to MTS and COM technology in general.

Further Reading

There are not many books on the topic of MTS and the one most often recommended just happens to be a truly excellent book.  It’s in the Roger Jennings Database Workshop Series, and it’s simply called “Microsoft Transaction Server 2.0”.  The Series was edited by Roger Jennings, but the actual authors are Steven Gray and Rick Lievano.  The publisher is SAMS Publishing, and the ISBN number is: 0-672-31130-5.  This book reads so well in fact, with such clear explanations, that I would very much like to read other books in the Series.  The only complaint I have about this book is that nowhere in it are those books actually mentioned or listed.  But they keep talking about the “Series”.  <sigh>  This book is worth checking out.

There have also been some excellent articles published in recent months in FoxTalk Magazine by Pinnacle Publishing.  Look for articles by Gary Dewitt and Jim Farino.

Less than a day before the deadline for getting these notes in, I found another brand-new book on MTS, and while I have not yet read it, I had seen various chapters on the Internet and was impressed enough to want to buy it.  The book is called “Professional MTS and MSMQ with VB and ASP” by Alex Homer and David Sussman.  The publisher is Wrox Press and the ISBN is 1-861001-46-0.

To find out more about ADO and RDS, particularly from a VFP point-of-view, I recommend Rod Paddock’s new book, “Visual FoxPro 6 Enterprise Development”.  The publisher is Prima Tech, and the ISBN is: 0-7615-1381-7.  This is actually a great book for lots of VFP topics.

Microsoft also has a great web site dedicated to data… COM, ADO/RDS, etc. which you can find at  You can also download the latest version of MDAC (Microsoft Data Access Components) there.

Final Notes

MTS is changing quickly, and so is Visual FoxPro.  We are expecting a Service Pack soon to solve some of the problems we have currently.  (Note that in Visual Studio 6.0 Service Pack 1, there were no fixes for VFP.) Stay tuned to my web site ( ) for information on when the Service Pack becomes available and also for updated notes on this topic.  I will also endeavor to keep my articles there up-to-date.