Perform Database Maintenance and Delete Stored Procedures and Views
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.
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:
- In SQL Server Management Studio, right-click your PI database, and then select New Query from the menu.
- Copy and paste the above query into the blank query window on the right.
- Click Execute.
- Close SQL Server Management Studio without saving the query.