>>>I have a MS SQL 7 database and I need to change one table and delete many records.
>>>
>>
>>>Can I just use a remote view in Foxpro, edit the records and delete the ones I do not want??
>>>
>>
>>Parameterize your remote view so that it retrieves ONLY the records you want to delete.
>>
>>REQUERY("rv_persons")
>>DELETE ALL
>>CURSORSETPROP("Buffering", 5)
>>TABLEUPDATE(1) && all rows
>>
>>>Then do I need to PACK the SQL table????
>>>
>>
>>Nope. SQL-Server doesn't store records with a deleted mark like VFP. When you delete them, they're gone for good.
>>
>>
>>>How I can I load an SQL table without it taking 30 minutes to load??
>>>I setup a remote view to the table, but when I open it, it locks my machine until all of the records load and that is at least 30 minutes.
>>>
>>
>>See above about parameterizing the remote view. If the result set is large and it will take a while to retrieve them, either break up the retrieve-delete into smaller subsets, or adjust the timeout intervals in your VFP connection into SQL-Server.
>>
>>These are the simplest ways, but there are others. You can alter the VFP connection to process asynchronously, which would free up Fox during processing, but you have to query SQLMORERESULTS() to determine when the query is done. Use can use SQL pass-thru either synchronously or asynchronously. Another very effective technique is to create a SQL view (inside SQL server) and then access it just like a SQL table with a VFP remote view. Your remote view can be parameterized, just like views on any other "table." This approach allows you to design visually in both VFP and SQL Enterprise manager, and get good performance.
>>
>>>THANKS
>>>Joel
>>>Houston, TX
>
>Good points about table buffering, parameters, SQL Server views, etc. My $.02 is to check out the view setting that just returns the 1st n of the data set.
>
>Bruce, do you use transactions in Mere Mortals to coordinate parent-child updates on a SQL Server back end? What methods have you employed?
>
>Cheryl

Hi, Cheryl,

Here's an excerpt from the VFP6 docs:

= SQLSETPROP(gnConnHandle, 'Transactions', 2) && Manual transactions
= SQLEXEC(gnConnHandle, "INSERT INTO authors (au_id, au_lname) ;
VALUES ('aupoe', 'Poe')")
= SQLCOMMIT(gnConnHandle) && Commit the changes

SQLSetProp() implicitly issues a BEGIN TRANS inside SQL Server This is not clearly explained in the help text. Note that SQL transactions are *per connection.*. Conversely, the VFP BEGIN TRANS ... END TRANS command is in effect for ALL cursors in the active datasession. If you have only one SQL server database to work, and if all remote views share the same connection, the code above will do the job. However, if you are using multiple SQL databases, you'll need to declare and commit/rollback the transaction in EACH connection. To make things even more interesting, say you have a single logical "transaction" involving updates to SQL-Server tables, Oracle tables, and Fox tables, you need to do something like this:

= SQLSETPROP(gnSqlHandle, 'Transactions', 2)
= SQLSETPROP(gnOracleHandle, 'Transactions', 2)
BEGIN TRANS && works on Fox data

I have wrapped these commands into custom methods of cBaseForm named .AllBeginTrans(), .AllCommitTrans() and .AllRollbackTrans(). These methods basically loop through all open cursors in the data session. Their respective start/commit/rollback is done in each remote connection that has updatable view(s) into it. If there are 1 or more updatable local views (Fox data), a single BEGIN TRANS is issued to handle them all.

This code predates Mere Mortals by at least 18 months, as I developed my own "one off" customization of the original Codebook 3 classes.

I did this so that SQL transactions would work regardless of whether remote connections are shared, of if multiple connections are utilized in the same datasession. This allows me to change a shared connection to a separate one without having to alter transaction handling code. This approach was designed also to handle "heterogenous" logical transactions against Fox and multiple types of remote data with no special coding in business objects or elsewhere.

This isn't the only possible solution, but you asked what I have done, and this is it. Let me know if you find flaws, or a better solution. I'm always willing to improve on ultra-reusable routines. <g>

- Bruce