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) > Error while executing script: Could not find stored procedure 'dbo.swsp_DropIndex'

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

Created by Malik Haider, last modified by MindTouch on Jun 23, 2016

Views: 545 Votes: 0 Revisions: 5

Overview

This article provides brief information and steps to resolve the following error when running the Configuration Wizard: 

Configuration Wizard cannot complete due to error message:

Database Configuration Failed:

Error while executing script- Could not find stored procedure 'dbo.swsp_DropIndex'.
Could not find stored procedure 'dbo.swsp_DropIndex'.
Could not find stored procedure 'dbo.swsp_DropIndex'.
Could not find stored procedure 'dbo.swsp_DropIndex'.

Environment

NPM version 10.6 or later

 

Cause 

dbo.swsp_DropIndex is only be available once  you have upgraded to NPM 11.5.2. If it is not available after the upgrade, create the stored procedure manually.

 

Resolution

1. Create a backup of the Orion database.

2. Using SQL Management Studio, connect to the SQL server hosting Orion database.

3. Go to the Database > Orion Instance > Programmability > Stored Procedures.

4. Select Stored Procedures, and then click New Query.

5. In the Query box, enter the following SQL Script:

Note: The SQL Script below will manually create the dbo.swsp_DropIndex

 

USE [SolarWindsOrion]
GO

/****** Object:  StoredProcedure [dbo].[swsp_DropIndex]    Script Date: 11/27/2015 11:05:28 ******/
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

 

 

 

6. Click Execute to create the dbo.swsp_DropIndex.

7. Re-run the Configuration Wizard.

 

 

 

 

Last modified

Tags

Classifications

Public