Just updating and re-emphasizing a previous blog post about refreshing view metadata (see previous blog post).
Future iMIS Database Upgrades will validate all view metadata by running the script in my previous Blog entry on the subject (reproduced below). Therefore, it's vitally important that all existing iMIS users run this script against their databases prior to upgrading to the next major version of iMIS in order to avoid errors during the database upgrade.
To recap: whenever a user creates a "View" in Microsoft SQL Server, the view is created with the schema information (column data types and sizes) that are present at the time the view was created. For example, if you create a view that's based on a column that is nvarchar(30), that data type is cached in the view itself. If, after the view is created, the column's type is changed (say, to nvarchar(60)), or if the column is renamed or dropped all together, the view still has its old cached value. The view is now "out of sync" with the schema. Using the view can cause unexpected issues (like truncating a 60 character string to 30 characters), or can cause errors (if the column no longer exists).
Microsoft provides a way of "refreshing" the view metadata through a stored procedure: sp_refreshview. Calling this causes SQL Server to go and re-fetch all the information, updating all the column types and lengths and validating the view schema against the schema of the tables it is defined upon. If the column has been renamed or removed, the call to sp_refreshview throw an error, letting you know what the problem is.
Over the years, it's quite possible that user-created views have become out of date as the iMIS schema has changed from version to version. Previously, these views becoming "out of sync" happened silently... there was nothing to notify you that your view was out of date, and using it could give incorrect results, or cause errors. This is a bad thing. We want to ensure that all views based on the iMIS Schema are valid, including those created by third parties, resellers, clients, and customers. Therefore, future versions of iMIS will refresh all view metadata at upgrade time.
It is highly recommended that every iMIS DB user run the script below on their database prior to upgrading to a new version of iMIS. And if the version of iMIS you're upgrading to is 15.1.1 or earlier, you shoudl run it again afterwards. This simple sanity check will help you ensure that all your views are accurate and up to date.
If any errors are reported, you'll want to fix up the problem views immediately. This may involve renaming a column in your view definition, or droping a column all together, or getting the data from a new or different table. It may be that the view itself is simply obsolete and can be dropped. Once the view is properly fixed, run the script again to ensure it runs through cleanly. You can then proceed with an upgrade with confidence.
The following is a little script that can be run on your database. It will refresh the views for all schema objects in the 'dbo' schema (which includes all IMIS tables). If you have a lot of tables in your database that exist in other schemas, you may wish to eliminate the clause that limits the script to only 'dbo', in order to ensure everything is refreshed and up to date. This can be run at any time, and it is completely non-destructive.
--Refresh the underlying metadata of all views in the 'dbo' schema
DECLARE @viewName AS VARCHAR(255)
DECLARE listOfViews CURSOR FOR
SELECT v.[TABLE_NAME]
FROM INFORMATION_SCHEMA.VIEWS v
LEFT OUTER JOIN sys.sql_dependencies d ON v.[TABLE_NAME] = OBJECT_NAME(d.object_id)
WHERE v.[TABLE_SCHEMA] = 'dbo' AND
(d.[class_desc] IS NULL OR d.[class_desc] <> 'OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND')
GROUP BY v.[TABLE_NAME]
ORDER BY v.[TABLE_NAME]
OPEN listOfViews
FETCH NEXT FROM listOfViews into @viewName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
PRINT @viewName
EXEC sp_refreshview @viewName
FETCH NEXT FROM listOfViews INTO @viewName
END
CLOSE listOfViews
DEALLOCATE listOfViews
*** Updated to handle schema-bound views (such views exist in the product as of an update to the 15.1.3 release)
For SQL Server 2005 users...
I've been asked if there were a way to get a complete report of the bad views all at once, rather than using the above script, which fails on the first error, requiring you to stop and fix the error before running it again to see the new bad view. The answer is yes... as long as you're using SQL Server 2005.
The following script will run through all the views and locate all the bad views that need attention. If you run this from SQL Server Management studio, you'll get two kinds of output. On the "Results" tab, you'll see a result set for each bad view that has the name of the view, and the column that is bad. On the "Messages" tab, you'll get a succinct list of bad views and a total count.
While this modified script will give you a good idea of all the errors and bad views, as well as the root causes for each, be aware that if there are multiple columns on the same view that are bad, SQL Server only reports the first one it encounters. Thus you'll still want to run this again after fixing all the reported problems, just to ensure that there aren't any others. However, the number of iterations should be drastically reduced.
Final note: if you've renamed the column in the view, the column name listed in this output is the original column name in the Table, not the name used in the View.
Here is the script:
-- Refresh the metadata for all views -- in the 'dbo' schema SET NOCOUNT ON DECLARE @viewName AS VARCHAR(255) DECLARE @count int DECLARE listOfViews CURSOR FOR SELECT [TABLE_NAME] FROM INFORMATION_SCHEMA.VIEWS WHERE [TABLE_SCHEMA] = 'dbo' ORDER BY [TABLE_NAME] OPEN listOfViews FETCH NEXT FROM listOfViews into @viewName SET @count = 0 WHILE (@@FETCH_STATUS <> -1) BEGIN BEGIN TRY EXEC sp_refreshview @viewName END TRY BEGIN CATCH PRINT @viewName + ' has ERRORS: ' + ERROR_MESSAGE() SELECT @viewName AS [ViewName], ERROR_MESSAGE() AS [ErrorMessage] SET @count = @count + 1 END CATCH FETCH NEXT FROM listOfViews INTO @viewName END CLOSE listOfViews DEALLOCATE listOfViews PRINT 'Total Views with errors: ' + CAST(@count AS nvarchar(10)) SET NOCOUNT OFFAnd once again, you may wish to remove the "WHERE [TABLE_SCHEMA] = 'dbo'" clause in the cursor definition if you have a lot of views that reference iMIS tables that don't live in the "dbo" schema.
If you see errors in a view whose name starts with "vBo", then you may simply wish to publish the associated Business Object in BOD, and that should fix it up. All views that start with "vBo" are automatically generated views, created by publishing business objects (the business object name is the part after the "vBo").