Mal,
Since you have to return a recordset anyway, you have to pass that data over the wire. It is better (IMO) to have SQL Server send you the raw data to your local machine. If you are using ADO in your COM server, your recordset is built automtcally for you; no further need processing is needed.

If you execute your COM server remotely, it creates an ADO recordset when it invokes the SP and then it has to pass that to your application. In other words, the COM server sends the recordset across the wire insetad of SQL Server. You haven't gained anything. In fact, because of the multiple COM barriers and remote nature of the server, it will be slower (as Ed explained).

>Hi Ed:
>
>>>Question: I didn't mention that each program (2 from each workstation) polls MyCom.EXE every second. Will MTS delay response back to the workstations?
>>>
>>
>>Remote access of the COM server will introduce a significant delay compared to local out-of-process access, since each call must be marshalled across the wire to the server twice - one request inbound to the remote server, and at least one reply outbound back every time a method or property is referenced, so traffic over the wire introduces delays. And then comes the issue of servicing 40 requests in parallel - an MTDLL runs on a single processor instance. In general, MTS managed objects need to be stateless - they do not rely on the server maintaining the context of itself relative to each session, so in general, more information will need to be passed for each method call to reestablish the context of a transaction.
>>
>>I doubt that the issue of MTS will be as great as the communication latency and added cost of establishing or maintaining states. What does the COM server do that makes execution on the remote server attractive?
>
>Thanks for your reply.
>
>All the COM server does is run Stored Procedures (SQL SERVER 7) and return a RecordSet or a String.
>
>I was thinking if I can run the Stored Procedures on the server itself, it'd obtain, perhaps, a quicker response to the requests. Wrong?
>
>TIA
>
>Mal