Perform Database Maintenance and Delete Stored Procedures and Views

Perform SQL Server Database Maintenance

Performing maintenance on your PI Data Store is optional; however, it is recommended. Perform SQL Server database maintenance, including SQL Server Index Defragmentation and App Data Store Maintenance in accordance with your organization’s data retention policies. These options can be accessed from the Settings > Data Store Configuration menu in the PI management console.

 

Delete Stored Procedures and Views

Run the following SQL Query against your PI database to ensure that stored procedures and views do not carry forward to your upgraded database. The query removes stored procedures and views, and also removes the version tracking from the database. This triggers a rewrite of the stored procedures the first time you launch your upgraded PI management console and it connects to the database.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur
DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'v'
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

DELETE from tbl_RouletteDatabaseVersionInfo

To run the query:

1. In SQL Server Management Studio, right-click your PI database, and then select New Query from the menu.
2. Copy and paste the above query into the blank query window on the right.
3. Click Execute.
4. Close SQL Server Management Studio without saving the query.