Solved

Trying to import data from Btrieve into SQL Server

Posted on 2011-02-15
8
1,933 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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