Sage Evolution
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata

Problem

When UDF's are created, but the views are not updated, you might get an error like the image below when running a report. 

Solution

Run the below SQL to refresh all views on the Sage database to solve this problem. 

--REFRESHING VIEWS AFTER CREATING New Fields!:
DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
	BEGIN TRY		
		EXEC SP_REFRESHVIEW @ViewName
		PRINT 'View ''' + @ViewName + ''' has been refreshed.'
	END TRY
	BEGIN CATCH
		BEGIN
			SELECT
			@ViewName as ViewName
			,ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
		END
	END CATCH
	FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews