Wednesday, March 21, 2012

Errors using sp_refreshview

Hi ... I hope someone can explain this. Some time ago we created a stored
procedure that loops through all SPs and Views and refreshes them. Yesterday
we noticed a problem where two specific views would not refresh (don't seem
to have the problem with SPs and sp_recompile). After manually dropping and
recreating the view and then rerunning the proc then worked without error.
Of course it wasn't necessary but we wanted to retest the main SP running th
e
refresh process.
Of course, nothing else (that we can see) changed. In addition, we have
several deployments of the same database structure, and this did NOT happen
in all databases ... in fact, in most deployments worked without error. On
our dev server there are several databases, all (supposedly) the same
structure and since on the same DB server it is of course the same version o
f
SQLServer. Yet we had the failure on two separate tables in two separate
databases (unknown if it was the same tables in the two databases).
We're still evaluating of course, but in the meanwhile ... any ideas'
Thanks in advance ...
--
Brad AshforthHi,
Did u checked the log why it happend like this as u explained .
Have u scheduled a job to execute ur SP.
From
Doller
Brad Ashforth wrote:
> Hi ... I hope someone can explain this. Some time ago we created a stored
> procedure that loops through all SPs and Views and refreshes them. Yesterd
ay
> we noticed a problem where two specific views would not refresh (don't see
m
> to have the problem with SPs and sp_recompile). After manually dropping an
d
> recreating the view and then rerunning the proc then worked without error.
> Of course it wasn't necessary but we wanted to retest the main SP running
the
> refresh process.
> Of course, nothing else (that we can see) changed. In addition, we have
> several deployments of the same database structure, and this did NOT happe
n
> in all databases ... in fact, in most deployments worked without error. On
> our dev server there are several databases, all (supposedly) the same
> structure and since on the same DB server it is of course the same version
of
> SQLServer. Yet we had the failure on two separate tables in two separate
> databases (unknown if it was the same tables in the two databases).
> We're still evaluating of course, but in the meanwhile ... any ideas'
> Thanks in advance ...
> --
> Brad Ashforth|||Hi Brad,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood it happened to failed refreshing for
two of your views via sp_refreshview. If I have misunderstood your
concern, please feel free to point it out.
It seems strange and it seems you have fixed this by dropping and
recreating the Views. Could you find any more related information in Error
Log?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael ... by Error Log I'm assuming you mean the SQL Server Error Log,
under the Management folder in Enterprise Manager. There is no entry in the
error log for this (at least no entry that mentions the databases in
question). Is it possible that the server is not configured to report errors
at this level? If so, how can I configure it to report these types of errors
should they occur again? We are concerned because we have seen this error no
t
only on our Dev server but on two different client production servers.
Thank you for your assistance ...
--
Brad Ashforth
"Michael Cheng [MSFT]" wrote:

> Hi Brad,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood it happened to failed refreshing for
> two of your views via sp_refreshview. If I have misunderstood your
> concern, please feel free to point it out.
> It seems strange and it seems you have fixed this by dropping and
> recreating the Views. Could you find any more related information in Error
> Log?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Brad,
This seems to be related to a known issue of us when the views are updated
in not dependent order. Please use sp_refreshview to update views in
dependent order.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael ... Thank you for your input. The next time it occurs, we will tr
y
to determine if the view is dependent on other views and if so will try to
refresh again (assuming the view that it was dependent on was refreshed late
r
in the 1st attempt).
--
Brad Ashforth
"Michael Cheng [MSFT]" wrote:

> Hi Brad,
> This seems to be related to a known issue of us when the views are updated
> in not dependent order. Please use sp_refreshview to update views in
> dependent order.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Brad,
You are welcome to reply here whenever it occurs again. Thank you for your
patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

No comments:

Post a Comment