we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities. The application is 3ed party, and at times makes changes to the databases which render views invalid on our "reporting" server for the application.
Is there a way I can roll through all the views on my report server (There are about 70 of them) with TSQL to find the Binding Errors after Tuesdays Depolyment?
Errors I would like to find are:
Executed as user: <>. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180) Could not use view or function 'PAT.dbo.Reorders' because of binding errors. [SQLSTATE 42000] (Error 4413) All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. [SQLSTATE 42000] (Error 205). The step failed.
Is there a way to do this programatically, or do I just have to keep right clicking each view and trying to see if it comes back with data?
Thanks,
Best Answer
Something like the following should be good enough to get you started. It should be quite lightweight as well.