VFP 6.0/SQL 7.0 Replication & Reporting Application 
Author Message
 VFP 6.0/SQL 7.0 Replication & Reporting Application

Sorry for the long post, but there are multiple VFP/SQL issues...

We currently have a VFP 6.0 data replication/ad hoc reporting
application with about 70 users.  Many of them have long since passed
the VFP 2GB/table limit (argggh!!!  Bill - where WERE you???) and are
manually splitting up their initial load (whole enchilada) and journal
update (weekly/biweekly or monthly .CSV updates), and then modifying
their queries with Union statements (ugly & cumbersome, but necessary
in VFP).  We're in the process of creating a user-friendly SQL Server
7.0 solution for those clients who need to expand their storage limits,
which we'd like to maintain easily in conjunction with our VFP product,
which we'd also like to change as minimally as possible on the
reporting application side.

Yes, I realize we don't live in Kansas...but I still believe in OZ.

Here are our goals:
Short-term:
1) To automate as much as possible the creation of a SQL Server 7.0
database from whatever source is fastest to develop (.CSV or .TSV
initial load, or upsized or imported VFP data), correctly converting
all data (dates especially) as needed.
2) To do the same with periodic journal updates:  adding, changing or
deleting data as needed using our COBOL-generated .CSV or .TSV update
files.
3) To enable our clients to speedily access their data with little
training, expense or maintenance on their part.
4) We have heavily-indexed tables in VFP as well, which we'd like to
optimize for SS7 and maintain as painlessly as possible.  Will Index
Tuning Wizard(!) help as much as I'm expecting it to...which is a lot?

Next:
1) To enable parallel Beta-testing in both VFP & SQL 7.0
2) To simplify conversion of VFP 6.0 programs to read SQL 7.0 data.
3) To use Seagate's Crystal Reports to hit the SQL database (another
forum perhaps...but any takers here?)
4) POSSIBLY: To allow our clients the option of replicating data
without a VFP database in the middle.

Other considerations:
1) We are VFP & COBOL-healthy and SQL 7.0 (& VB) -...challenged.
2) Upsizing wizard in VFP isn't an ideal solution since we have
periodic journal updates to contend with...right?
3) We have a very short time-frame with which to develop the
replication engine (THAT's a surprise, huh?)

Questions (FINALLY!):
1) Any 'Show-stoppers' that you can see to any of our goals?
Recommendations on any portion?  CAN we do this in my lifetime (which
ends next week, if we're not past 'short-term goals')
2) Although I've created a SQL Server database structure mirroring our
60-odd VFP tables (except for dates), I've had minimal luck populating
them directly from .CSVs or .TSVs due mostly to the DTS conversion of
dates.  (We had EMPTY dates in VFP, not NULLs, so the DTS used
12/30/1899 for them.  I wrote a Q&D to set them to NULLs, but of course
now EMPTY() doesn't work in VFP & we'll have to add ISNULL()
everywhere.)  I've had much better luck importing to SQL Server from
VFP 6.0 tables, but don't want to force our clients to maintain both.
(Plus, the date conversion problem is ongoing).  PLUS:  The Import
wizard 'hangs' (without error) after zero-to-partial loads
(varies)...any clue how to proceed next?
3) Is there a reliable non-VB solution for creating a sustainable DTS
package that would read comma- or tab-delimited tables directly and
convert dates to SQL's DATETIME format (all we really want is date, but
SMALLDATE isn't big enough.)
3) What's the best way to Update the SS7 database using our
existing .CSV & TSV tables, all of which mirror the table structure
they update EXCEPT for an initial added field which is either an A, C
or D (for Add, Change, or Dismember...uh, make that 'Delete')
4) Can I easily set all my primary keys for indexing, then run through
a number of sample queries & let the Query Tuning Wizard do its thing?
Can our clients update easily using this method?

OK, this is too long a post already, & I apologize in advance - you're
a new resource to me & I'm being greedy.  Please feel free to respond
independently to any portion of this, and I'd be grateful rather than
hurt if you have any solutions that work better than mine.


THANKS!
- Steve

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Fri, 11 Apr 2003 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. VFP 6.0, SQL Server 7.0, & Identity Fields

2. VFP 6.0 & SQLSEVER 7.0

3. Help-me I have a problem with SQL 7.0 and VFP 6.0

4. VFP 6.0 -> SQL 7.0

5. old newbie and sql edit window in VFP 6.0 or SQL editor for VFP 6.0

6. VFP 7.0 & Activex w/Reports

7. Tahoe-VFP 6.0 - Replication

8. visual foxpro 6.0 and SQL Server 7.0 DTS files

9. Visual FoxPro 6.0 + SQL-server 7.0

10. VFP 6.0 dont understand type ntext of MSQL 7.0

11. VFP 6.0 dont understand type ntext of MSQL 7.0

12. VFP 6.0 / 7.0 - Date and Time Picker

 

 
Powered by phpBB® Forum Software