|
|
Posted by Dan Guzman on 04/01/06 20:24
> ...you examine @@Error and ignore or continue in the loop?
Some errors will abort the batch so you are SOL after the error. If the
linked server doesn't work for you, you might try preceeding the
sp_refreshview with a select statement with SET FMTONLY ON. That will allow
you detect the error and skip the sp_refreshview for problem views.
DECLARE @DatabaseObject nvarchar(261)
DECLARE ObjectCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW'
OPEN ObjectCursor
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ObjectCursor INTO @DatabaseObject
IF @@FETCH_STATUS = -1 BREAK
PRINT 'Refreshing view ' + @DatabaseObject
EXEC ('SET FMTONLY ON SELECT * FROM ' + @DatabaseObject)
IF @@ERROR = 0
BEGIN
EXEC sp_refreshview @DatabaseObject
PRINT 'View ' + @DatabaseObject + ' refreshed'
END
ELSE
BEGIN
PRINT 'Error refreshing view ' + @DatabaseObject
END
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
<rod.weir@gmail.com> wrote in message
news:1143705825.644611.51750@u72g2000cwu.googlegroups.com...
> Thanks Erland,
>
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement. When the
> underlying view changes (new column etc), the parent view does not pick
> up the new column in the embedded view that it references.
>
> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
>
> Perhaps just after the following line...
>
> EXEC sp_refreshview @DatabaseObject
>
> ...you examine @@Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.
>
> I'll give your website a read. Thanks again.
>
>
Navigation:
[Reply to this message]
|