Greetings!

My application will usually be used with four offline views of data that is stored in SQL Server. Once a user is done, the files will be returned to us and the views will be brought back online, automatically uploading the data to SQL Server. This works fine.

But what if it doesn't work fine? What if three of the views upload successfully and the fourth bombs? I tried wrapping the uploads in SQLExec() calls to create a transaction and then roll it back, and data gets uploaded anyway.

The views are all created from the same connection and are sharing it. I use DBSETPROP() to get the name of the connection a view is using and then to get the connection string for that connection. Then, I call SQLSTRINGCONNECT() with the second argument set to .T. to use the same connection the views should be using. Then I issue SQLExec(lnSqlHandle, 'BEGIN TRANSACTION'). Then I bring the views online by issuing USE ViewName ONLINE EXCLUSIVE followed by TABLEUPDATE(). Then I issue SQLExec(lnSqlHandle, 'IF @@TRANCOUNT > 0 ROLLBACK'). Once all this completes, I look at my SQL Server table and I see that the data that should have been rolled back was there.

Is what I am trying to do possible? If so, what am I doing wrong?

Thanks very much!