RE: Quickest way to load VFP tables to MSSQL?
Vince Teachout wrote: Hello, I'm working under a deadline to transfer a DBC of VFP 9.0 tables to MS SQL server. Before I get too far into it, I thought I'd ask for better ideas than what I'm doing. DTS or if SS2005 SSIS job. If you do inserts for a lot of data then your log file will have it listed. I have taken in 300 meg of VFP data into SQL Server for a DW and it took under a min to pull the data and another 2 min to run the normalization procs to fit the OLAP system. Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 A good way to judge people is by observing how they treat those who can do them absolutely no good. ---Unknown http://spaces.msn.com/members/srussell/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.17.19/663 - Release Date: 2/1/2007 2:28 PM ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Quickest way to load VFP tables to MSSQL?
Stephen the Cook wrote: Vince Teachout wrote: Hello, I'm working under a deadline to transfer a DBC of VFP 9.0 tables to MS SQL server. Before I get too far into it, I thought I'd ask for better ideas than what I'm doing. DTS or if SS2005 SSIS job. If you do inserts for a lot of data then your log file will have it listed. I've finished the Job, using views and ODBC, and it works fine. Now that the pressure is off, I'll spend a little learning time next week looking into your's, John's and Rick's suggestions. Thanks! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Quickest way to load VFP tables to MSSQL?
Upsizing wizard is out of the question. I can highly recommend you test out the new *Sedna* Upsizing Wizard if the structures are the same (not sure if this was a reason for the constraint of if you were thinking the old Upsizing wizard stinks). It has moved several of my VFP DBCs and the data to SQL Server 2000 and SQL Server 2005 without any issues. You can test this (including the download and time) in less than a couple hours depending on the quantity of data you are moving. The Sedna components work fine on VFP 9 SP1 (no need to get SP2 involved). The XML Bulk updates are way faster than the remote view approach. Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vince Teachout Sent: Thursday, February 01, 2007 03:13 PM To: [EMAIL PROTECTED] Subject: Quickest way to load VFP tables to MSSQL? Hello, I'm working under a deadline to transfer a DBC of VFP 9.0 tables to MS SQL server. Before I get too far into it, I thought I'd ask for better ideas than what I'm doing. The constraints: Upsizing wizard is out of the question. Won't be able to use any MS SQL tools to pull the data in, it has to be a Push from VFP via ODBC. Have: VFP9 Tables may be assumed to be created correctly on the backend, with all rights Transfer will be Localhost My current idea is to create a temporary XFER DBC. Then I open the DBC containing the local tables to be transfered, Get the list of tables from aDBObjects(), and iterate through the tables. For each table: Create a REMOTE view on the fly on the backend table, with correct send updates, etc, etc. Set optimistic table buffering Append into the Remote view from the local table Issue tableupdate() Does this sound reasonable? Any faster/better way to do it? I know nothing at all about what cursoradoptors can do. Can they do the job, and if so, would the couple of hours I lose learning to use them pay off? (Need to deliver tomorrow) Would appreciate any ideas. I'll be here, working on the above in the meantime. :-) [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Quickest way to load VFP tables to MSSQL?
DTC? John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vince Teachout Sent: Thursday, February 01, 2007 2:13 PM To: Profox Subject: Quickest way to load VFP tables to MSSQL? Hello, I'm working under a deadline to transfer a DBC of VFP 9.0 tables to MS SQL server. Before I get too far into it, I thought I'd ask for better ideas than what I'm doing. The constraints: Upsizing wizard is out of the question. Won't be able to use any MS SQL tools to pull the data in, it has to be a Push from VFP via ODBC. Have: VFP9 Tables may be assumed to be created correctly on the backend, with all rights Transfer will be Localhost My current idea is to create a temporary XFER DBC. Then I open the DBC containing the local tables to be transfered, Get the list of tables from aDBObjects(), and iterate through the tables. For each table: Create a REMOTE view on the fly on the backend table, with correct send updates, etc, etc. Set optimistic table buffering Append into the Remote view from the local table Issue tableupdate() Does this sound reasonable? Any faster/better way to do it? I know nothing at all about what cursoradoptors can do. Can they do the job, and if so, would the couple of hours I lose learning to use them pay off? (Need to deliver tomorrow) Would appreciate any ideas. I'll be here, working on the above in the meantime. :-) [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Quickest way to load VFP tables to MSSQL?
Rick Schummer wrote: Upsizing wizard is out of the question. I can highly recommend you test out the new *Sedna* Upsizing Wizard if the structures are the same (not sure if this was a reason for the constraint of if you were thinking the old Upsizing wizard stinks). It has moved several of my VFP DBCs and the data to SQL Server 2000 and SQL Server 2005 without any issues. You can test this (including the download and time) in less than a couple hours depending on the quantity of data you are moving. The Sedna components work fine on VFP 9 SP1 (no need to get SP2 involved). Well, at this point I'm 90% done. Wrote the utility, it works fine, just need to replace hard-coding with vars to make it flexible, then iterate through my DBC and call it. But does the Sedna handle the actual transfer of data as well, and would I be able to write a stand-alone utility with it that I can send to the client (who only has the vfp9 runtimes)? If so, I'll take a look. Thanks! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Quickest way to load VFP tables to MSSQL?
john harvey wrote: DTC? Bless you! :-) (I know not of this DTC. I am but a simple caveman programmer. Your scientists found me in a block of Ice and thawed me out. This DTC frightens and confuses me) :-( ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Quickest way to load VFP tables to MSSQL?
But does the Sedna handle the actual transfer of data as well, and would I be able to write a stand-alone utility with it that I can send to the client (who only has the vfp9 runtimes)? If so, I'll take a look. Thanks! The new Upsizing Wizard uses the XML Bulk inserts to move the data. Doug Hennig blogged about the performance increases: http://doughennig.blogspot.com/2006/07/sqlxmlbulkload-rocks.html I don't know if the licensing for the Sedna components has been released, but they are part of Xsource and the licensing agreement for that allows developers to make changes and release them. As far as running it in a VFP executable, it should not be hard to do. There is a separate engine component too so you can automate it programmatically to your liking. You do not need the wizard interface. Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vince Teachout Sent: Thursday, February 01, 2007 05:07 PM To: [EMAIL PROTECTED] Subject: Re: Quickest way to load VFP tables to MSSQL? Rick Schummer wrote: Upsizing wizard is out of the question. I can highly recommend you test out the new *Sedna* Upsizing Wizard if the structures are the same (not sure if this was a reason for the constraint of if you were thinking the old Upsizing wizard stinks). It has moved several of my VFP DBCs and the data to SQL Server 2000 and SQL Server 2005 without any issues. You can test this (including the download and time) in less than a couple hours depending on the quantity of data you are moving. The Sedna components work fine on VFP 9 SP1 (no need to get SP2 involved). Well, at this point I'm 90% done. Wrote the utility, it works fine, just need to replace hard-coding with vars to make it flexible, then iterate through my DBC and call it. But does the Sedna handle the actual transfer of data as well, and would I be able to write a stand-alone utility with it that I can send to the client (who only has the vfp9 runtimes)? If so, I'll take a look. Thanks! [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Quickest way to load VFP tables to MSSQL?
I always preferred the brute force method.. Something like this if my memory works correct Use ODBC Sqlconnect() Input string = Insert into ??? Scan sqlexec(input string) select table endscan Virgil Bierschwale http://www.bierschwalesolutions.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vince Teachout Sent: Thursday, February 01, 2007 4:10 PM To: ProFox Email List Subject: Re: Quickest way to load VFP tables to MSSQL? john harvey wrote: DTC? Bless you! :-) (I know not of this DTC. I am but a simple caveman programmer. Your scientists found me in a block of Ice and thawed me out. This DTC frightens and confuses me) :-( [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Quickest way to load VFP tables to MSSQL?
The DTC takes a fraction of the time of other update methods. If it's a one time update, use the DTC wizard. If it is an ongoing deal, look at saving the scripts. The data to be updated needs to be on the same server as the SQL Server for it to work as fast as possible. John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Virgil Bierschwale Sent: Thursday, February 01, 2007 5:29 PM To: 'ProFox Email List' Subject: RE: Quickest way to load VFP tables to MSSQL? I always preferred the brute force method.. Something like this if my memory works correct Use ODBC Sqlconnect() Input string = Insert into ??? Scan sqlexec(input string) select table endscan Virgil Bierschwale http://www.bierschwalesolutions.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vince Teachout Sent: Thursday, February 01, 2007 4:10 PM To: ProFox Email List Subject: Re: Quickest way to load VFP tables to MSSQL? john harvey wrote: DTC? Bless you! :-) (I know not of this DTC. I am but a simple caveman programmer. Your scientists found me in a block of Ice and thawed me out. This DTC frightens and confuses me) :-( [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
