IBM Support

**Troubleshooting** 'Error occured when deleting temporary VFP files' when running database optimise

Troubleshooting


Problem

User launches Controller, chooses 'single user mode' and performs a database optimise. After a few minutes, at the bottom-left of the screen, the process indicator says 'Removing Temporary FoxPro Tables...'.
 
User then receives error message.

Symptom

The error message may/will vary depending on circumstances. Below are some examples:

Information


Error occured when deleting temporary VFP files: File Error (1)
OK

  

Information

Error occured when deleting temporary VFP files: ODBC Error (2)
OK
 
Information
Error occured when deleting temporary VFP files: VFP dll missing (3)
OK

Cause

There are several potential causes for the message 'Error occurred when deleting temporary VFP files':
Scenario #1 - Error only occurs in development database (not live/production database)
  • Errors similar to these are typically only seen during the development phase of the system. They may be caused because certain parts of the financial system/application have not yet been created/configured.
  • In other words, the Controller system cannot delete some of the tables simply because these tables have not yet been configured/created. In turn, this is simply because the development stage of Controller has not reached this section/part yet.
Scenario #2 - Controller application server has missing ODBC registry key, caused by invalid/corrupt database client installation.
  • The message will occur for all databases
  • The likely error message includes the text "ODBC Error (2)"
 
Scenario #3 - Controller application server has missing/unregistered/deleted/virus-quarantined DLL files.
  • The likely error message includes the text "VFP dll missing (3)"
  • For more information, see separate IBM Technote #1442198.
 
Scenario #4 - Controller application server requires rebooting.
  • The likely error message includes the text "VFP dll missing (3)"
  • For more information, see separate IBM Technote #1507593.
 
Scenario #5 - Controller application server's "roles and users" COM+ security settings are too restrictive.
  • The likely error message includes the text "VFP dll missing (3)"
  • For more information, see separate IBM Technote #1958102.
 
Scenario #6 - Controller application server has a non-English Oracle client version installed.
  • The message will occur for all databases
  • The error message includes the text "ODBC Error (2)"
   
Scenario #7 - There is another batch job occurring in the background (for example a consolidation), which is also using FoxPro.
  • The error message will include the text "File Error (1)"
  • For more information, see separate IBM Technote #0960153 .
  
Scenario #8 -  The COM+ user (the username used to run the COM+ application) does not have sufficient rights to use the Oracle client.
  • The error message includes the text "ODBC Error (2)"
  • For more information, see separate IBM Technote #1106979.
 
Scenario #9 -  The COM+ user (the username used to run the COM+ application) does not have sufficient rights to use the COM+ subsystem
  • The error message includes the text "ODBC Error (2)"
  • For more information, see separate IBM Technote #1106979.
 
Scenario #10 -  The database connections are encrypted and there is no 32-bit SystemDSN (ODBC) created which corresponds to the name of the database connection
  • The error message includes the text "ODBC Error (2)"
  • For more information, see separate IBM Technote #6248791.

Diagnosing The Problem

Scenario #2
One method that you can use is Microsoft's Process Monitor tool during the optimise.
  • After capturing the events, filter out all the 'SUCCESS' messages, then search through the remainder.
  • In one customer's scenario, there was a 'NAME NOT FOUND' error for the process 'RegOpenKey' for the location 'HKLM\SOFTWARE\ODBC\ODBCINST.INI\SQL Native Client':

Resolving The Problem

Workaround:
In some cases, you can safely ignore error and simply click OK to carry on.

Fix:
Scenario #1
Continue to develop your Controller database. The error will generally disappear soon
Scenario #2
Diagnose database client installation issue on Controller application server
  • See below for exact steps.
Scenario #3
See separate IBM Technote #1442198.
Scenario #4
Reboot Controller application server.
  • See separate IBM Technote #1507593.
 
Scenario #5
Add an entry for 'Everyone' into the COM+ Server section of 'Controller Configuration'
  • See separate IBM Technote #1958102.
Scenario #6
Rename a registry key (on the application server)
  • See separate IBM Technote #1994312.
 
Scenario #7
Perform all of the following:
1. Make sure that you are using Controller 10.3.1 (or later)
2. Whenever you run a database optimise, make sure that it is scheduled to run as a batch job
3. Ensure that the maximum number of batch jobs is configured to be 1
  • See separate IBM Technote #0960153.
 
Scenario #8
In some (rare) environments, the COM+ user must be changed to be the application server's local Windows user 'administrator'.
For example:      servername\administrator
  • See separate IBM Technote #1106979.
  
Scenario #9
In some (rare) environments, the COM+ user must be changed to be a 'real' Windows user account (for example the application server's local Windows user 'administrator', or a domain user with local administrative rights) not the user 'Local System':
image 2481
  • For example:      servername\administrator
See separate IBM Technote #237755 .

 
Scenario #10
If customers are using encrypted database connections, then there must be a permanent SystemDSN (32-bit ODBC) connection with the correct name.
  • See separate IBM Technote #6248791.
=============================================
Steps for Scenario #2

IMPORTANT: Before modifying your registry, please take a backup as a precaution and fully test before/afterwards.

Example #1 - Microsoft SQL
In one customer's environment, using Process Monitor (see above) it was clear that the problem was caused by corruption inside their HKLM\SOFTWARE\ODBC\ODBCINST.INI registry key.
  • In this case, they were using the SQL 2008 native client provider (sqlncli10.1) inside Controller Configuration
  • The corruption caused the application server to attempt to use the SQL 2005 native registry key (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Native Client) instead of the "correct" key (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server Native Client 10.0)
    • One solution was to change to using the SQL 2000 Provider (SQLOLEDB.1)
    • An alternative solution was to add an extra entry, to repair the corrupt registry key
  • In this case, they imported the following registry file:
    • Windows Registry Editor Version 5.00

      [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Native Client]
      "UsageCount"=dword:00000001
      "Driver"="c:\\WINDOWS\\system32\\sqlncli10.dll"
      "Setup"="c:\\WINDOWS\\system32\\sqlncli10.dll"
      "APILevel"="2"
      "ConnectFunctions"="YYY"
      "CPTimeout"="60"
      "DriverODBCVer"="10.00"
      "FileUsage"="0"
      "SQLLevel"="1"

Example #2 - Oracle 10G
In a different customer's environment, the customer's Controller application server's Oracle client had not been correctly patched. In fact, they had installed the Oracle client into 5 different/separate folders, causing the issue. In this specific customer's case, the solution was to:
  1. Obtain a short period of downtime
  2. Logon to the Controller application server as an administrator
  3. Uninstall all the existing versions of Oracle 10G clients (client_1, client_2 etc)
  4. Delete any Oracle client folders that remain on the hard drive
  5. Installed new Oracle 10G client using the IBM Proven Practice documentation
  6. Reboot application server

WARNING:
  • It is very easy to patch the client incorrectly. Therefore take great care to follow the IBM recommended steps exactly.
  • In particular, ensure that all the patches are installed into the SAME directory as the original client was installed.
  • In other words, install in the same OraHome location.
  • NOTE: The Oracle patch install routine tries to install patches to a DIFFERENT folder!

Steps:
For a full description of how to patch the client, see Proven Practice document "Step-by-Step guide to installing Oracle 10G Client on a Controller 8.3 Application server" which gives easy step-by-step instructions.
  • TIP: This is available from separate IBM Technote #1361692.

Example #3 - Oracle 11G
In a different customer's environment, the Controller application server did not have a 'full' ("Administrator") installation of the Oracle client on the application server.
  • The missing Oracle client components are needed in certain Controller menu items, including a database optimise
  1. Obtain a short period of downtime
  2. Logon to the Controller application server as an administrator
  3. Uninstall all the existing versions of Oracle 11G clients
  4. Delete any Oracle client folders that remain on the hard drive
  5. Installed new Oracle 11G release 2 client, choosing the option "full" ("Administrator") during the install wizard
  6. Afterwards, patch the Oracle client using Oracle patch "10100100". TIP: See separate IBM Technote #1448885 for more details.
  7. Reboot application server

Using_Microsoft_Process_Monitor.jpg

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1040231

Document Information

Modified date:
15 July 2020

UID

swg21365329

Page Feedback