locked
Upgrade Visual Foxpro/SQL Server 2000 database to SQL Server 2012 RRS feed

  • Question

  • I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.

    Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?

    Tuesday, August 4, 2015 3:21 PM

Answers

All replies

  • That's weird to not have FK relationship in the SQL Server. Are you sure it's true?

    Do you have access to the FoxPro code application? Are you going to study the source code to figure out the relationship? Do you have any documentation for the DB used?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 4, 2015 3:39 PM
    Answerer
  • No, I am not 100% sure of my facts.  It is all second hand info at the moment.  A SQL Server 2000 development server is being set up with the data on it, that I will have access to tomorrow morning (UK time) so I will know more then.  I strongly suspect that I will not have access to the FoxPro code, at least for another week or so until the HR director returns from holiday and authorises me to have full access to the system.  I was hoping to make, at least some, progress in understanding the data before she returns.
    Tuesday, August 4, 2015 3:51 PM
  • Start from looking at the database and may be making Database Diagram if there is not one. If the tables have good naming conventions, you may also try to figure out the relations by analyzing table and column names.

    To find all existing relations you may try code from this article (turned among the first in my search):

    https://technet.microsoft.com/en-us/library/Aa175805%28v=SQL.80%29.aspx?f=255&MSPPError=-2147217396


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 4, 2015 4:09 PM
    Answerer
  • Thanks!  I will try that when I get my hands on the SQL Server 2000 database.  The present administrator of the system is leaving in the middle of September and is attempting to create an ERD of the system before he leaves, although he will probably not complete it.  So I have some idea of the relationships but I would like to get all of them into SQL Server 2012 so that I can use the tools there to create better SSRS reports.
    Tuesday, August 4, 2015 4:30 PM
  • Just had my first look at the data and, sure enough, there are no foreign keys.  Also any FoxPro code is only on the live server which, at the moment I do not have access to.  Apparently, this is common for FoxPro systems that have been upgraded. 

    There seem to be tables in SQL Server containing system information such as indexes and, possibly, keys.  There is a concept of unique identifiers and parent identifier that I assume equates to primary and foreign keys but the identifiers have their own structure and it would surely be a very large amount of work to work out how they are applied and turn them into an ERD or, even better, directly into SQL Server keys.  I am hoping that there is a tool or tools that could do it for me - perhaps a case tool that can reverse engineer FoxPro structures.  The problem is that this is no longer purely a FoxPro system which would use .dbf files to store the data.

    Any inspired thoughts would be greatly appreciated.

    Wednesday, August 5, 2015 12:54 PM
  • If the FKs do not exist in the database, there is no way to get them.  You will need to infer them by reading the code.
    Wednesday, August 5, 2015 3:36 PM
    Answerer