SMS SQL - Insert Memo fields from Backup into Order and Estimate tables

Explanation of SQL

This SQL statement will take fields from one database table then fields from another database table and combine them into a single temporary table. You can then utilize that temporary table to insert the combined data into a new set of database tables and indexes.

This is especially helpful when a user encounters severe corruption of their memo (aka BLOB) fields which are stored in the .MB database tables. When severe corruption occurs and the DBRebuild utility is unable to repair the data this may be your only available option of restoring the data in those fields.

In the most basic example we will take a copy of the current Estimate Database table then a backup copy of the Estimate Database table that did not contain the corruption. Using the SQL below we'll pull all estimates and non-memo fields from the Estimate Database table into a query. Within the same query we'll pull just the memo fields from the backup and select those into the proper order so that we end up with a list of all estimates with their proper fields, in the proper order. From that point, since we have a complete set of good data (at least through the backup date) stored in the temporary table we can then execute an insert statement into a blank Estimate Database table.

Risk of Data Corruption if Run Improperly

High. Data is modified in this query. Do not run this unless you are an expert in SQL and fully understand the ramifactions of performing this action. Before attempting anything be sure to make a full backup and a compressed (.zip) format backup as well. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

SQL

These queries may require slight modification based on how you configure your folders. In the example below 3 folders were created for combining the data.
  • The current Data was placed in a new folder C:\Program Files\Cyrious\SMS\Current\
  • The backup Data was placed in a new folder C:\Program Files\Cyrious\SMS\Backup\
  • The fixed Data was placed in a new folder C:\Program Files\Cyrious\SMS\Fixed\ NOTE: Make sure this database is empty/has no data in it.
Note: If you receive an error that the Table is Full when attempting to run any of the queries then you will need to break the query up into smaller segments of records by utilizing a where statement such as:
  • WHERE OrderDetailID Between 1 and 50000
  • WHERE OrderDetailID Between 50001 and 100000
  • WHERE OrderID Between 1 and 50000
  • WHERE OrderID Between 50000 and 100000
Depending on how many records you have the range you utilize may need to be smaller. In my experience the SELECT statement for the Estimate Details and Order Details tables are typically the only tables that you have to break into parts.

SELECT
CAST(OrderID AS INTEGER) AS OrderID,
CAST(StoreID AS INTEGER) AS StoreID,
CAST(PricingRatio AS INTEGER) AS PricingRatio,
CAST(Description AS CHAR(100)) AS Description,
CAST(NumberItems AS INTEGER) AS NumberItems,
CAST(CustomerID AS INTEGER) AS CustomerID,
CAST(ContactID AS INTEGER) AS ContactID,
CAST(OrdererFirstName AS CHAR(20)) AS OrdererFirstName,
CAST(OrdererLastName AS CHAR(20)) AS OrdererLastName,
CAST(OrdererTitle AS CHAR(30)) AS OrdererTitle,
CAST(OrdererPAreaCode AS CHAR(25)) AS OrdererPAreaCode,
CAST(OrdererPhoneNumber AS CHAR(20)) AS OrdererPhoneNumber,
CAST(OrdererPhoneExtention AS CHAR(10)) AS OrdererPhoneExtention,
CAST(OrdererFAreaCode AS CHAR(25)) AS OrdererFAreaCode,
CAST(OrdererFaxNumber AS CHAR(20)) AS OrdererFaxNumber,
CAST(OrdererEmailAddress AS CHAR(75)) AS OrdererEmailAddress,
CAST(SalePersonID AS INTEGER) AS SalePersonID,
CAST(EnteredByID AS INTEGER) AS EnteredByID,
CAST(STATUS AS CHAR(15)) AS STATUS,
CAST(OnHold AS BOOLEAN) AS OnHold,
CAST(EstimateDate AS TIMESTAMP) AS EstimateDate,
CAST(OrderDate AS TIMESTAMP) AS OrderDate,
CAST(DueDate AS DATE) AS DueDate,
CAST(DueTime AS TIME) AS DueTime,
CAST(BuiltDate AS TIMESTAMP) AS BuiltDate,
CAST(VoidDate AS TIMESTAMP) AS VoidDate,
CAST(PickedUpDate AS TIMESTAMP) AS PickedUpDate,
CAST(WriteoffDate AS TIMESTAMP) AS WriteoffDate,
CAST(HoldDate AS TIMESTAMP) AS HoldDate,
CAST(LastInterestDate AS TIMESTAMP) AS LastInterestDate,
CAST(ClosedDate AS TIMESTAMP) AS ClosedDate,
CAST(SignSales AS Money) AS SignSales,
CAST(AVISales AS Money) AS AVISales,
CAST(InstallSales AS Money) AS InstallSales,
CAST(ProductionCat1Sales AS Money) AS ProductionCat1Sales,
CAST(ProductionCat2Sales AS Money) AS ProductionCat2Sales,
CAST(ProductionCat3Sales AS Money) AS ProductionCat3Sales,
CAST(ItemsTotal AS Money) AS ItemsTotal,
CAST(SalesSubtotal AS Money) AS SalesSubtotal,
CAST(TaxableAmount AS Money) AS TaxableAmount,
CAST(TaxExemptAmount AS Money) AS TaxExemptAmount,
CAST(TaxExemptAmountCalculated AS Money) AS TaxExemptAmountCalculated,
CAST(TaxExemptAmountOverridden AS BOOLEAN) AS TaxExemptAmountOverridden,
CAST(TaxExempt AS BOOLEAN) AS TaxExempt,
CAST(TaxIDNumber AS CHAR(20)) AS TaxIDNumber,
CAST(TaxRegionID AS INTEGER) AS TaxRegionID,
CAST(CountySalesTax AS Money) AS CountySalesTax,
CAST(CountySalesTaxRate AS NUMERIC) AS CountySalesTaxRate,
CAST(StateSalesTax AS Money) AS StateSalesTax,
CAST(StateSalesTaxRate AS NUMERIC) AS StateSalesTaxRate,
CAST(TaxTotal AS Money) AS TaxTotal,
CAST(PromotionID AS INTEGER) AS PromotionID,
CAST(DiscountAmount AS Money) AS DiscountAmount,
CAST(DiscountPercent AS NUMERIC) AS DiscountPercent,
CAST(ShippingCharge AS Money) AS ShippingCharge,
CAST(InterestAmount AS Money) AS InterestAmount,
CAST(SalesTotal AS Money) AS SalesTotal,
CAST(TotalPayments AS Money) AS TotalPayments,
CAST(BalanceOutstanding AS Money) AS BalanceOutstanding,
CAST(WriteoffAmount AS Money) AS WriteoffAmount,
CAST(PurchaseOrderNumber AS CHAR(15)) AS PurchaseOrderNumber,
B.ShippingAddress,
CAST(DispositionOnCompletion AS CHAR(9)) AS DispositionOnCompletion,
B.OrderNotes,
B.InstallNotes,
CAST(EstimateNumber AS INTEGER) AS EstimateNumber,
CAST(ItemsDiscountTotal AS Money) AS ItemsDiscountTotal,
CAST(DiscountTotal AS Money) AS DiscountTotal,
CAST(DispositionOnCompletionID AS INTEGER) AS DispositionOnCompletionID,
CAST(CustomerName AS CHAR(50)) AS CustomerName,
CAST(ShippingTaxable AS BOOLEAN) AS ShippingTaxable,
CAST(RoyaltyRate AS NUMERIC) AS RoyaltyRate,
CAST(JobBox AS CHAR(18)) AS JobBox,
CAST(ProofDate AS TIMESTAMP) AS ProofDate,
CAST(LineItemsOverriden AS BOOLEAN) AS LineItemsOverriden,
CAST(OrderOriginID AS INTEGER) AS OrderOriginID,
CAST(IsLocked AS BOOLEAN) AS IsLocked,
CAST(LockedDate AS TIMESTAMP) AS LockedDate,
CAST(LockedUser AS CHAR(25)) AS LockedUser,
CAST(ModifiedDate AS TIMESTAMP) AS ModifiedDate,
CAST(ModifiedUser AS CHAR(25)) AS ModifiedUser,
CAST(InvoiceID AS INTEGER) AS InvoiceID,
B.EstGreetingIntro,
CAST(EstGreetingIntroOV AS BOOLEAN) AS EstGreetingIntroOV,
B.EstGreetingBody,
B.EstGreetingClosing,
CAST(EstGreetingClosingOV AS BOOLEAN) AS EstGreetingClosingOV,
CAST (ConvertedDate AS TIMESTAMP) AS ConvertedDate,
CAST (ConvertedOrderID AS INTEGER) AS ConvertedOrderID,
CAST (LostDate AS TIMESTAMP) AS LostDate,
CAST (ModifiedByComputer AS CHAR(25)) AS ModifiedByComputer,
CAST (EnteredBy AS CHAR(50)) AS EnteredBy,
CAST (EnteredByPhone AS CHAR(20)) AS EnteredByPhone,
CAST (EnteredByPhoneExtention AS CHAR(10)) AS EnteredByPhoneExtention,
CAST (EnteredByFax AS CHAR(20)) AS EnteredByFax,
CAST (EnteredByEmail AS CHAR(75)) AS EnteredByEmail,
CAST (Salesperson AS CHAR(50)) AS Salesperson,
CAST (SalespersonPhone AS CHAR(20)) AS SalespersonPhone,
CAST (SalespersonPhoneExtention AS CHAR(10)) AS SalespersonPhoneExtention,
CAST (SalespersonFax AS CHAR(20)) AS SalespersonFax,
CAST (SalespersonEmail AS CHAR(75)) AS SalespersonEmail
 
FROM "C:\Program Files\Cyrious\SMS\Current\Estimate Database" C
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Estimate Database" B ON B.OrderID = C.OrderID
;FixedEstimate FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Estimate Database"
SELECT * FROM "FixedEstimate"
 

You will need to change the ending of the query above to the following to work on the Order table
FROM "C:\Program Files\Cyrious\SMS\Current\Order Database" C
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Order Database" B ON B.OrderID = C.OrderID
;FixedOrder FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Order Database"
SELECT * FROM "FixedOrder"

SELECT
CAST(OrderDetailID AS INTEGER) AS OrderDetailID,
CAST(StoreID AS INTEGER) AS StoreID,
CAST(LineItemNumber AS INTEGER) AS LineItemNumber,
CAST(OrderID AS INTEGER) AS OrderID,
CAST(ProductID AS INTEGER) AS ProductID,
CAST(ProductCode AS CHAR(30)) AS ProductCode,
CAST(Quantity AS INTEGER) AS Quantity,
CAST(Sides AS NUMERIC) AS Sides,
CAST(Height AS NUMERIC) AS Height,
CAST(Width AS NUMERIC) AS Width,
CAST(ForegroundColor AS CHAR(100)) AS ForegroundColor,
CAST(BackgroundColor AS CHAR(100)) AS BackgroundColor,
CAST(Font AS CHAR(100)) AS Font,
CAST(BasePrice AS Money) AS BasePrice,
CAST(BasePriceOverriden AS BOOLEAN) AS BasePriceOverriden,
CAST(BasePriceCalculated AS Money) AS BasePriceCalculated,
CAST(AVTotal AS Money) AS AVTotal,
CAST(InstallAmount AS Money) AS InstallAmount,
CAST(DiscountPercentage AS NUMERIC) AS DiscountPercentage,
CAST(DiscountAmount AS Money) AS DiscountAmount,
CAST(ItemTotal AS Money) AS ItemTotal,
CAST(UnitPriceOverridden AS BOOLEAN) AS UnitPriceOverridden,
CAST(OverRidden AS BOOLEAN) AS OverRidden,
CAST(ItemTotalCalculated AS Money) AS ItemTotalCalculated,
CAST(Taxable AS BOOLEAN) AS Taxable,
CAST(AllInstallation AS BOOLEAN) AS AllInstallation,
B.Description,
B.SignText,
CAST(AVIDS AS Bytes(100)) AS AVIDS,
CAST(AVValues AS Bytes(25)) AS AVValues,
CAST(AVAdditionalAmt AS Money) AS AVAdditionalAmt,
CAST(AVAdditionalDescription AS CHAR(25)) AS AVAdditionalDescription,
CAST(AVOverridden AS BOOLEAN) AS AVOverridden,
CAST(AVCalculated AS Money) AS AVCalculated,
CAST(ProductionLocation AS INTEGER) AS ProductionLocation,
CAST(ShopTime AS NUMERIC) AS ShopTime,
CAST(FieldTime AS NUMERIC) AS FieldTime,
CAST(FieldCrewSize AS NUMERIC) AS FieldCrewSize,
CAST(MaterialCost AS Money) AS MaterialCost,
CAST(HeightAboveGround AS NUMERIC) AS HeightAboveGround,
CAST(OtherInstallConsideration AS Money) AS OtherInstallConsideration,
CAST(SpecialEquipment1 AS BOOLEAN) AS SpecialEquipment1,
CAST(SpecialEquipment2 AS BOOLEAN) AS SpecialEquipment2,
CAST(SpecialEquipment3 AS BOOLEAN) AS SpecialEquipment3,
CAST(SpecialEquipmentOther AS BOOLEAN) AS SpecialEquipmentOther,
CAST(SpecialEquipmentOtherAmou AS Money) AS SpecialEquipmentOtherAmou,
CAST(EstimatedInstallCharge AS Money) AS EstimatedInstallCharge,
CAST(AccountCode AS INTEGER) AS AccountCode,
CAST(Hidden AS BOOLEAN) AS Hidden,
CAST(PMsAnswered AS BOOLEAN) AS PMsAnswered,
CAST(ParentItemNumber AS INTEGER) AS ParentItemNumber,
CAST(ChildLevel AS INTEGER) AS ChildLevel,
CAST(BaseCost AS Money) AS BaseCost,
B.ProductionNotes,
CAST(HasChildren AS BOOLEAN) AS HasChildren,
CAST(BaseSonsPrice AS NUMERIC) AS BaseSonsPrice,
CAST(BaseMeAndSonsPrice AS NUMERIC) AS BaseMeAndSonsPrice,
CAST(InstallSonsPrice AS NUMERIC) AS InstallSonsPrice,
CAST(InstallMeAndSonsPrice AS NUMERIC) AS InstallMeAndSonsPrice,
CAST(DiscountSonsPrice AS NUMERIC) AS DiscountSonsPrice,
CAST(DiscountMeAndSonsPrice AS NUMERIC) AS DiscountMeAndSonsPrice,
CAST(ItemTotalSonsPrice AS NUMERIC) AS ItemTotalSonsPrice,
CAST(ItemTotalMeAndSonsPrice AS NUMERIC) AS ItemTotalMeAndSonsPrice,
B.ModifierXML,
CAST (RoyaltyGroupID AS INTEGER) AS RoyaltyGroupID,
CAST (ModifiedByComputer AS CHAR (25)) AS ModifiedByComputer,
CAST (ModifiedDate AS TIMESTAMP) AS ModifiedDate
 
FROM "Estimate Details Database" Ct
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Estimate Details Database" B ON B.OrderDetailID = Ct.OrderDetailID
;FixedEstimateDetails FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Estimate Details Database"
SELECT * FROM "FixedEstimateDetails"

You will need to change the ending of the query above to the following to work on the Order Details table

FROM "Order Details Database" Ct
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Order Details Database" B ON B.OrderDetailID = Ct.OrderDetailID
;FixedOrderDetails FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Order Details Database"
SELECT * FROM "FixedOrderDetails"

In the above query you may or may not need to remove the following lines for it to complete successfully (Especially if the data is from 8.6 or earlier):
CAST (ModifiedByComputer AS CHAR (25)) AS ModifiedByComputer,
CAST (ModifiedDate AS TIMESTAMP) AS ModifiedDate

++Also in some cases you may have to switch the ordering of B.ModifierXML with that of CAST (RoyaltyGroupID as Integer) as RoyaltyGroupID.

++This is usually indicated with the error: "Type mismatch in expression" when executing the query.


Final Step

The last step in the process is to take the tables from the Fixed Data folder and put them into the live Data folder.


Note

If the previous 2 queries do not work, you can try the following queries:

SQL


SELECT
C.OrderID, C.StoreID, C.PricingRatio, C.Description, C.NumberItems, C.CustomerID, C.ContactID,
C.OrdererFirstName, C.OrdererLastName, C.OrdererTitle, C.OrdererPAreaCode, C.OrdererPhoneNumber,
C.OrdererPhoneExtention, C.OrdererFAreaCode, C.OrdererFaxNumber, C.OrdererEmailAddress,
C.SalePersonID, C.EnteredByID, C.STATUS, C.OnHold, C.EstimateDate, C.OrderDate,
C.DueDate, C.DueTime, C.BuiltDate, C.VoidDate, C.PickedUpDate, C.WriteoffDate,
C.HoldDate, C.LastInterestDate, C.ClosedDate, C.SignSales, C.AVISales, C.InstallSales,
C.ProductionCat1Sales, C.ProductionCat2Sales, C.ProductionCat3Sales, C.ItemsTotal,
C.SalesSubtotal, C.TaxableAmount, C.TaxExemptAmount, C.TaxExemptAmountCalculated,
C.TaxExemptAmountOverridden, C.TaxExempt, C.TaxIDNumber, C.TaxRegionID,
C.CountySalesTax, C.CountySalesTaxRate, C.StateSalesTax, C.StateSalesTaxRate,
C.TaxTotal, C.PromotionID, C.DiscountAmount, C.DiscountPercent, C.ShippingCharge,
C.InterestAmount, C.SalesTotal, C.TotalPayments, C.BalanceOutstanding,
C.WriteoffAmount, C.PurchaseOrderNumber,
B.ShippingAddress,
C.DispositionOnCompletion,
B.OrderNotes, B.InstallNotes,
C.EstimateNumber, C.ItemsDiscountTotal, C.DiscountTotal, C.DispositionOnCompletionID,
C.CustomerName, C.ShippingTaxable, C.RoyaltyRate, C.JobBox, C.ProofDate,
C.LineItemsOverriden, C.OrderOriginID, C.IsLocked, C.LockedDate,
C.LockedUser,
C.ModifiedDate, C.ModifiedUser, C.InvoiceID,
B.EstGreetingIntro,
C.EstGreetingIntroOV,
B.EstGreetingBody,
B.EstGreetingClosing,
C.EstGreetingClosingOV,
C.ConvertedDate,
C.ConvertedOrderID,
C.LostDate,
C.ModifiedByComputer,
C.EnteredBy,
C.EnteredByPhone,
C.EnteredByPhoneExtention,
C.EnteredByFax,
C.EnteredBy Email,
C.Salesperson,
C.SalespersonPhone,
C.SalespersonPhoneExtention,
C.SalespersonFax,
C.SalespersonEmail
FROM "C:\Program Files\Cyrious\SMS\Current\Estimate Database" C
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Estimate Database" B ON B.OrderID = C.OrderID
;FixedEstimate FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Estimate Database"
SELECT * FROM "FixedEstimate"

You will need to change the ending of the query above to the following to work on the Order table

FROM "C:\Program Files\Cyrious\SMS\Current\Order Database" C
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Order Database" B ON B.OrderID = C.OrderID
;FixedOrder FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Order Database"
SELECT * FROM "FixedOrder"

SELECT C.OrderDetailID, C.StoreID, C.LineItemNumber, C.OrderID,
C.ProductID, C.ProductCode, C.Quantity, C.Sides, C.Height, C.Width,
C.ForegroundColor, C.BackgroundColor, C.Font, C.BasePrice, C.BasePriceOverriden,
C.BasePriceCalculated, C.AVTotal, C.InstallAmount, C.DiscountPercentage, C.DiscountAmount,
C.ItemTotal, C.UnitPriceOverridden, C.OverRidden, C.ItemTotalCalculated, C.Taxable,
C.AllInstallation,
B.Description,
B.SignText,
C.AVIDS, C.AVValues, C.AVAdditionalAmt, C.AVAdditionalDescription, C.AVOverridden,
C.AVCalculated, C.ProductionLocation, C.ShopTime, C.FieldTime, C.FieldCrewSize,
C.MaterialCost, C.HeightAboveGround, C.OtherInstallConsideration, C.SpecialEquipment1,
C.SpecialEquipment2, C.SpecialEquipment3, C.SpecialEquipmentOther, C.SpecialEquipmentOtherAmou,
C.EstimatedInstallCharge, C.AccountCode, C.Hidden, C.PMsAnswered, C.ParentItemNumber,
C.ChildLevel, C.BaseCost,
B.ProductionNotes,
C.HasChildren, C.BaseSonsPrice, C.BaseMeAndSonsPrice, C.InstallSonsPrice, C.InstallMeAndSonsPrice,
C.DiscountSonsPrice, C.DiscountMeAndSonsPrice, C.ItemTotalSonsPrice, C.ItemTotalMeAndSonsPrice,
B.ModifierXML, C.RoyaltyGroupID,
C.ModifiedByComputer,
C.ModifiedDate,
C.ProductNameOnEstimates
 
 
FROM "Order Details Database" C
LEFT JOIN "C:\Program Files (x86)\Cyrious\SMS\Backup\Order Details Database" B ON B.OrderDetailID = C.OrderDetailID
;FixedOrderDetails FALSE
 
INSERT INTO "C:\Program Files (x86)\Cyrious\SMS\Fixed\Order Details Database"
SELECT * FROM "FixedOrderDetails"

You will need to change the ending of the query above to the following to work on the Estimate Details table

FROM "Estimate Details Database" C
LEFT JOIN "C:\Program Files\Cyrious\SMS\Backup\Estimate Details Database" B ON B.OrderDetailID = C.OrderDetailID
;FixedEstimateDetails FALSE
 
INSERT INTO "C:\Program Files\Cyrious\SMS\Fixed\Estimate Details Database"
SELECT * FROM "FixedEstimateDetails"

In the above query you may or may not need to remove the following lines for it to complete successfully (Especially if the data is from 8.6 or earlier):

C.ModifiedByComputer,
C.ModifiedDate,

++Also in some cases you may have to switch the ordering of B.ModifierXML with that of C.RoyaltyGroupID.

++This is usually indicated with the error: "Type mismatch in expression" when executing the query.

Additional Notes

  • At times you may need to import these records into a totally new blank set of database tables. This helps to ensure that you have a healthy set of tables that you will use going forward.

Version Information

  • Entered : 08/13/2010

Related SQLs

Related Errors

SMS Error - Blob Error