SQL Remote View Limit?

[JICGreg]

I have been using remote view to update data from foxpro to sql. My sql database has grown large (just under 2gb) and nearly 700,000 records.

I'm now having trouble updating the sql program with the process I have been using for over six months. I'm wondering if there could be some sort of size limit on views that is kicking in.

When I issue tableupdate() it works for a while and then I finally get an error code that sames ...tmp file is too large. When I go to help it tells me about a 2gb limit. None of my foxpro dbc or tables would be anywhere close to that but if the sql table was included, then I would probably violate that.

Thanks for any input.

 

[Mike Lewis]

This is almost certainly because you have reached the 2 GB limit on the back end table. It's true that the limit doesn't apply to (non-FoxPro) database servers, but the point is that your remote view is creating a temporary cursor within VFP, and that cursor has the same 2 GB limit as an ordinary DBF.

The root of the problem is that the remote view is fetching the entire back end table into VFP. This not only creates the error you are seeing, but is in general bad client-server design.

The remote view should have a WHERE clause which limits the records to those that you actually need for the process in hand. If you are only using the view to send an UPDATE, it might be better to use SQLEXEC() for that.

But in any case, you should definitely add a WHERE clause to the view to limit the number of records. Also, the view should only fetch the actual fields that you need.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business