|
|
Posted by Erland Sommarskog on 04/02/06 13:36
[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
Navigation:
[Reply to this message]
|