VFP is not "automatically" putting the passthrough queries in a transaction. In SQL Server, every query is part of a transaction. Now that transaction can take one of three froms: auto-commit, explicit, or implicit.
The "automatic" transaction that you refer to is the auto-commit. Each query is a transaction. When all the row have been modified, the transactions is committed - automatically.
When you changed your stored procedure to explicitly use a BEGIN TRAN and COMMIT TRAN, you're using an explicit.
The last time I checked, VFP implemented "manual" transaction using SQL Server implicit transaction setting. When you enable implicit transaction, an explicit transaction is automatically started whenever a DML query is submitted to the server. The transaction is in place until the user explicit terminates it with either a COMMIT or ROLLBACK. The fun begins when the next DML query is submitted, which automatically starts another explicit transaction. Hence the reason that you need to disable VFP's manual transactions.
-Mike