Constraint check issues when migrating Orion SQL database to the cloud (Microsoft Azure or Amazon AWS)

5/19/2020 12:17 AM

5/21/2020 2:30 PM

When you run the Extract Data-Tier Application or Deploy Database to Microsoft Azure SQL tasks in the Microsoft SQL Management studio, you might encounter errors like the following one:

Error:
When using task Extract Data-Tier Application or Deploy Database to Microsoft Azure SQL in Microsoft SQL Management Studio errors like:
Error validating element [dbo].[CHK_LoadAverage_Hourly_20190117]: Check Constraint: [dbo].[CHK_LoadAverage_Hourly_20190117] has an unresolved reference to object [dbo].[LoadAverage_Hourly_20190117].[TimeStampUTC ]

 

Orion Platform

There is a redundant space in a column name.

To resolve the issue, run the following script:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

    BEGIN TRANSACTION
    DECLARE @definition NVARCHAR(255)
    DECLARE @table NVARCHAR(255)
    DECLARE @constraint NVARCHAR(255)
    DECLARE @getConstraints CURSOR
    DECLARE @sqlCommand NVARCHAR(255)
    DECLARE @correctConstraint NVARCHAR(255)
    DECLARE @errorMsg VARCHAR(MAX)
    DECLARE @errorSeverity INT
    DECLARE @errorState INT
    DECLARE @columnName NVARCHAR(255)

    SET @getConstraints = CURSOR FOR
    SELECT t.name, c.Name, c.definition FROM sys.check_constraints c
    INNER JOIN sys.tables t ON t.object_id = c.parent_object_id
    WHERE c.name LIKE N'CHK_CiscoBuffers_Detail%' or c.name LIKE N'CHK_CiscoBuffers_Hourly%' or c.name LIKE N'CHK_CiscoBuffers_Daily%'
	or c.name LIKE N'CHK_CPULoad_Detail%' or c.name LIKE N'CHK_CPULoad_Hourly%' or c.name LIKE N'chk_CPULoad_Daily%'
	or c.name LIKE N'CHK_LoadAverage_Detail%' or c.name LIKE N'CHK_LoadAverage_Hourly%' or c.name LIKE N'CHK_LoadAverage_Daily%'
	or c.name LIKE N'CHK_ResponseTime_Detail%' or c.name LIKE N'CHK_ResponseTime_Hourly%' or c.name LIKE N'CHK_ResponseTime_Daily%'
    or c.name LIKE N'CHK_VolumeUsage_Detail%' or c.name LIKE N'CHK_VolumeUsage_Hourly%' or c.name LIKE N'CHK_VolumeUsage_Daily%'

    BEGIN TRY
        OPEN @getConstraints
        FETCH NEXT
        FROM @getConstraints INTO @table, @constraint, @definition
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (CHARINDEX('LoadAverage',@table) > 0)
		SET @columnName = 'TimeStampUTC'
	    ELSE
                SET @columnName = 'DateTime'

            IF (CHARINDEX(@columnName + ' ', @definition) > 0)
            BEGIN
                SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] DROP CONSTRAINT ['+ @constraint +']';
                EXEC (@sqlCommand)

                SET @correctConstraint = REPLACE(@definition, @columnName + ' ', @columnName)
                SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] WITH CHECK ADD CONSTRAINT ['+ @constraint +'] CHECK '+ @correctConstraint;
                EXEC (@sqlCommand)
            END
            FETCH NEXT
            FROM @getConstraints INTO @table, @constraint, @definition
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        SELECT @errorMsg = Error_Message() + ' ' + @sqlCommand, @errorSeverity = Error_Severity(), @errorState = Error_State()
        RAISERROR (@errorMsg, @errorSeverity, @errorState);
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH

    CLOSE @getConstraints
    DEALLOCATE @getConstraints