Other: Mass-Altering a Universal Database

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:

  1. Invalid column name 'Final_DisplayOrder'
  2. 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:

  1. exec sp_MSforeachtable '

  3. --Notice that single quotes are put in twice.
  4. alter table ? add Final_Created_OnBehalfOf nvarchar(MAX) default ''''

  6. '

 




    • 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 ...