An error occurred while reading the Excel: org.apache.poi.hssf.record.RecordFormatException: Duplicate PageSettingsBlock record (sid=0x89c).

ISSUE IF POI JAR FILES ARE NOT UPGRADED

Exception: ‘An error occurred while reading the Excel: org.apache.poi.hssf.record.RecordFormatException: Duplicate PageSettingsBlock record (sid=0x89c).’

Exception Cause: Using older version of POI jar files.

Description:

At times, while reading few .xls files, using cfspreadsheet, we face the following issue:

‘An error occurred while reading the Excel: org.apache.poi.hssf.record.RecordFormatException: Duplicate PageSettingsBlock record (sid=0x89c).’

This is because the default poi jar files-we get with CF installation, do not process all excel files. CF10 gives us version 3.6 beta jar files by default and these do not process all the excel files. So we have to upgrade these jar files for better Excel support in ColdFusion.

  

Solution:

1.       Download the latest stable “Binary Distribution” version jar files from “http://poi.apache.org/download.html”. I have downloaded version 3.10 from ”http://mirror.nexcess.net/apache/poi/release/bin/poi-bin-3.10-FINAL-20140208.tar.gz”

2.       Extract the files to a temporary folder.

3.       Navigate to the ColdFusion’s lib folder - “C:\ColdFusion10\cfusion\lib”.

4.       Take a backup of all the jar files starting with “poi-“.

5.       Stop the ColdFusion Service. (Services.msc  -> ColdFusionMX Application Server).

6.       Delete the existing poi jar files (jar files starting with poi -) from the “lib” folder (C:\ColdFusion10\cfusion\lib).

7.       Copy the latest jar files, which were extracted to a temporary folder earlier, into the “lib” folder.

8.       Now start the ColdFusion Service.

9.       Execute the file processing the .xls file. It should now be able to read the excel file.

J

Windows could not start the ColdFusion 10 Application Server on Local Computer. For more information review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 0.

                                MAXIMUM JVM HEAP SIZE ALLOWED IN A 32-BIT OPERATING SYSTEM.

Error Message: “Windows could not start the ColdFusion 10 Application Server on Local Computer. For more information review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 0. “

Error Cause:  Allocating 1844 MB or more for ‘Maximum JVM Heap Size (in MB)’ in ‘Java and JVM‘ Settings of CF Admin on a 32-bit processing system.

Note:

Any value, greater than 1844, in a 32-bit OS, for Max JVM Heap Size would cause the issue mentioned.

Description:

At times, we get errors specifying that the process is running short of JVM Heap memory. So we increase the Max JVM Heap size through CF Administrator. But increasing the Max size, without knowing its boundary value, would land up in getting the issue mentioned below:

1.       Try changing the ‘Maximum JVM Heap Size (in MB)’ value in ‘Java and JVM’ settings of CF Admin to any value higher than 1844 on a 32bit operating system, as shown in the below screen shot.

  

2.       Then click on Submit. An alert appears, as shown below, which asks for a ColdFusion Service restart.

3.       Open Services.msc and try restarting the ColdFusion service.

4.       It throws an error as shown in the below screenshot:

 5.       The only way, this issue can be fixed, is by changing the jvm.config file.

a.       Go to CF Installation directory like “C:\ColdFusion10\cfusion\bin\” and there you will find a file jvm.config.

b.      If the file is not found, search for jvm.config file in the installation directory.

c.       Open this file in Notepad and search for “Arguments to VM”.

d.      Change the value after –Xmx to a value less than 1844.

e.      Save the file and then restart the ColdFusion Service.

Issue fixed J

Reverting accidental delete/update without recent backup

Issue:  At times, we may run/execute an update/delete script accidentally and then start googling to find if there is a way to revert it.

Requirement to fix the issue: Old Backup of the same Database.

Solution:

The solution given below is helpful if you have some old Database backup.

Steps:

1.      Note the approximate time at which you executed the update/delete script. Restoring the DB to a minute earlier to that time would make sure that we haven’t lost any updates on the DB.

2.      Take a ‘Transaction Log’ backup of the Database to be restored by selecting the ‘Transaction Log’ from the ‘Backup Type’ dropdown. To get the "Transaction Log" in the dropdown, make sure the DB Recover Model is "FULL". 

      To make a DB recover model as "Full":

            1.      After connecting to the appropriate instance of the SQL Server Database Engine, in Object       Explorer, click the server name to expand the server tree.

2.      Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

3.      Right-click the database, and then click Properties, which opens the Database Properties dialog box.

4.      In the Select a page pane, click Options.

5.      The current recovery model is displayed in the Recovery model list box.

6.      Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.

7.      Click OK.

         

3.      Now ‘Restore’ the old backup of the database you have. Make sure that select the ‘RESTORE WITH NORECOVERY’ option in the ‘Recovery State’ dropdown in Restore options.

4.      Once the Old backup is restored, now restore the Transaction Log Backup specifying the timeline which is BEFORE the update/delete script execution.Also make sure to select the ‘RESTORE WITH RECOVERY’ option in Restore Options.

5.      Now run a SELECT script and check the records. They would contain the values before the Update/Delete script execution. J