qboDB solution *History table changes, deprecated field type changes and related items

posted Oct 12, 2017, 10:20 AM by Kevin Foley
SVN revision 35145 

Almost every table in every project in the qboDB solution has been modified.

*History table changes.
The CreatedDate and CreatedPersonID fields have been re-added to the *History tables 
*History table structures now match their source such as Loan and LoanHistory
Not only were fields added or removed, type mismatches were also corrected.
Such as 
CollectionTemplateHistory.CustomerStatusList BIT vs VARCHAR(50)
ContractHistory.ContractID BIGINT vs INT

Since *History tables now match their source, some fields are being removed.
!! Please review these changes to make sure you do not lost historical data required by your applications.

*HistoryInsert trigger changes
Most if not all *HistoryInsert triggers were changed to address three issues
1) addition of CreatedDate and CreatedPersonID
2) addition of fields missing from their source due to base changes not carried over
3) the addition of the Except clause to exclude non changing row updates from overpopulating the *History tables
This may break some systems that rely on *History table RevisionDates to track row touches, regardless of any data being updated.

Deprecated fields NTEXT and IMAGE have been converted to VARCHAR(MAX)

Offer
Change deprecated types: NTEXT to VARCHAR(MAX) on fields
Terms, SourceXml and MetaXML

Holiday
Change deprecated types: NTEXT to VARCHAR(MAX) on fields
Description

Attachment
Change deprecated types: IMAGE to VARCHAR(MAX) on fields
FileContent

The change to Attachment will cause a table rebuild.

SQL create table syntax change

Table file group specifier is now only included on the clustered indexes.
A file group was normally provided with the table creation and on the clustered index.
This is no longer the SQL pattern ince the clustered index file group location overrides the table location.

-- New
CREATE TABLE [dbo].[Attachment] (
[AttachmentID]
...
CONSTRAINT [PK_Attachment] PRIMARY KEY CLUSTERED ([AttachmentID] ASC) ON [FGData],
...
) TEXTIMAGE_ON [FGBlobData];
GO

vs

-- Old
CREATE TABLE [dbo].[Attachment] (
[AttachmentID]
...
CONSTRAINT [PK_Attachment] PRIMARY KEY CLUSTERED ([AttachmentID] ASC),
...
) ON [FGData] TEXTIMAGE_ON [FGBlobData];
GO

Tables losing fields

qbo.Standard
BusinessRuleHistory ( multiple fields )
CollectionMemberHistory.OldcollectionMemberID 
DecisionDelayHistory.MilestoneID
DecisionHistory.OldDecisionID
DecisionStepTemplateHistory.Batch
SmartWorklistMemberHistory.AuditImportFormID
SmartWorklistTemplteHistory.WorklistXslt

qbo.Mortgage
Bankruptcyistory.LoanID
Bankruptcyistory.ProcessHistoryID
LitigationHistory.ProcessHistoryID
LoanTransactionHistory.PublicRecordHistoryID

qbo.Debt
DebtCollectionHistory.ProcessHistoryID
PaymentPlanHistory.PaymentPlanStatus

qbo.Credit
No deletions

qbo.CourtCase 
No deletions

qbo.Complaint
No deletions

Before you deploy...

The above documents most of the changes, possible that a few may have been missed.
As such please review the modifications to your target database by doing a schema compare with Visual Studio or Redgate

Before deploying an update from trunk via Visual Studio, 
  • please make sure you have installed SSDT 17.2 for VS2015.
  • configure the advanced settings in your deployment profile to ignore column order

If you are not using VS2015, please review the notes from the Microsoft link above for the proper SSDT version that supports ignoring column order.


Comments