Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

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: 295 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
22:19, 22 Jun 2016

Tags

Classifications

Public