Catching an Error and Preserving Query Flow

Here's the code I have:

declare @table_name varchar(8000)
declare cursor1 insensitive scroll cursor for
select table_name from information_schema.views
open cursor1
fetch next from cursor1 into @table_name
while(@@fetch_status=0)
begin
exec sp_refreshview @table_name
fetch next from cursor1 into @table_name
end
close cursor1
deallocate cursor1

Unfortunately maybe not.  Certain errors in SQL Server are not "trappable", so the batch will abend no matter what you do.

Small comfort: this will be possible in SQL Server 2005.

SoftEng: I prefer this pattern

Declare @C... Cursor
...


Declare @More Bit, ...


Set @More = 1
Open C...


While (@More = 1)
  Begin
   
    Fetch Next From C...
    Into ....

    If (@@Fetch_Status != 0)
      Set @More = 0
    Begin
      ...
    End
  End


Close C...
Deallocate C...


I have no idea why I seem to be the only one preferring this. Well, anyhoo, the main reason: only one FETCH statement. Try writing a cursor with 34 columns one time, and you'll see how that could be a Good Thing (TM).

looks interesting...
I might just try that next time.
and i know what you mean about fetch.
I do a lot of db to mainframe parsing and the column count can get into the 60's so far....

Please do -- it only takes once to see the point  :-)

Thanks for looking into this for me guys.

Unfortunately, I tried to get a row from the view before running the refresh function as well and ran into the same error.

If it matters, the errors I get are #208 (Invalid object name 'table_name') and #4413 (Could not use view or function 'view_name' because of binding errors).

2005 you say?  That's just around the corner. =P

You could create a DTS package with an execute SQL task that runs another DTS package that refreshes a single view... it'd be slow though.

did you actually try my proc? it worked on my server just fine...

you will see the errors from the execute sql proc but the print statements will verify that the proc runs thru all of the views.

Aha, when I ran your procedure and looked over the results, I found the view in question was the last one run.  I just assumed it was the last one because it errored out and stoped the process.  I never thought to check to see if it was last simply because it was the most recently created one.

Anyway, I reordered them within the cursor to check and yes, it does work.  Thanks!

SoftEng007 - I'm new at this, but was hoping you could explain your code to me a bit more.  I think it'll do what I'm looking for, but let me explain.

I want to automate the process of regenerating our views whenever we make changes to the base tables.  Is there a better way to do this?

Thanks for any help!

sure here goes:

use sp_executesql and look for error:

declare @table_name varchar(8000) -- variable to hold name of view
declare @rc int                               -- retrun code from EXEC we check this to make sure exec returns 0 for success
declare @sql nvarchar(1000)           -- variable to hold dynamically created statement we want too run
declare cursor1 insensitive scroll cursor for       -- standard
select table_name from information_schema.views  -- get the name of all views
open cursor1
fetch next from cursor1 into @table_name
while(@@fetch_status=0)
BEGIN
 if left(@table_name,3)<>'sys' -- dont run against sys views
 BEGIN
       SET @sql = N'select top 1 * from ' + @table_name
       execute @rc = sp_executesql @sql                   --try to grab a row from the view. this is to see if the view is still
                                                                            -- working. bad underlying tables will cause this exec to fail.
       print @rc -- verify proc runs
       if @rc = 0   -- if it grabs a row then refresh view. if rc=0 then the exec ran ok and we can refresh view
       begin
          print 'running against ' + @table_name
          exec sp_refreshview @table_name
       end
 END
 fetch next from cursor1 into @table_name
END
close cursor1
deallocate cursor1

Thanks SE,
I'll give that a try.  One question, if a view is not working, will it skip that view and move on to the next, or will it cause the script to abort?

Thanks for the hlep!

that the whole idea behind this section:

      SET @sql = N'select top 1 * from ' + @table_name
       execute @rc = sp_executesql @sql                   --try to grab a row from the view. this is to see if the view is still
                                                                            -- working. bad underlying tables will cause this exec to fail.
       print @rc -- verify proc runs
       if @rc = 0   -- if it grabs a row then refresh view. if rc=0 then the exec ran ok and we can refresh view


the

execute @rc = sp_executesql @sql
will try to select 1 row from the view.
if it can then @rc will be 0 if it fails @rc will be not 0
we check for @rc = 0 before we try to refresh the view.
this keeps our script from aborting on a bad view.

Ok, now one last "newbie" question!  What does the N in N'select do?  I'm not familar with that.  Also, where is information_schema.views found?

Thanks so much!

the declare
declare @sql nvarchar(1000)           -- variable to hold dynamically created statement we want too run

specifies unicode {nvarchar}

so we have to prefix any character string with 'N' to identify it as a unicode string.
SQL server will do an implicit conversion of the string to unicode characters.
sp_executesql only accepts nvarchar variables so we have to use them.

look in the master database at the views

find the view 'VIEWS' you will notice the owner is `information_schema`

this is a owner created by default in sql server. it allows us to get information from the sysobject tables with out querying the tables directly. this is the preferred method for getting object information. When and if MS changes any of the sysobject table definitions these views would be updated to reflect those changes. this means you would not have to go back and re-write all of your sysobjects queries you may be using in your day to day operations.

Thank you so much!   You should write a book!  Your explanations are great!

no problem, and thanks!