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
---END-----
|
References: https://social.technet.microsoft.com/wiki/contents/articles/33565.how-to-check-if-any-view-has-metadata-binding-errors-after-db-refresh.aspx
Tags: Views, sql-view,sql-server,sp_refreshview all views,how to refresh data source view in ssas,sql server view,ssas data source view add column,
sp_refreshview all views,create view with schemabinding
0 comments:
Post a Comment