Solved

Trying to import data from Btrieve into SQL Server

Posted on 2011-02-15
8
1,828 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now