Troubleshooting

BTR2SQL allows you to generate two types of reports to trace errors that can occur during the migration process. The migration report is a comprehensive report summary that you can use to determine whether or not the migration was successful. If any inconsistency is encountered, the report displays where the error occurred.

Another method of reporting that BTR2SQL uses is a low-level driver trace that you can enable by setting either environment variables or registry values.

External tools can also be downloaded to assist in troubleshooting.

Migration Report

The Migration Report is a summary report that displays any errors that may have occurred during the last migration, the license and version of the migration tool, a table creation report, an index creation report, and a data migration report. You can generate multiple migration reports in one file for viewing multiple migration reports and then clear the report to start a new migration report (see Setting Migration Preferences).

At the top of the report, the file displays the driver version and the license number. Next, the file displays the date and time the report was generated and the user name of the user who generated the report. A sample migration report is shown in Figure 27.

Select Report | Migration Report.

==============================================================================
Mertech Data Systems, Inc
Database Migration Report
SQLBTR 5.0.5914.0

Licensed to: Evaluation License
------------------------------------------------------------------------------

Report Generated on: 12/18/2012
At: 10:24:30 AM
By: sa
For Server: GATEWAY\sqlexpress Database/Tablespace: tempdb
DDF Location: C:\ProgramData\Pervasive Software\PSQL\Demodata
------------------------------------------------------------------------------
REPORT SUMMARY

Errors: No errors

No Errors in Table Creation
No Errors in Index Creation
No Errors in Data Migration
                                       TABLE CREATION REPORT

------------------------------------------------------------------------------

Table Name   Status             Error Log

------------------------------------------------------------------------------

CLASS        * * CREATED * *

                                        INDEX CREATION REPORT

------------------------------------------------------------------------------

Table Name    Index No.                Status             Error Log

------------------------------------------------------------------------------

CLASS         Index.1                  * * CREATED * *

              Index.2                  * * CREATED * *

                                        DATA MIGRATION REPORT

------------------------------------------------------------------------------

Table Name  Total Records  Records Migrated   Status     Remark

------------------------------------------------------------------------------

Class                 211               211   * * PASS * *

---------------------------------- END MIGRATION -----------------------------
==============================================================================

Figure 27 Sample Migration Report

Migration Driver Trace

If you are receiving an error during migration, you may need more details than the summary report provides. Enabling the driver trace during migration can identify which row failed and the values of the fields. This report is enabled in the Preferences dialog box (see Setting Migration Preferences).

Select Report | Driver Trace.

11:49:59.900> SQLBTR 5.0.5914.0 SQL_BTR.dll [10000000] (C:\Program
              Files\Mertech Data Systems\DB Drivers\Btrieve\bin\sql_btr.dll)
11:49:59.900> Computer : GATEWAY
11:49:59.900> ModuleName : C:\Program Files\Mertech Data Systems\DB
              Drivers\Btrieve\bin\Btr2SQL.exe
11:49:59.900> Process # 280 WTS # 0
11:49:59.916> Trace Level: 1 flush: no buffers: 'sql pb kb db '
11:49:59.916> Date: Tue Dec 18 11:49:59 2012
11:49:59.947> Btrieve: 10.30 NT Server Engine
11:49:59.947> Active Code Page: 1252 (1252 (ANSI - Latin I))
11:49:59.947> Active OEM Code Page: 437 (437 (OEM - United States))
|-----------------------------------------------------------------------------------------|

11:50:13.431> Performance: 13.48748 gap in trace
11:50:13.431> btr2sql.exe: Converting Class
|-----------------------------------------------------------------------------------------|

11:50:13.478> btr2sql.exe: Enabling SnapShot Isolation on Database . . .
11:50:13.478> PrepareBuffer: /*EOVDV#F@PB@@WF"uanref#QDP#CMHLU^WMCQWKMU[JQNHBVHKM"NJ
              ___________________________ mc*/

11:50:13.525> Execute query time: 0.04576
11:50:13.525> PrepareBuffer: /*EOVDV#F@PB@@WF"uanref#QDP#PDEG]BKNOHPWGE[PL@TPJNP#MO
              ____________________________mc*/

11:50:13.759> Execute query time: 0.23281
11:50:13.759> OLE-DB ERROR: Execute failed:
11:50:13.759> HRESULT: 0X80040E14, SQLSTATE: 42000, Native Code: 5058 : Option
              'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.

11:50:13.759> MSSQL ERROR: Execute failed: Errors in SQL command: 5058 : Option
              'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.

11:50:13.759> MDS ERROR: #25000: 'Execute failed: Errors in SQL command: 5058 : Option
              'READ_COMMITTED_SNAPSHOT' cannot be set in database 'tempdb'.'

11:50:15.369> Performance: 1.60708 gap in trace
11:50:15.369> PrepareBuffer: /*<"<$@CMH#qq[wcchfq!,<.>(/.1-#*/
11:50:15.431> Performance: Prepare ran long: 0.06619
11:50:15.603> Execute query time: 0.16406
11:50:15.603> Read 1 rows
11:50:17.681> Performance: 2.07310 gap in trace
11:50:17.681> Status 12 opening Btrieve file 'C:\ProgramData\Pervasive
              Software\PSQL\Demodata\fieldext.ddf'

11:50:17.681> Field 1 ID               type=15  ofs=0   size=4  flags=0
11:50:17.681> Field 2 Name             type=0   ofs=4   size=7  flags=1
11:50:17.681> Field 3 Section          type=0   ofs=11  size=3  flags=1
11:50:17.681> Field 4 Max_Size_nis
11:50:17.681> Field 5 Max_Size         type=14  ofs=15  size=2  flags=4
11:50:17.681> Field 6 Start_Date       type=3   ofs=17  size=4  flags=0
11:50:17.681> Field 7 Start_Time       type=4   ofs=21  size=4  flags=0
11:50:17.681> Field 8 Finish_Time      type=4   ofs=25  size=4  flags=0
11:50:17.681> Field 9 Building_Name    type=0   ofs=29  size=25 flags=1
11:50:17.681> Field 10 Room_Number     type=14  ofs=54  size=4  flags=0
11:50:17.681> Field 11 Faculty_ID      type=14  ofs=58  size=8  flags=0
11:50:17.744> RecordSize: 66 flags: 512
11:50:17.744> Index 1.1 ID flags=258
11:50:17.744> Index 2.1 NAME flags=1298
11:50:17.744> Index 2.2 SECTION flags=1282
11:50:17.744> Index 3.1 FACULTY_ID flags=278
11:50:17.744> Index 3.2 START_DATE flags=274
11:50:17.744> Index 3.3 START_TIME flags=258
11:50:20.384> Performance: 2.63401 gap in trace
11:50:20.384> Creating new table (CLASS) at 'C:\ProgramData\Pervasive
              Software\PSQL\Demodata\CLASS.MKD'

11:50:20.384> RecSize=66 (fixed length)   11 fields, 3 indexes.
11:50:20.431> Transaction Started  iso level=READ COMMITTED

Figure 28 Sample Migration Driver Trace

Runtime Driver Trace

The Runtime Trace file is generated while your application is running. Each Btrieve function call made is logged. The Runtime Trace file is enabled in the Preferences dialog box (see Setting Migration Preferences).

NOTE: It is important to note that you should turn this option on only when trying to debug the calls and data being sent and received from the server. For normal operation, remember to turn this off otherwise you will experience performance degradation.

Two variables control the trace output. TRACE_ON defines the location of the trace file — full (or relative) path and filename. TRACE_LEVEL sets the amount of output — depending on the amount of detail needed.

‑1: ERROR_LEVEL Only messages generated because of an error situation. Messages in trace are preceded by “ERROR:” or “WARNING:”

0: BASIC_LEVEL Basic output - flow of application. Showing begin/end transactions, locks gained/released, record reads

1: DETAIL_LEVEL1 Field values in record read/written - Btrieve input/output buffers

2: DETAIL_LEVEL2 The “debug” level - it fills in more details above level 1 in order to track down a problem

3: DETAIL_LEVEL3 Needed only in rare cases

Each TRACE_LEVEL includes details from all previous levels.

NOTE: In the event that you are receiving an error from the server and the nature of the problem is unclear, Mertech recommends you run this trace first and then contact Mertech Technical Support. For any error on the server, you should first look in the server documentation for clarification before contacting Mertech.

22:16:25.140> SQLBtr 3.1.1.2
22:16:25.140> ModuleName : C:\bat\WBEXEC32.exe
22:16:25.140> Trace Level: 0
22:16:25.140> Date: Thu Nov 08 22:16:25 2007
|-----------------------------------------------------------------------------------------|
#1
22:16:25.140> BtrieveFunctionCall : **B_OPEN** KeyNum: 0 DataLen: 0 KeyLen: 29
22:16:25.140> Filename = "c:\pvsw\demodata\billing.mkd"
22:16:25.140> Reading table information from: c:\pvsw\demodata\billing_mkd.int
22:16:26.843> Login (ServerName=sqlsrv,UserName=sa)
22:16:26.843> License File : c:\bat\sql_btr.cfg
22:16:27.000> SQL Server 9.00.3054.00 - Developer Edition SP2
22:16:27.031> Database set to: DemoData
22:16:27.031> OpenTable: "DemoData"."dbo"."BILLING"
22:16:27.031> hFileHandle: 7
22:16:27.031> ReturnCode : 0 DataLen: 0
|-----------------------------------------------------------------------------------------|
#2
22:16:27.109> BtrieveFunctionCall : **B_STAT** KeyNum: 0 DataLen: 2185 KeyLen: 255 PosBlock: 7
22:16:27.109> GetRecordsUsedOF :"DemoData"."dbo"."BILLING" = 1315
22:16:27.109> ReturnCode : 0 DataLen: 80
|-----------------------------------------------------------------------------------------|
#3
22:16:29.796> BtrieveFunctionCall : **B_GET_FIRST** KeyNum: 0 DataLen: 61057 KeyLen: 255 PosBlock: 7
22:16:29.796> FIND GT "DemoData"."dbo"."BILLING" BY INDEX 1 MAX_ROWS=10 IndexName: BILLING_I01
22:16:29.828> FetchNextRecord: "DemoData"."dbo"."BILLING" [FOUND] MDS_RECNUM = 1
22:16:29.828> Move KeyColumns -> BTR Key : Table BILLING Index 1
22:16:29.828> STUDENT_ID="100062607"
22:16:29.828> TRANSACTION_NUMBER="1"
22:16:29.828> LOG="03/28/1996 15:38:52"
22:16:29.828> REGISTRAR_ID="130312616"
22:16:29.828> ReturnCode : 0 DataLen: 137
|-----------------------------------------------------------------------------------------|
#4
22:16:30.796> BtrieveFunctionCall : **B_GET_NEXT** KeyNum: 0 DataLen: 61057 KeyLen: 255 PosBlock: 7
22:16:30.796> FIND NEXT "DemoData"."dbo"."BILLING" BY INDEX 1 MAX_ROWS=10 IndexName: BILLING_I01
22:16:30.796> FetchNextRecord: "DemoData"."dbo"."BILLING" [FOUND] MDS_RECNUM = 2
22:16:30.796> Move KeyColumns -> BTR Key : Table BILLING Index 1
22:16:30.796> STUDENT_ID="100285859"
22:16:30.796> TRANSACTION_NUMBER="1"
22:16:30.796> LOG="03/28/1996 15:38:52"
22:16:30.796> REGISTRAR_ID="313053054"
22:16:30.796> ReturnCode : 0 DataLen: 137
|-----------------------------------------------------------------------------------------|
#5
22:16:32.296> BtrieveFunctionCall : **B_UPDATE** KeyNum: 0 DataLen: 137 KeyLen: 255 PosBlock: 7
22:16:32.296> BeginTransaction [Implicit]: sqlsrv
22:16:32.296> BindParameter: STUDENT_ID = '99237283' lPosBind: 1 lLength: 8
22:16:32.296> BindParameter: MDS_RECNUM = '2' lPosBind: 2 lLength: 1
22:16:32.921> ImplicitTransactionCommit: thomaslap
22:16:32.921> Move KeyColumns -> BTR Key : Table BILLING Index 1
22:16:32.921> ReturnCode : 0 DataLen: 137
|-----------------------------------------------------------------------------------------|
#6
22:16:34.000> BtrieveFunctionCall : **B_CLOSE** KeyNum: 0 PosBlock: 7
22:16:34.000> CloseTable: "DemoData"."dbo"."BILLING"
22:16:34.000> ReturnCode : 0 DataLen: 0
|-----------------------------------------------------------------------------------------|
#7
22:16:35.296> BtrieveFunctionCall : **B_STOP** KeyNum: 0
22:16:35.296> LogoutFrom: (ServerName=thomaslap)
22:16:35.296> ReturnCode : 0 DataLen: 0

Figure 29 Sample Runtime Driver Trace

Turning on application logging

There are several ways to turn on application logging:

Tracing driver calls from BTR2SQL using the Preferences dialog box

The easiest method of enabling the trace file is to set it from BTR2SQL in the Preferences dialog box by checking the Runtime Trace File option. However, this tool is not usually deployed with your application, so the end-user may not have access to this.

Tracing driver calls with environment variables

SET TRACE_ON=<TracefileName>

SET TRACE_LEVEL=<level>

“NOTE: There are no spaces after the equals sign.

The environment variables must be active in the system before starting the application. You can add these two variables using the My Computer properties (Advanced tab, Environment Variables button). Or you can do this at a Command Prompt before starting the application:”

Figure 30 Setting up a trace file at the command prompt

Tracing driver calls with registry settings

HKEY_CURRENT_USER\SOFTWARE\Mertech Data Systems\DB Drivers\BTR\\\ *driver*\\v5

where driver is one of Oracle Driver, SQL Server Driver, or PgSQL Driver

Add the string value TRACE_ON and set its value to the name of the trace file. Add another string value named TRACE_LEVEL with a number between -1 and 3. If the registry keys do not exist, you will need to create each key in the hierarchy before entering the values.

Figure 31 Setting up a trace file using the registry

NOTE: Settings can also be stored under HKEY_LOCAL_MACHINE

Tracing driver calls with mds_global.ini settings

You can also use settings in the mds_global.ini (Trace_on, Trace_Level, and Trace_Flush) to control application logging.

  • See also mds_global.ini

Tracing driver calls using MdsSetSetting

You can also call MdsSetSetting to programmatically set trace variables.

  • See also Setting global and folder specific values programmatically

Driver Trace Control Tool

Logging can also be configured using Mertech’s separate Driver Trace Control Tool. Click the Windows Start button, open the Program menu and select Mertech ISDBC Drivers for Btrieve | Driver Trace Control Tool.

Figure 32 Setting up a trace file using the trace utility

Specify the trace file and trace level, then click Save.

Startup Trace

There is a startup trace file to record errors detected during the early stages of driver initialization (i.e., before the regular trace begins). The startup trace is automatically turned on and then turned off after successful driver connection.

The startup trace file is named “mds_<drivername>.log” (for example, mds_ora_btr.log, mds_sql_btr.log, mds_pgs_btr.log) and is always written to %temp%.

NOTE: Always use Windows Start | Run “%temp%” to navigate to the temp folder rather than manually going to what you *think* is the temp folder.

The startup trace logs only errors and only during the driver’s initial phase. The startup trace file is good place to look if, for example, your user-defined trace file is not created. An entry may be recorded in the startup trace file indicating that the user-defined trace file cannot be opened.

External Tools

Errors are often caused because a driver dependency is not installed or not available on the Windows PATH. Dependency Walker and Process Monitor are two good tools that you can use to identify a missing dependency for the BTR2SQL Migration Utility or your application and to troubleshoot runtime errors.

Dependency Walker

1. Download Dependency Walker from www.dependencywalker.com. Mertech driver and the Migration Utility are 32-bit, make sure to download the 32-bit version of Dependency Walker.

  1. Open the downloaded ZIP file.

  2. Create an extract folder and extract all the files.

  3. (Optional) drag a shortcut to your desktop or quick launch toolbar.

Identifying statically loaded dll dependencies

  1. Start Dependency Walker.

2. Choose File | Open from the menu bar and browse to the location of the Mertech driver dll. Mertech driver dlls are usually located at <Program Files>Mertech Data SystemsDB DriversBtrieve\ bin.

3. Choose sql_btr.dll for MS SQL, ora_btr.dll for Oracle or pgs_btr.dll for PostgreSQL.

Missing or mismatched dependencies appear in yellow or red. The most common dll to be missing is oci.dll.

Examining your application at runtime

  1. Start Dependency Walker and set the View | Full Paths option.

2. Select the application using the File | Open command.
The application is scanned for dependencies and a hierarchical diagram of all dependent modules is displayed in the Module Dependency Tree View.

  1. Select Profile | Start Profiling.

  2. Make sure Use full paths when logging file names is checked.

  3. Click OK.

Your application should begin to run. As your application runs, Dependency Walker logs information to the Log View and updates the tree and list views.

Process Monitor

Process Monitor can also be used to do identify missing dependencies and to troubleshoot other runtime errors.

1. Download Process Monitor from https://technet.microsoft.com/en-us/sysinternals/processmonitor.

  1. Open the downloaded ZIP file.

  2. Create an extract folder and extract all the files.

  3. (Optional) drag a shortcut to your desktop or quick launch toolbar.

  4. Start Process Monitor.

6. Select File from the menu bar and make sure Capture Events IS NOT checked.

  1. Select Edit | Clear Display.

8. Select Filter and add Process Name btr2sql.exe to the list of events to be captured.

9. Enable event capture: select File from the menu bar and select Capture Events so IT IS checked.

10. Start the Btr2SQL Migration Utility or your application and reproduce the error.

11. Disable event capture in the Process Monitor: select File from the menu bar and select Capture Events so it IS NOT checked.

12. Examine the output. NAME NOT FOUND in the Result column can point to a missing dependencies.

Blog Topics

The Mertech Data Website contains white papers and blogs to assist with additional troubleshooting topics. The site search option image50 can help you to find the required topics.