We are trying to convert some of our data into SQL server. Most of our data is in free tables. We have tried using the VFP upsizing wizard but had database compatibility issues, worked with Toad SQL and tried importing in SQL Server Management Studio. Is anyone who has does this successfully willing to share any insights?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Server Conversion
- Thread starter tkee
- Start date
Mike Gagnon
Programmer
We are doing it successfully, and we have for years, although I cannot post code in a public forum, since it is proprietary, I can give pointers. If you want contact me at mike.gagnon[at]Umana.ca
Other than that, you may know that you can import a .csv file into an SQL table.
If you want to get the best response to a question, please check out FAQ184-2483 first.
Other than that, you may know that you can import a .csv file into an SQL table.
If you want to get the best response to a question, please check out FAQ184-2483 first.
Mike Lewis
Programmer
Upsizing VFP data to any back-end database (not just SQL Server) is more than simply importing it. You normally have to make decisions about the design of the target database.
In particular, you need to think about your data types. For example, you might need to convert VFP date field to SQL Server datetimes. With integer data, you have to think about whether you want to convert to bigint, int, smallint or tinyint. And with character fields, you have to decide between char and varchar. These are all quite a crucial decisions, and not ones that can be automated.
Other decisions concern SQL Server's IDENTITY property, the use of NULLs, and the design of your indexes.
So think carefully about the design of your SQL Server data before you start worrying about the mechanics of the conversion.
I have been involved in upsizing several VFP databases to SQL Server. I have always found that the best approach is to write a custom upsizing program (in VFP). Essentially, this uses CREATE TABLE commands to create the tables in the target databases, then code using SQL Pass-through to actually move the data across. This is typically more work than using an automated tool like the VFP upsizing wizard, but it is the best option if you want to end up with a trouble-free conversion.
Of course, your scenario might be different than mine.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
In particular, you need to think about your data types. For example, you might need to convert VFP date field to SQL Server datetimes. With integer data, you have to think about whether you want to convert to bigint, int, smallint or tinyint. And with character fields, you have to decide between char and varchar. These are all quite a crucial decisions, and not ones that can be automated.
Other decisions concern SQL Server's IDENTITY property, the use of NULLs, and the design of your indexes.
So think carefully about the design of your SQL Server data before you start worrying about the mechanics of the conversion.
I have been involved in upsizing several VFP databases to SQL Server. I have always found that the best approach is to write a custom upsizing program (in VFP). Essentially, this uses CREATE TABLE commands to create the tables in the target databases, then code using SQL Pass-through to actually move the data across. This is typically more work than using an automated tool like the VFP upsizing wizard, but it is the best option if you want to end up with a trouble-free conversion.
Of course, your scenario might be different than mine.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
TamarGranor
Programmer
In addition to the other comments, are you using the updated Upsizing Wizard? You can get it at
Tamar
Tamar
- Thread starter
- #5
I have been given two weeks while performing my regular duties to do this probject with no background at all in SQL Server. I think I now have an idea of how to start and will ask if they want garbage or data which I will need time to do correctly. Thanks for your help and suggestions.
Olaf Doschke
Programmer
Yes, it actually won't matter what quantity of data you need to convert, you'll not do this with copy&paste anyway. The number of tables is already more relevant, you will write a small portion of general conversion code, a larger portion per table, but the rarest code I wrote was about single records. Indeed there are some system tables with just 2 records switching between two very distinct worlds and I had migration of data where the relationship with one of two records meant two separate data migration paths. So potentially such distinctions cause a multiplication factor of work.
You need time for the learning curve ahead. the data quantity will just determine whether you manage to do an overnight transition or not. But two weeks overall conversion time? I'd say forget it, I'd even say that no matter if it's an appliction just having a single main form and table or not. Forget it.
Bye, Olaf.
Olaf Doschke Software Engineering
You need time for the learning curve ahead. the data quantity will just determine whether you manage to do an overnight transition or not. But two weeks overall conversion time? I'd say forget it, I'd even say that no matter if it's an appliction just having a single main form and table or not. Forget it.
Bye, Olaf.
Olaf Doschke Software Engineering
- Status
Similar threads
- Locked
- Question