question on views

Hello,
A little background...
I have a cube that automatically refreshes every 10 minutes and pulls it's
data from a view ('ViewA') in sql server 2000.
The cube takes around a minute and a half to completely refresh. During
this time, in sql server, one of my other views ('ViewC') starts to return
inconsistent results in the form of varying numbers of records in its result
set. The result set is somewhere in the neighborhood of 1,000 records, and
varies by anywhere from 40 to 50 records each time it is run during the cube
refresh.
A bit more info...
The cube pulls it's data from ViewA with these characteristics (simplified
for relevance):
ViewA selects * from ViewB (nolock) which, in turn, selects * from
MyTable (nolock)
As mentioned earlier, while the cube is refreshing, ViewC returns an
inconsistent number of records. ViewC has these characteristcs (simplified
for relevance):
ViewC selects * from MyTable (nolock)
It seems to me that the problem may have to do with record locking, but as
shown above, i am using (nolock) to prevent the issuing of any shared locks
while ViewA is running. So what is it about this cube refresh that prevents
one of my other views from gaining access to, and completely skipping over,
some of the records?!
One hundred million points for this one ;)
ThanksNolock doesn't guarantee consistent data so you can expect
inconsistent results...it's not unusual. You can retrieve
before and after images of rows being updated, can read
uncommitted transactions.
It doesn't look like it's record locking itself that is the
cause the issue but rather using a hint that doesn't care
about other exclusive locks, allows dirty reads. You really
have to weigh out using the nolock hint with the
inconsistent results that you can end up with.
-Sue
On Mon, 17 Oct 2005 15:18:30 -0400, "Jesse Aufiero"
<jaufiero@.moaboil.com> wrote:

>Hello,
>A little background...
>I have a cube that automatically refreshes every 10 minutes and pulls it's
>data from a view ('ViewA') in sql server 2000.
>The cube takes around a minute and a half to completely refresh. During
>this time, in sql server, one of my other views ('ViewC') starts to return
>inconsistent results in the form of varying numbers of records in its resul
t
>set. The result set is somewhere in the neighborhood of 1,000 records, and
>varies by anywhere from 40 to 50 records each time it is run during the cub
e
>refresh.
>A bit more info...
>The cube pulls it's data from ViewA with these characteristics (simplified
>for relevance):
> ViewA selects * from ViewB (nolock) which, in turn, selects * from
>MyTable (nolock)
>As mentioned earlier, while the cube is refreshing, ViewC returns an
>inconsistent number of records. ViewC has these characteristcs (simplified
>for relevance):
> ViewC selects * from MyTable (nolock)
>It seems to me that the problem may have to do with record locking, but as
>shown above, i am using (nolock) to prevent the issuing of any shared locks
>while ViewA is running. So what is it about this cube refresh that prevent
s
>one of my other views from gaining access to, and completely skipping over,
>some of the records?!
>One hundred million points for this one ;)
>Thanks
>