Share via

How to check if any view has metadata binding errors after DB refresh

  • Article

Scenario:

In my recent project after Source Database refresh we need to push to target server. We have views created in separate database by referring to target database and Views will be used in Cube. Here the problem we have seen is whenever we do some deployments and then DB refresh, we do face some metadata issues. So, we need to check each and every view to find out metadata issues by selecting view and its tedious task for us to check 100-150 views.

Solution:

We have created one automation script which will give us the view name, error message and error linen umber

Code snippet: 

/** Script to find views which have metadata error*/
DECLARE @ViewName VARCHAR(255);
DECLARE @ViewErrors TABLE
  (
vw_name VARCHAR(255) ,
err_msg VARCHAR(MAX),
err_line INT
  );
 
DECLARE Error_Views CURSOR 
FOR   
SELECT DISTINCT s.name+'.'+v.name  
FROM sys.views V
JOIN Sys.schemas  S
ON V.schema_id=S.schema_id    
     
OPEN Error_Views
 
FETCH NEXT FROM Error_Views 
INTO @ViewName
 
WHILE @@FETCH_STATUS = 0 
 BEGIN
     
BEGIN TRY    
EXEC sp_refreshview @ViewName;
 
IF @@error <> 0
BEGIN
RAISERROR(15165,-1,-1,@ViewName)   
END
 
END TRY
 BEGIN CATCH
       
INSERT  INTO @ViewErrors  
SELECT  @ViewName,ERROR_MESSAGE(), ERROR_LINE(); 
 END CATCH
     
 FETCH NEXT FROM Error_Views INTO @ViewName;
 
  END
   IF EXISTS ( SELECT 1 FROM   @ViewErrors ) -- Check if any erros exists
  BEGIN  
 SELECT  *  FROM    @ViewErrors;
  END
 
CLOSE Error_Views
DEALLOCATE Error_Views