Testing for Broken Views


We recently found a view in our warehouse which had only been created for demonstration purposes but the underlying tables had changed over time.  The view was broken and, of course, someone found it.  This necessitated a remediation which would identify broken views and create a record of the issue.  Ideally, there would never be broken views if this process was in place so we could also create a record showing how few issues we have had.  

At first, I thought of creating a SQL Agent job which would query the view and fail when the view was not working. But this seemed like a lot of work as views got added over time.  So, something more flexible was called for.

I wanted to create a procedure that would test all of the views in a database and record the relevant data if the view broke.  I created a procedure with a try-catch block and selected the top(1) record from the views and when there was a failure the catch block would insert the record into a table.  This would have been pretty awesome, especially since the new STRING_AGG function in SQL 2017 makes dynamic SQL much easier to write.  But there was a problem, the view would fail but the catch block code never executed.  It turns out that broken views don't rise to the necessary level to warrant a catch.  It turns out the failed stored procedures do rise to the correct error level so the code below uses the procedure sp_refreshView.  I included the database creation, the table creation, and the stored procedure.


/**Check if the metadata_db exists and create it if not. **/

IF NOT EXISTS(SELECT name from master.dbo.sysdatabases where name = 'MetaData_DB')
CREATE Database MetaData_DB;

/** Create the Broken View table **/
USE [Metadata_DB]
GO
CREATE TABLE [dbo].[BrokenViews](
[Database_Name] [nchar](50) NULL,
[Schema_Name] [nchar](50) NULL,
[Table_Name] [nchar](100) NULL,
[Date_Broken] [date] NULL
) 
GO


/** Create the procedure which will test views and if the view is broken add a record to the brokenViews table **/

USE <database name>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[TestViews]
  AS
       BEGIN
       DECLARE @SQL as nvarchar(max)
       SELECT @SQL = STRING_AGG(' Begin Try  exec sp_refreshview  ' + '''' + SCHEMA_NAME(schema_ID) + '.' + name + '''' + 
  ' END TRY BEGIN CATCH INSERT INTO  Metadata_DB.dbo.BrokenViews VALUES( ' + '''' + DB_NAME() + '''' + ', ' + '''' + SCHEMA_NAME(schema_ID) + '''' + ', '  + '''' + name + '''' + ', ' + '''' + CAST(CAST(GETDATE() AS DATE) AS VARCHAR) + '''' + ') END CATCH' , ';') 
  FROM sys.objects
  WHERE type = 'v'
  EXEC sp_executeSQL @SQL
  END




Popular posts from this blog

Power BI Report Server: "An Error Has Occurred" or Power BI Report Server: "My Power BI Report is Coming up Blank" or Power BI Report Server: "401 and 403” Errors From Nowhere

Image
The information below is an extremely brief summary of the information collected to solve the elusive problem.  There are points I left out because I could not find supporting documentation and I did not want to release suppositions into the universe (why does an NTLM only issue impact a system using kerberos?).  I also want to thank my friend Josh Corrick for staying the course and seeing this through to the end.  See Josh's blog here:  https://corrick.io/blog/ Without warning, our Power BI Report Server began having issues presenting Power BI reports.   The reports would appear to render but would eventually return a blank report canvas.   There was not an error presented and this was impacting all Power BI reports.   An important note is the Power BI reports had worked as expected previously and no changes had been made to the report server such as upgrades or configuration changes. Subsequently, and seemingly unrelated, an error notificatio...
Read more

SQL SERVER 2017: STRING_AGG

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function....
Read more

Dynamic Label Positions With SSRS

Image
On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome.  When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits.  On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall. Example of bar chart with label set to bottom. Same chart with the middle column label set to top. First we thought about using Smart Labels  but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical.  As you can see in the examples above the size of the numbers would make horizontal labels messy. So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough. In Report Builder, go to your chart and select a specific column - not just the chart....
Read more