|
|
Posted by Dan Guzman on 10/21/51 11:31
> 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.
I agree that SELECT * should be avoided but I wouldn't go as far as to say
that sp_refreshview isn't needed when an explicit column list is
specified. The view meta data may still get out-of-sync. This probably
isn't a show-stopper but this looks to be like an accident waiting to
happen.
CREATE TABLE Table1(Col1 int)
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1'
GO
ALTER TABLE Table1
ALTER COLUMN Col1 varchar(20)
GO
EXEC sp_help 'View1'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9709632348F5Yazorman@127.0.0.1...
> 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
>
Navigation:
[Reply to this message]
|