Post date: Oct 12, 2017 5:20:58 PM
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
https://www.youtube.com/watch?v=AuVpmu9CKRY
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
You can download SSDT 17.2 here: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
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.