Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > Configuration Wizard error while executing script: Could not find stored procedure 'dbo.swsp_DropIndex'

Configuration Wizard error while executing script: Could not find stored procedure 'dbo.swsp_DropIndex'

Created by Malik Haider, last modified by Jessica Solis on Nov 13, 2017

Views: 201 Votes: 6 Revisions: 12

Updated October 11, 2017

Overview

The following error appears while running the Orion Configuration Wizard after migrating the Orion database to another server by restoring database:

 

 Configuration wizard Error while executing script : Could not find stored procedure 'dbo.swsp_DropIndex' 

 

 

cwerrrr.PNG

Environment

  • NPM 11.5 and later

Cause 

This occurs when the stored procedure failed for the backup and is missing from the stored procedure list.

Resolution

  1. Run the following SQL script through SQL Server Management Studio to recreate the stored procedure called [dbo].[swsp_DropIndex]

    Note: Change the name of database (DATABSE-NAME) to match the database name you have assigned to your Orion installation.
     
    USE [DATABASE-NAME]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[swsp_DropIndex]
    (
        @parSourceTable NVARCHAR(255),
        @parColumnToBeDropped NVARCHAR(255)
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        
        -- here we must remove anything which will prevent us for doing DROP COLUMN
        -- it means all indexes, constraints, foreign keys etc.
        -- check parameters    
        IF(@parSourceTable IS NULL OR @parColumnToBeDropped IS NULL )
        BEGIN
            PRINT N'Invalid parameters'
            RETURN 1;
        END
        -- check if table exists
        IF ( OBJECT_ID(@parSourceTable) IS NULL )
        BEGIN
            PRINT N'Table ' + @parSourceTable + N' does not exist.'
            RETURN 2;
        END
        -- check if column exists
        IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_NAME = @parSourceTable AND COLUMN_NAME = @parColumnToBeDropped)
        BEGIN
            PRINT N'Column ' + @parColumnToBeDropped + N' does not exist.'
            RETURN 2;
        END    
        -- let's get list of constraints
        DECLARE @dependencies AS TABLE
        (
            DependencyName NVARCHAR(255)
        );
        INSERT INTO @dependencies
        SELECT i.name
        FROM sys.indexes i
            JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
            JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
            JOIN sys.objects o ON o.object_id = i.object_id
        WHERE o.name = @parSourceTable AND i.type=2 AND c.name = @parColumnToBeDropped AND is_unique_constraint = 0
        DECLARE @dep_name NVARCHAR(MAX)
        DECLARE @type NCHAR(1)
        DECLARE dep_cursor CURSOR FOR 
            SELECT * FROM @dependencies;
        OPEN dep_cursor
        FETCH NEXT FROM dep_cursor 
        INTO @dep_name;
        DECLARE @sql NVARCHAR(max)
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @parSourceTable + ']'
            -- PRINT @sql
            BEGIN TRY
                EXEC sp_executesql @sql
                FETCH NEXT FROM dep_cursor 
                    INTO @dep_name;
            END TRY
            BEGIN CATCH
                -- removing constraints failed, continue has no reason
                CLOSE dep_cursor;
                DEALLOCATE dep_cursor;
                PRINT 'Drop constraint ' + @dep_name + ' failed';
                RETURN 3;
                
            END CATCH
        END
        CLOSE dep_cursor;
        DEALLOCATE dep_cursor;
        RETURN 0;
    END
    GO

     

  2. Rerun the Configuration Wizard.

 

Last modified

Tags

Classifications

Public