While this works great when views are valid it crashes the proc if a view won't refresh correctly.

It seems to be fine when running this with the sp_refreshsqlmodule on functions and stored procedures...if validation fails it reports it and lets you move on to the next one.

On views it always errors out...it shows the validation error and then crashes on the next view with the following error:

refreshing...[dbo].[vwA]

Validation failed for : [dbo].[vwA], Error:Invalid column name 'address'.

refreshing...[dbo].[vwB]

Validation failed for : [dbo].[vwB], Error:The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Any ideas on how to trap this and move on to the next...I'm trying to generate a list of faulty views.

thanks

PS Try Catch does NOT work on views....works like a charm on Stored Procs

here is my try catch logic:

WHILE @Cnt <= @Tot BEGIN

SELECT @spname = spname

FROM @v-2

WHERE RecID = @Cnt

PRINT 'refreshing...' + @spname

BEGIN TRY

EXEC sp_refreshsqlmodule @spname

END TRY

BEGIN CATCH

PRINT 'Validation failed for : ' +

@spname + ', Error:' +

ERROR_MESSAGE()

END CATCH

SET @Cnt = @cnt + 1

END