I'm sure this is an easy one...Error trap to skip over a "bad" object.

Hello,  I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it.  It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed.  Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped.  I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.


DECLARE @DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @DatabaseObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @DatabaseObject
Print @DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

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.

> ...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_SCHE
MA) +
'.' +
 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.
>
>

[My newsserver had an outage, and my original post got lost. Now that it
's
back, I'm reposting]

(rod.weir@gmail.com)  writes:
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement.

Did anyone tell you that this is bad practice? :-)

> 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.

The problem is that there are quite few errors that abort the batch, and
those you cannot trap easily in SQL 2000. I seem to recall that refreshview
errors belongs to this group. The linked-server trick is a serious kludge,
but for this case it could be worth the pain.

Then again, if you are a VB developer, just code the loop in a VB program
or in VB script. That's probably easier than setting up linked servers for
this task.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Dan,

Many thanks for your response.  This code does exactly what I'm after.
It skipped over the bad queries and kept refreshing the good ones.  I
modified the following line to speed it up a little bit.

EXEC ('SET FMTONLY ON SELECT * FROM ' + @DatabaseObject + 'Where 1=0')

Notice the Where 1 = 0 clause?  Much quicker now.

Thanks Dan and Erland.  Problem solved.

p.s. Erland.  I am going to start another thread on the evils of
embedded queries.  I have heard a lot of people say that this is a bad
practice, however I've never heard any really compelling evidence to
say why.