Solved

Catching an Error and Preserving Query Flow

Posted on 2004-08-11
20
685 Views
Last Modified: 2008-02-01
Hi,

I have a job that runs sp_refreshview on all views in a database.  Once, we dropped a table from the database but forgot to remove the view that was pointing to it.  Because of this, the sp_refreshview stored procedure raised an error and everything stopped.

Is there a way to catch this error such that the rest of the query may continue (to refresh the rest of the views)?  Thanks.
0
Comment
Question by:Moozh
  • 8
  • 4
  • 4
  • +2
20 Comments
 

Author Comment

by:Moozh
ID: 11775865
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11777536
Unfortunately maybe not.  Certain errors in SQL Server are not "trappable", so the batch will abend no matter what you do.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11779336
Small comfort: this will be possible in SQL Server 2005.
0
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 125 total points
ID: 11780484
use sp_executesql and look for error:

declare @table_name varchar(8000)
declare @rc int
declare @sql nvarchar(1000)
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
 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
        print @rc -- verify proc runs
        if @rc = 0   -- if it grabs a row then 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
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11780558
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).
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11780613
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....
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11780715
Please do -- it only takes once to see the point  :-)
0
 

Author Comment

by:Moozh
ID: 11789011
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
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11789311
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.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11790065
did you actually try my proc? it worked on my server just fine...
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:SoftEng007
ID: 11790068
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.
0
 

Author Comment

by:Moozh
ID: 11797572
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!
0
 

Expert Comment

by:mjkriegl
ID: 12594665
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!
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12599653
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
0
 

Expert Comment

by:mjkriegl
ID: 12605332
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!
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12605746
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.
0
 

Expert Comment

by:mjkriegl
ID: 12606412
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!
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12608226
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.
0
 

Expert Comment

by:mjkriegl
ID: 12609643
Thank you so much!   You should write a book!  Your explanations are great!
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12611223
no problem, and thanks!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now