Ideas for VFP SQL server application

[VBFOXDEV35]

All:
I have just created a master asset file table in SQL server of about 1 million records (15 fields) for my company. They want to keep track assets for a given 2 years and add new ones as we move along.

The program that updates the assets is in VFP using ADO. The new assets are in a VFP table and I use ADO to update the SQL server table. My testing (on the SQL table being 250,000 test records) was successful using the following code:

oADORs = CREATEOBJECT("ADODB.Recordset&quot
oADORs.ActiveConnection = oADOConn
oADORs.Source = "Select * from .dbo.tblMasterLoans"
oADORs.CursorType = 3
oADORs.LockType = 3
oADORs.Open
DO WHILE NOT EOF()
oADORs.AddNew
oADORs.Fields("AssetNumber&quot.Value = ALLTRIM(strtran(t1lnnu," ","&quot)
oADORs.Update
skip
enddo

But now since the database is about 1 million records, I am getting a timeout error in VFP.

I guess my real question is, should I forgo the ADO, create an XML file of the VFP table, and then create stored procedures in SQL server to add the data?

Or is there another way of doing this easily?

Thanks all for those who reply.

Art
Art DeGaetano II
Software Developer, MOUS

 

[Mike Lewis]

Art,

Personally, I would not use ADO in this case. I would go straight to SQL Server using SQL pass-through (that it, SQLCONNET(), SQLEXEC(), etc.). It should be more efficient.

However, no matter what access mechanism you are using, it does not make sense to download a million records into your application. The essence of good client-server design is to work with the smallest possible sub-set of records at one time. You certainly don't need to have the full table in your workstation memory in order to process it the way you describe.

If you can change the design, that should solve the timeout problem. However, it is also possible to increase the timeout parameter - you do that in the Connection dialogue within the DBC. But doing that is not a substitute for good design.

Hope this helps.

Mike
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[VBFOXDEV35]

Mike, thanks for the advice.

The problem I am running into is I have a web site in .NET that I created to track these assets.

I have users that can get a request to look at an asset that was done 3 years ago. So they go to the site, type the number in, and information is returned along with a link to the location on the server where this particular deal is.

I know this sounds strange, but the reason for this is I have about 3400 seperate clipper systems on a server that contain asset information. So the reason for a repository system is to have these files in one place to easily find the asset and then return information and a link.

I hope this clarifies what I am trying to accomplish.

Art Art DeGaetano II
Software Developer, MOUS

 

[Mike Lewis]

Art,

OK, I understand what you are trying to do. But I still don't see any reason for you to download such a large number of records. It looks to me like you just need to grab the records relating to the asset under enquiry.

Mike
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk