Solved

Trying to import data from Btrieve into SQL Server

Posted on 2011-02-15
8
1,893 Views
Last Modified: 2012-05-11
Hi
I am have been give 156 btr and 4 ddf files. I have been asked to extract the data and export this into either MS Access or SQL Server. Unfortunately, I have no experience of Btrieve and have no access to the Btrieve server / Application used to extract these files. I do have the following information: btrieve version 6.15.445 on Netware.

I have so far, downloaded a trial version of the Pervasive software version 10.30.017.000 and converted the btr files to 9.5. I have also used the Scalable DDF utility to upgrade the DFF files from version 3 to version 4 as requested by the Pervasive application. I have then created a new database and pointed it to where the files are stored. Unfortunately, no tables are shown in the database.the DDF files show up though. I have tried updating the file.ddf file but keep getting the following error message: [LNA][Pervasive][ODBC Engine Interface][Data Record Manager]The owner name is invalid(Btrieve Error 51)

Any suggestions please?
Thanks
0
Comment
Question by:Harvwar
  • 4
  • 4
8 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 34898077
If X$File/X$Field/X$Index show up, but nothing else does, then you may have been provided with EMPTY DDF's.  Start the Pervasive Maintenance Utility and go to Options/Create Stat Report and do a STAT on the FILE.DDF.  If you see only 3 or 4 records there, then your DDF's are indeed empty and useless to you.  (You can also do this from a command prompt with the command "BUTIL -STAT FILE.DDF" and looking at the Number of Records line.)

If your DDF's are empty, then go back to the user or developer and request correct DDF's.  With them, this task is trivial -- and you already have taken all of the steps needed.  Without them, this task is VERY non-trivial.  

Now, one more thing -- if you created a new database and did NOT already have the right DDF's in the folder, then the database engine would have created new ones for you -- probably ones that had 8-10 tables defined in it -- all X$.... tables.  You may then be able to delete this "new" set of DDF's and copy your original DDF's into the correct location.  It all depends on exactly how you created the database wand what was where.  However, the STAT report on the FILE.DDF will give you a big clue -- if it shows less than 10 records, then the DDF's are indeed empty.

FYI, there are a few more papers on accessing Pervasive databases here:
    http://www.goldstarsoftware.com/press.asp
but it looks like you already know how to do it.
0
 

Author Comment

by:Harvwar
ID: 34898249
Hi and Thanks for your reply. Unfortunately, I have no access to the developer  and the users are not IT literate (they are lawyers). Is there a way of importing these files into either access or sql? The STAT report has the following:

File Statistics for c:\btr_test\new\ddf\file.ddf

File Version = 9.50
Page Size = 1024
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 13
Record Length = 97  
Record Compression = No
Page Compression = No
Variable Records = No

Available Linked Duplicate Keys = 0
Balanced Key = No
Log Key = 0
System Data = No
Total Number of Keys = 2
Total Number of Segments = 2

Key         Position        Type            Null Values*               ACS
    Segment          Length           Flags              Unique Values
  0    1         1       2  Unsigned            --                13    --
  1    1         3      20  String     I M      --                13    --

Legend:
 < = Descending Order
 D = Duplicates Allowed
 I = Case Insensitive
 M = Modifiable
 R = Repeat Duplicate
 A = Any Segment (Manual)
 L = All Segments (Null)
 * = The values in this column are hexadecimal.
?? = Unknown
-- = Not Specified


Thanks.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 34898461
These are not the older DDF's, but rather the brand new ones that the database engine created.  Did you say that you had old ones (the set with the 4 files)?  Try a BUTIL -STAT on that FILE.DDF and let me know how many records are in THAT copy.

Here's the exact paper which explains in more detail exactly what you are trying to do and why it is hard:
    http://www.goldstarsoftware.com/papers/AccessingBtrieveDataFromODBC.pdf
As you see in the paper, without DDF's, there is no way to access the data relationally, and thus no way to get to it from Access, either.  You'll either need to create the DDF's yourself or have someone do it for you.  Without detailed data structure documentation or source code, this is (again) a non-trivial exercise.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Harvwar
ID: 34898848
Hi Billbach Please see details of request below:
File Statistics for c:\btr_test\data\ddf\file.ddf

File Version = 6.00
Page Size = 512
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 4
Record Length = 97  
Record Compression = No
Page Compression = No
Variable Records = No

Available Linked Duplicate Keys = 0
Balanced Key = No
Log Key = 0
System Data = No
Total Number of Keys = 2
Total Number of Segments = 2

Key         Position        Type            Null Values*               ACS
    Segment          Length           Flags              Unique Values
  0    1         1       2  Unsigned            --                 4    --
  1    1         3      20  String       M      --                 4     0

Alternate Collating Sequence(ACS) List:
  0 UPPER  

Legend:
 < = Descending Order
 D = Duplicates Allowed
 I = Case Insensitive
 M = Modifiable
 R = Repeat Duplicate
 A = Any Segment (Manual)
 L = All Segments (Null)
 * = The values in this column are hexadecimal.
?? = Unknown
-- = Not Specified
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 34898992
As I thought -- empty.  May as well just delete them, as they are doing nothing except taking up disk space.
0
 

Author Comment

by:Harvwar
ID: 34900845
Hi BillBach, Thanks for all the help!

I just looked at the file dates on the DDFs and realised that FILE.DDF was overwritten when I created a database on PSQL10 (the originals are sitting on an old Novell server)

The original file BUTIL-STAT looks like:

Btrieve Maintenance Utility 10.30.017.000
Copyright (C) Pervasive Software Inc. 2009
All Rights Reserved.

File Statistics for F:\PDS\Data\DDF\FIELD.DDF

File Version = 6.00
Page Size = 512
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 159
Record Length = 32  
Record Compression = No
Page Compression = No
Variable Records = No

Available Linked Duplicate Keys = 0
Balanced Key = No
Log Key = 0
System Data = No
Total Number of Keys = 5
Total Number of Segments = 8

Key         Position        Type            Null Values*               ACS
    Segment          Length           Flags              Unique Values
  0    1         1       2  Unsigned            --               159    --
  1    1         3       2  Unsigned      D     --                 4    --
  2    1         5      20  String       MD     --               159     0
  3    1         3       2  Unsigned     M      --               159    --
  3    2         5      20  String       M      --               159     0
  4    1         3       2  Unsigned     MD     --               159    --
  4    2        26       2  Unsigned     MD     --               159    --
  4    3        30       1  String       MD     --               159    --

Alternate Collating Sequence(ACS) List:
  0 UPPER  

Legend:
 < = Descending Order
 D = Duplicates Allowed
 I = Case Insensitive
 M = Modifiable
 R = Repeat Duplicate
 A = Any Segment (Manual)
 L = All Segments (Null)
 * = The values in this column are hexadecimal.
?? = Unknown
-- = Not Specified

The command completed successfully.
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 34901020
Wonderful.   Delete all of the DDF files that the system created in the directory, and then copy THOSE DDF's into the folder.  Then, open it with the PCC, and you should see all of your tables there.  

You can either use the PCC to export the data (right-click on the table and select Export Data), or you can use any ODBC-compliant tool to directly access the new database and read the data directly.  SSIS/DTS will do the job nicely if you are bringing the data into SQL Server.
0
 

Author Comment

by:Harvwar
ID: 34905378
Hi BillBach. Thanks for yor help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question