I agree ...

I've already changed the existing method of implicit SQL Server Transactioning to explicit.

The next step (Monday AM) is to wrap the views up using VFP Transactioning as well.

The existing implicit method of transactioning does not return the server's transaction level with the following:

SQLEXEC( nConnectionHandle, "SELECT @@TRANSLEVEL", "cTest" )

call to the server ... changing implicit to explicit like so:

SQLEXEC( nConnectionHandle, "BEGIN TRANSACTION" )

causes the server to return 1 ... as expected.

Another thing that I found alarming after the VFP Conference is how many speakers made the statement "Remotes views cannot be transactioned using SQL Server" ... the experts were, obviously, very wrong. What they may have meant to say or interpreted what I was asking is that SQL Server does not support NESTED transactions. Here is how I discovered this and how this fact really manifests itself in VFP through SQL Server.

After changing the implicit transactioning in MM to explicit and being able to query the server as to it's exact transaction level I conducted the following test (in a suspended state in the middle of a save).

I issued the following commands back to back:

SQLEXEC( nConnectionHandle, "BEGIN TRANSACTION" )
SQLEXEC( nConnectionHandle, "SELECT @@TRANSLEVEL", "cTest" )
"cTest" contained a 1

SQLEXEC( nConnectionHandle, "BEGIN TRANSACTION" )
SQLEXEC( nConnectionHandle, "SELECT @@TRANSLEVEL", "cTest" )
"cTest" contained a 2 ... ooh ... I got excited here.

I then issued the following command
SQLEXEC( nConnectionHandle, "ROLLBACK" )
SQLEXEC( nConnectionHandle, "SELECT @@TRANSLEVEL", "cTest" )
"cTest" contained a ... tada ... 0 ... sigh

The rollback coming from VFP caused SQL Server to rollback ALL of the nested transactions to 0. Therefore, the rule of thumb appears to be, "You can't exert very granular control over transactions in SQL Server using remote views" ... you probably have to use SQLPassThrough.

Take care,

CTBlankenship