sp_refreshview Error Handling

24 Mar 2006 6:12 PM

JSzymanski

I am using the following code in a stored procedure to refresh a selected
group of views (for which the underlying tables have changed):

         SET XACT_ABORT OFF
         EXECUTE @spErrNo = sp_refreshview @fullViewName
         IF @spErrNo <> 0 or @ERROR<> 0
              PRINT 'ERROR: (' + LTRIM(STR(@spErrNo)) + ') '
         END

Part way through fetching from the cursor, I get the following error message
on one of the calls to sp_refreshview:

Server: Msg 207, Level 16, State 3, Procedure vEMPL_DED_SUM, Line 2
Invalid column name 'DED_DESC'.
Server: Msg 4413, Level 16, State 1, Line 2
Could not use view or function 'dbo.vEMPL_DED_SUM' because of binding
errors.

I understand why this error is generated for this particular view; however,
when the error is encountered, it terminates further execution of my
procedure. Can anyone tell me how to trap/handle this error so that I can
continue processing the rest of the views in the recordset?    Is there some
special way to prevent the calls to system sp's from raising fatal errors?

25 Mar 2006 12:51 AM

oj

There are errors that can't be trapped, unfortunately. I suggest you take a
look at Erland's article for some insight.
http://www.sommarskog.se/error-handling-I.html

--
-oj

Show quote

"JSzymanski" <JSzyman***@discussions.microsoft.com> wrote in message
news:FDB5270E-8A30-400D-97DC-FE49353BF745@microsoft.com...
>I am using the following code in a stored procedure to refresh a selected
> group of views (for which the underlying tables have changed):
>
>         SET XACT_ABORT OFF
>         EXECUTE @spErrNo = sp_refreshview @fullViewName
>         IF @spErrNo <> 0 or @ERROR<> 0
>              PRINT 'ERROR: (' + LTRIM(STR(@spErrNo)) + ') '
>         END
>
> Part way through fetching from the cursor, I get the following error
> message
> on one of the calls to sp_refreshview:
>
> Server: Msg 207, Level 16, State 3, Procedure vEMPL_DED_SUM, Line 2
> Invalid column name 'DED_DESC'.
> Server: Msg 4413, Level 16, State 1, Line 2
> Could not use view or function 'dbo.vEMPL_DED_SUM' because of binding
> errors.
>
> I understand why this error is generated for this particular view;
> however,
> when the error is encountered, it terminates further execution of my
> procedure. Can anyone tell me how to trap/handle this error so that I can
> continue processing the rest of the views in the recordset?    Is there
> some
> special way to prevent the calls to system sp's from raising fatal errors?