Other: Mass-Altering a Universal Database
The Universal Schema is Continuously Evolving
The Universal Database schema continually undergoes slight
modifications in order to continually upgrade the migration experience.
In most cases, these changes are minor and will not interrupt an in-progress
migration, however, sometimes you may need to make manual adjustments to a
legacy UM-database to make it compatible with the current version of the
Universal Migrator.
Identifying a Database that Needs Upgrading
If you are attempting to perform a migration and you get
messages such as:
- Invalid column name 'Final_DisplayOrder'
-
Invalid column name 'Final_Created_OnBehalfOf'
or
this means the schema has changed and you need to apply
manual adjustments to make the legacy database compatible with the current
version of the Universal Migrator.
Running a Mass-Alter Script
To perform a mass-alter script, run a command like the
following:
-
exec sp_MSforeachtable '
-
--Notice that single quotes are put in twice.
-
alter table ? add Final_Created_OnBehalfOf nvarchar(MAX) default ''''
-
'
Related Articles
Audit Scripts: Row Counts and Progress Monitoring
This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...
Clear all Result_Ids
/* WARNING!!! THIS IS A DANGEROUS SCRIPT!!! MAKE SURE YOU ARE USING THE RIGHT DATABASE!!! This script will clear out all Result_Ids in a database. */ USE [DATABASENAME]; EXEC sp_MSforeachtable UPDATE ? SET Batch_Number = 0, Result_Id = '''' '
Other: Total Record Counts
The following SQL script will count all records in all tables in a database. DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableName], [RowCount]) EXEC sp_MSforeachtable 'SELECT ''?'' ...
Custom Fields: Creating Custom Fields via Queries
Sometimes you may need to create a new custom field to handle an unmapped column in a database. The queries below will help you do that. -- This is the ID we are giving our custom field definition. DECLARE @FieldId NVARCHAR(MAX) = 'ConNo --- CUSTOM' ...
Audit Scripts: Activity Billed/Unbilled row counts
After you have backed up financial information like Time Entries, Expense Entries, and Fee Entries it is a good idea to audit the results prior to restoring data to the destination system. For example, some Universal Destinations support restoring ...