?
Solved

Catching an Error and Preserving Query Flow

Posted on 2004-08-11
20
Medium Priority
?
695 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

Expert Comment

by:Scott Pletcher
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Accepted Solution

by:
SoftEng007 earned 500 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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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