Posted by Erland Sommarskog on 10/04/06 11:31

Susie-Q (anonymous@devdex.com) writes:
> However, I would like to know if my script could be written differently.
> The sp_refreshviews raises a 'fatal' error when it cannot recreate a
> view. I had originally tried to capture the error code using @@error but
> as soon as the 'severe' error was encountered it kicked me out of the
> loop. I found that I could validate the view with the above select
> statement. It also fails when it encounters an error message but allows
> me to capture it and continue to loop. Do you have any suggestions or
> insight into a better way to handle the error issue? I need to loop
> through the views in each database. I never know what views are or are
> not in them.

In SQL Server 2000, errors can have different effects. Some terminate the
current statement, some the current scope and some the current batch.
And there is no way you can catch this. I would suggest that you implement
the the loop in some client language, VBscript, Perl or whatever your
prefer.

In SQL 2005, there is vastly improved error handling, and there you can
catch all errors.

Of course, the best workaround is to get all those SELECT * in the views
out of the house. Then you don't to bother about sp_refreshview.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]