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

Thanks

I think it's unlikely that AS is the root of the problem since it's just
sending queries to SQL Server. Have you tried running the query from the
cube build (and dims?) outside of AS to see whether that causes the same
problem? If you can reproduce this outside of AS, I would suggest posting
the problem to the SQL Server General group.

Dean.

"Jesse Aufiero" wrote:

Your problem may indeed have to do with record locking, but because of
the NoLock (= Read Uncommitted) option:

http://groups.google.com/group/micro...programming/ms
g/31a225ef7b001cba
There are exceedingly few occasions when a production level application
should be running with the READ UNCOMMITTED transaction isolation level.
Deadlocks is **not** a reason to change to the READ UNCOMMITTED
transaction
isolation level. The book "Inside Microsoft SQL Server" ... there are
editions for SQL Server 6.5, 7.0 and 2000 ... explains deadlocks: what
they
are and how to code to prevent deadlocking. I strongly suggest that you
get
hold of the appropriate edition for a better approach to resolving
deadlocking issues than changing to the READ UNCOMMITTED transaction
isolation level.

When you specify the READ UNCOMMITTED transaction isolation level, you
are
telling SQL Server to try to read data regardless of the locks that
might
have been placed on the data. This exposes your application to the risk
of
SQL Server trying to read data when the data is still in an inconsistent
state ... not just logically inconsistent, but physically inconsistent.
And
in fact that is exactly what this particular error message is telling
you.

If you insist upon using the READ UNCOMMITTED transaction isolation
level,
then you have to add code to your application to expect and, basically,
ignore these errors. You need to resubmit your query ... unchanged ...
and
the probability is that the physical inconsistency that was present in
the
data will have been resolved by the time that you attempt to read the
same
data again. But I do emphasize again, that I strongly recommend
**against**
running production level code with the READ UNCOMMITTED transaction
isolation level except in a very limited number of scenarios.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***