SQL Diagnosis

Introduction

Is External Utility to Fixing & Managing SQL Firebird Database.

Program

• Version (2.9.1.32) - 27 Feb 2021
• SQLDiagnosisV2
• MD5 : AF0CE34E01D388DEBFE029FB024F6535

History New/Updates/Changes

--Build 32--

• Mini Console - Add Get Generator List Function.
• Fix still unable to backup FDB if no SY_Profile table.
• Mini Console - Add Get Table Records Count Function.
• Change Schedule Backup using command instead exe.
• Enable support restore without zip.
• Remove Verbose on Schedule Backup.
• Update High Resolution Icon.
• Add "Non System Document Number Set..." Patch.

--Build 31--

• Add New Application Window function.
• Add Sweep Database in Validate function.
• Add Trim Empty Space - Payroll function in DB Patch.
• Update Copy Paste Error function to allow user enter Valid Company Code.
• Fix Memo not scroll down when remove temp table.
• Add Remove InValid Stock Category... Patch
• Fix unable to backup FDB if no SY_Profile table.

--Build 30--

• Add Option Use Full name for batch backup.
• Enable support FB3.0.2.

--Build 29--

• Add Update Generator to DB Patch function.
• Add Update Non Stock Control to Fixed Cost...to DB Patch function.
• Add Remove InActive User to DB Patch function.
• Add Change Agent & Area code to DB Patch function.
• Add Update GL Account Special A/c Type Null to Empty to DB Patch function.
• Add Update Batch Null to Empty to DB Patch function.

--Build 28--

• Upgrade to Version 2.8.
• Change New Icon.
• Fix InValid GST Transactions not working.
• Add Update Generators... Patch.

--Build 27--

• Fix patch Error string literal with 19890 characters exceeds the maximum length of 16383 characters for the utf8 character set.
• Add Multi Copy function for SQL DB Copy Table...

--Build 26--

• Add YED QT && PQ Purge...
• Add InValid GST Transations.

--Build 25--

• Upgrade to Version 2.7.
• Remove -l -g -ig from Schedule & Batch Backup.
• Replace IB Pump with SQL DB Copy Table...function.

--Build 24--

• Add Remark for Batch Backup filename.
• Add Load From File 2 for DB Patch function.

--Build 23--

• Upgrade to Version 2.6.
• Fixed DB Patch will auto remove & in the script.
• Fixed Load Build-In Patch Error in some pc.
• Add Remove Duplicate GL Stock Value Patch.
• Enable Proper Installation.

--Build 22--

• Internal release.

--Build 21--

• Upgrade to Version 2.5.
• Fix unable open fdb in LocalHost.
• Add Batch Backup.
• Add Schedule Backup.
• Fix AR/AP Copy Paste Patch not working in some situation.
• Fix still unable to backup in some situation.
• Fix Backup Metadata setting not correct for FB3.0 unicode
• Add Firebird Version label for Patch, Validate & Adhoc Backup.

--Build 20--

• Fix Application Name Error.
• Add DB Online function.
• Add Remove InValid CN Patch.

--Build 19--

• Fix unable to backup.
• Add Load From File... for DB Patch...

--Build 18--

• Upgrade to Version 2.4.
• Enable Support Firebird 3.0.
• Enhance Validate to support IBPump.
• Find Tune SQL Patch for New Patch Generator.
• Add Remove Maintain Customer, Supplier, Stock Item & Other Stock Maintenance.
• Add AR/AP Copy Paste Error Patch.
• Add Change Cash To Bank Type Patch.
• Add Reset Grid Layout Patch.
• Add Delete Default Currency Patch.

[top]

Requirement

• Firebird Server (even is Standalone user)

[top]

Todo

[top]

Database Patch

• A min console use to manipulate the data including edit, delete and append data.
• There are 2 type of patch

1. Patch From Estream
2. Build In Patch

Steps

01. Click Database Patch button (Icon A).

02. Click This button to select the SQL Firebird Database.

Patch From Estream

03. Right Click Here

04. Select Load From File 1...

05. Look for patch file (Norm begin with Patch-XXXX.sql2)

06. Click Open button.

07. Click Execute... button (In Step 3 Section).

Build In Patch

03. Click the Arrow down button near the Execute... button.

04. Select the Patch from the available list.

05. Click Execute... button (In Steps 3 Section).

Patch Type	Description
AR/AP Copy Paste Error...	It will Create EstreamAR (Customer) & EstreamAP (Supplier) <= Not Valid from Build 31 & above Set the Invalid Transaction code to the EstreamAR/EstreamAP code... <= Not Valid from Build 31 & above After patch please go to the respective doc no, Maintain customer & maintain supplier to delete it... <= Not Valid from Build 31 & above For from Build 31 & above Will Update Customer or Supplier Code base on Code Given by user Set the Invalid Transaction code to the Customer or Supplier code given... After patch may go to the respective doc no, Maintain customer & maintain supplier to delete it or use CN to contra it
Change -> Cash To Bank Type...	Change Account Type from Cash To Bank Type Make sure Change the Account Code to CH2BA before run this patch
Change -> Agent Code...	Change From Agent Code A to Agent Code B for All Transactions
Change -> Area Code...	Change From Area Code A to Area Code B for All Transactions
Reset Grid Layout...	Reset all Layout to Default.
YED QT & PQ Purge...	Batch Delete Sales Quotation & Purchase Request Records.
Add Browse/Detail Form Mode...	Add Form Mode like SQL Accounting Version 775 & Below for SQL Accounting 776 & above.
Update -> Non Stock Control to Fixed Cost...	Set All Non Stock Control to Fixed Cost
Update -> Generators...	Update All the Generators for SQL Accounting.
Update -> GL Account Special A/c Type Null to Empty....	Set Maintain Account Special Account Type Field From Null to Empty.
Update -> Batch Null to Empty...	Set All Batch Field From Null to Empty
Update -> Trim Empty Space - Payroll...	Remove empty space for all field due to import
Remove -> InValid Payment Method...	Delete Invalid Record in Maintain Payment Method
Remove -> Non-Stock Control Opening Balance...	Delete all Non-Stock Control Opening Balance
Remove -> All Customer...	Delete All Maintain Customer Records
Remove -> All Supplier...	Delete All Maintain Supplier Records
Remove -> All Stock Item...	Delete All Maintain Item Records
Remove -> All Other Stock Maintenance...	Delete All Other Stock Maintenance Stock Group Stock Location Stock Price Tag Stock Batch Stock Category
Remove -> Default Currency Code...	Delete Default Currency
Remove -> InValid Credit Note...	Remove the redundancy AR/AP Credit Note Detail Records in GL Ledger
Remove -> Duplicate GL Stock Value...	Remove the double amount shown in GL Maintain Stock Value
Remove -> InValid GST Transactions...	Remove the "Ghost" transactions in GST Listing(GST_TR)
Remove -> InActive Users...	Remove the All the User where Active is False
Remove -> InValid Stock Category...	Remove Stock Category Code which had set in Maintain Item due to Import

[top]

Database Validate

• A function use to fix database corrupt

Steps

01. Stop the Firebird Service.

02. Copy the database & Paste to Desktop or any folder you wanted.

03. Start the Firebird Service.

04. Click Database Validate button (Icon B).

05. Click This button to select the Copied SQL Firebird Database.

06. Click Validate button.

You can Click few times Validate button to make sure it can maximise the database fixing

07. Click Backup... button.

Function	Description
Backup Metadata...	To Extract Empty Database Structure for IB Pump to copy to New Database.
SQL DB Copy Table...	Open the Database Pump which use to Copy data to New Database.
Direct Zip...	Use this to Zip & upload to Estream if still unable to fix the problem.

Backup MetaData

• Use this option when normal backup is fail.

Steps

01. Click the arrow down button at Backup... button.

02. Select Backup MetaData....

03. Click Start Copy to begin copy

Function	Description
Start Copy...	Using Single Connection to copy the table to New Database.
Start Multi Copy...	Using 2 Connections in same time to copy the table to New Database. Connection 1 - AUDIT, AUDITDTL, GST_TR & ST_TR Connection 2 - Other then Connection 1

Using Start Multi Copy... might will cause following problem Application User Interface will scramble Will prompt out of memory Access Violation on close window or Exit Application If above happen just Restart the SQL Diagnosis Refer below picture after done copy using Start Multi Copy...

04. Click OK when see Done message

05. Click Close Button.

06. Click Backup... Button.

Wanted to Import Specific Table Only?... Just Delete the table which you don't wanted to Import from the list. Be careful if you just wanted to Import Specific table Only as might cause more error.

[top]

Adhoc Backup

• A function use to backup the database

Steps

01. Click Adhoc Backup button (Icon C).

02. Click This button to select the SQL Firebird Database.

03. Click Backup button.

04. Select the Backup Folder (Default will Save to Desktop).

05. Click OK to Start Backup.

[top]

Database Online

• A function to bring back the database online when facing error "Database Shutdown"

Steps

01. Restart the Firebird.

02. Click Database Online button (Icon D).

03. Click This button to select the SQL Firebird Database.

[top]

Batch Backup

• A function to 1 button to backup for all database or for Schedule backup.

Steps

01. Click Batch Backup... button(Icon E)

02. Click New button.

03. Enter the Server Name/IP Address

04. Click on 3 dot button for Target Backup Folder field

05. Select the folder where you wanted to backup to & click OK

06. Click on 3 dot button for Database File field

07. Select the SQL Accounting/Payroll Database & Click Open

08. Click Save Button.

09. Repeat Step 2 to 8 for additional database

Field	Description
Check Box	Tick = Backup UnTick = Not Backup
Seq	System Auto run record number sequence
Server IP or Name	Server Name/IP Address
Target Backup Folder	A folder path where the database is backup to
Database File	SQL Accounting/Payroll Firebird Database
Remark	System Auto Shown the selected Database Company Name & Remark
Status	Last Backup Status
Date Time	Last Backup Date & Time

Schedule Backup

• Using Windows Schedule to Automatically run Batch Backup.

Steps

01. Open Windows Control Panel | Administrative Tools | Task Scheduler

02. Click Action | Create Basic Task...

03. Enter Task Name (eg SQL Backup) & Click Next

04. Select Daily & Click Next

05. Set the Start Date & Time to Start

06. Set 1 for Recur every field & Click Next

07. Select Start a program & Click Next

For Build 32 & above

08. Click Browse button & find the SQLDiagnosisV2.exe (Default at C:\eStream\Utilities\SQLDiagnosis)

08A. Set the Add arguments(optional) as -Auto

For Build till 31

08. Click Browse button & find the SQLScheduleV2.exe (eg. C:\eStream\Utilities\SQLDiagnosis\SQLScheduleV2.exe)

09. Set the Start in(optional) path to the Exe folder (eg. C:\eStream\Utilities\SQLDiagnosis) & Click Next

10. Tick the option Open the Properties dialog for this task when I click Finish

11. Click Finish

12. Select the option Run whether user is logged on or not

13. Click Triggers tab at top

14. Click Edit... button

15. Change the time to run

16. Click OK 2 times

17. Enter the window User Name & Password

18. Click Ok

You can set below command for Schedule Stop & Start Firebird Stop Firebird => C:\WINDOWS\SYSTEM32\NET.EXE stop "Firebird Server - DefaultInstance" Start Firebird => C:\WINDOWS\SYSTEM32\NET.EXE start "Firebird Server - DefaultInstance"

[top]

See also

• Others Customisation