(70 intermediate revisions by the same user not shown) | |||
Line 4: | Line 4: | ||
==Program== | ==Program== | ||
* Version (2. | * Version (2.9.1.32) - 27 Feb 2021 | ||
* | * [https://www.sql.com.my/downloadfile/Fairy/SQLDiagnosisV2-setup.exe SQLDiagnosisV2] | ||
* MD5 : | * MD5 : AF0CE34E01D388DEBFE029FB024F6535 | ||
===History New/Updates/Changes=== | ===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-- | --Build 23-- | ||
* Upgrade to Version 2.6. | * Upgrade to Version 2.6. | ||
Line 58: | Line 112: | ||
==Todo== | ==Todo== | ||
* | * | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
Line 74: | Line 129: | ||
:03. Right Click Here | :03. Right Click Here | ||
[[File:SQLDiagnosis-DatabasePatch-02.jpg|center]] | [[File:SQLDiagnosis-DatabasePatch-02.jpg|center]] | ||
:04. Select '''Load From File...''' | :04. Select '''Load From File 1...''' | ||
[[File:SQLDiagnosis-DatabasePatch-03.jpg|700px|center]] | [[File:SQLDiagnosis-DatabasePatch-03.jpg|700px|center]] | ||
:05. Look for patch file (Norm begin with ''Patch-''XXXX''. | :05. Look for patch file (Norm begin with ''Patch-''XXXX''.sql2'') | ||
:06. Click '''Open''' button. | :06. Click '''Open''' button. | ||
:07. Click '''Execute...''' button (In '''Step 3''' Section). | :07. Click '''Execute...''' button (In '''Step 3''' Section). | ||
[[File:SQLDiagnosis-DatabasePatch-FromEstream.gif|center]] | |||
====Build In Patch==== | ====Build In Patch==== | ||
Line 90: | Line 146: | ||
|- | |- | ||
| AR/AP Copy Paste Error... || It will | | AR/AP Copy Paste Error... || It will | ||
* Create EstreamAR (Customer) & EstreamAP (Supplier) | * <del>Create EstreamAR (Customer) & EstreamAP (Supplier) </del> <= Not Valid from Build 31 & above | ||
* Set the Invalid Transaction code to the EstreamAR/EstreamAP code... | * <del>Set the Invalid Transaction code to the EstreamAR/EstreamAP code... </del> <= Not Valid from Build 31 & above | ||
* After patch please go to the respective doc no, Maintain customer & maintain supplier to delete it... | * <del>After patch please go to the respective doc no, Maintain customer & maintain supplier to delete it... </del> <= 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 <span style="color:#0000ff">Account Code</span> to <span style="color:#0000ff">CH2BA</span> 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 -> InValid Payment Method... || Delete Invalid Record in Maintain Payment Method | ||
|- | |- | ||
| Remove > Non-Stock Control Opening Balance... || Delete all Non-Stock Control Opening Balance | | Remove -> Non-Stock Control Opening Balance... || Delete all Non-Stock Control Opening Balance | ||
|- | |- | ||
| Remove > All Customer... || Delete All Maintain Customer Records | | Remove -> All Customer... || Delete All Maintain Customer Records | ||
|- | |- | ||
| Remove > All Supplier... || Delete All Maintain Supplier Records | | Remove -> All Supplier... || Delete All Maintain Supplier Records | ||
|- | |- | ||
| Remove > All Stock Item... || Delete All Maintain Item Records | | Remove -> All Stock Item... || Delete All Maintain Item Records | ||
|- | |- | ||
| Remove > All Other Stock Maintenance... || Delete All Other Stock Maintenance | | Remove -> All Other Stock Maintenance... || Delete All Other Stock Maintenance | ||
* Stock Group | * Stock Group | ||
* Stock Location | * Stock Location | ||
Line 116: | Line 195: | ||
* Stock Category | * Stock Category | ||
|- | |- | ||
| Remove > Default Currency Code || Delete Default Currency | | 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 > | | Remove -> InActive Users... || Remove the All the User where Active is False | ||
|- | |- | ||
| Remove > | | Remove -> InValid Stock Category... || Remove Stock Category Code which had set in Maintain Item due to Import | ||
|} | |} | ||
Line 155: | Line 240: | ||
| Backup Metadata... || To Extract Empty Database Structure for IB Pump to copy to New Database. | | 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. | | Direct Zip... || Use this to Zip & upload to Estream if still unable to fix the problem. | ||
Line 167: | Line 252: | ||
:02. Select ''Backup MetaData...''. | :02. Select ''Backup MetaData...''. | ||
[[File:SQLDiagnosis-DatabaseValidate-02.jpg|center]] | [[File:SQLDiagnosis-DatabaseValidate-02.jpg|center]] | ||
:03. Click | :03. Click ''Start Copy'' to begin copy | ||
: | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
: | |- | ||
! 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 | |||
|} | |||
{| style="margin: 1em auto 1em auto;" | |||
|- | |||
| | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
{| | |||
|- | |||
| [[File:Template.Warning-01.jpg|80px]]|| | |||
: 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...''' | |||
|} | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
|} | |||
[[File:SQLDiagnosis-DatabaseValidate-03.jpg|center]] | [[File:SQLDiagnosis-DatabaseValidate-03.jpg|center]] | ||
: | :04. Click OK when see Done message | ||
: | :05. Click ''Close'' Button. | ||
: | :06. Click ''Backup...'' Button. | ||
{| style="margin: 1em auto 1em auto;" | |||
[[File: | |- | ||
| | |||
[[File: | ---------------------------------------------------------------------------------------------------------------------------------------------------- | ||
{| | |||
|- | |||
| [[File:Template.Tips-01.jpg|60px]]|| | |||
* Wanted to Import Specific Table Only?... | |||
* Just Delete the table which you don't wanted to Import from the list. | |||
|- | |||
| [[File:Template.Warning-01.jpg|80px]]|| | |||
* Be careful if you just wanted to Import Specific table Only as might cause more error. | |||
|} | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
Line 265: | Line 383: | ||
:07. Select ''Start a program'' & Click Next | :07. Select ''Start a program'' & Click Next | ||
[[File:SQLDiagnosis-ScheduleBackup-02.jpg|center]] | [[File:SQLDiagnosis-ScheduleBackup-02.jpg|center]] | ||
<span style="color:#0000ff">For Build 32 & above</span><br /> | |||
:08. Click Browse button & find the '''SQLDiagnosisV2.exe''' (Default at ''C:\eStream\Utilities\SQLDiagnosis'')<br /> | |||
:08A. Set the <span style="color:#0000ff">Add arguments(optional)</span> as '''-Auto''' <br /> <br /> | |||
<span style="color:#0000ff">For Build till 31</span><br /> | |||
:08. Click Browse button & find the '''SQLScheduleV2.exe''' (eg. C:\eStream\Utilities\SQLDiagnosis\SQLScheduleV2.exe) | :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 | :09. Set the Start in(optional) path to the Exe folder (eg. C:\eStream\Utilities\SQLDiagnosis) & Click Next | ||
[[File:SQLDiagnosis-ScheduleBackup-03.jpg|center]] | [[File:SQLDiagnosis-ScheduleBackup-03.jpg|center]] | ||
:10. Tick the option ''Open the Properties dialog for this task when I click Finish'' | :10. Tick the option ''Open the Properties dialog for this task when I click Finish'' | ||
Line 282: | Line 407: | ||
:18. Click Ok | :18. Click Ok | ||
{| style="margin: 1em auto 1em auto;" | |||
|- | |||
| | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
{| | |||
|- | |||
| [[File:Template.Tips-01.jpg|60px]]|| You can set below command for Schedule Stop & Start Firebird | |||
:* Stop Firebird => <span style="color:#0000ff">C:\WINDOWS\SYSTEM32\NET.EXE stop "Firebird Server - DefaultInstance"</span> | |||
:* Start Firebird => <span style="color:#0000ff">C:\WINDOWS\SYSTEM32\NET.EXE start "Firebird Server - DefaultInstance"</span> | |||
|} | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
==See also== | |||
* Others [[Customisation]] |
Latest revision as of 09:58, 21 August 2023
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.
Requirement
- Firebird Server (even is Standalone user)
Todo
Database Patch
- A min console use to manipulate the data including edit, delete and append data.
- There are 2 type of patch
- Patch From Estream
- 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
For from Build 31 & above
|
Change -> Cash To Bank Type... | Change Account Type from Cash To Bank Type
|
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
|
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 |
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.
|
- 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... |
|
- 04. Click OK when see Done message
- 05. Click Close Button.
- 06. Click Backup... Button.
|
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.
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.
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 |
|
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
|
See also
- Others Customisation