Strange view results

[Ryan]

Bit of an obscure one here, so please bear with me. I have two copies
of a database which should be identical. Both have a complex view
which is identical. I can open the views and the data is as expected
and match. I can query it in several ways as detailed below. The 5th
version of the simple query below based on the second copy of the view
fails, but works under the first copy.

/*1 Statement below works*/
SELECT *
FROM AgentHierarchy
WHERE AdviserId = 6069819

/*2 Statement below works*/
SELECT *, AH.AdviserLastName, AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819

/*3 Statement below works*/
SELECT *, AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819

/*4 Statement below works*/
SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH

/*5 Statement below fails*/
SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstName
FROM AgentHierarchy AH
WHERE AdviserId = 6069819

The error I get is to do with conversion of data within the view. It's
a little complex, but the view works fine. It looks to me like when I
run the 5th statement above, it re-runs the view and then finds an
error.

So, I took the complex view and ran that with the data output into a
temporary table with the queries above run against that, and it works
fine. The problem is that the statement I need is based around the 5th
one above (part of an update statement).

I'm struggling to understand why some of the queries above work and
one doesn't. If you look at 3 and 5 I'd expect them both to fail. If
it failed consistently I could get further into it.

The problem is that it's a little difficult to get the view itself
changed as it was supplied by a third party, but if it hasn't changed
and the data hasn't changed then it's got to be something else causing
the problem.

Anyway, as I said, it's a bit obscure, but if this sounds familiar I'd
be interested in your opinion.

Thanks in advance.

[Ryan]

It does do a conversion and that is mainly the problem. However, the
data it has selected to work on should exclude any Char's and only
work on the int values so it shouldn't be a problem. At least, that's
the theory.

However, if I run the view as a query into a temporary table, and
query that then it works fine. If I re-create the view, that fails. I
can't see any offending data in the view so am assuming it happens
during the build of the view data.

I have tried to create a view which excludes the codes that are
causing the problem and then point my conversion view to that. Again,
it causes the same problem. I'm guessing that when a view is called,
it re-runs the query within the view instead of treating it as a
virtual table.

Not that it makes a huge difference, but this did work for a few
months and has only stopped recently. Just not been able to pin down
what caused it to stop.

Unfortunately the users decided in their infinite wisdom :-) to change
a style of agreed coding which is what is causing the problem. They
did this some months ago and thought they didn't need to tell us. No
suprises there. I may try to change the codes throughout the database
but would prefer not to due to the size and work involved. Not
impossible though. Would rather change the users :-)

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Ryan (ry********@hotmail.com) writes:

Bit of an obscure one here, so please bear with me. I have two copies
of a database which should be identical. Both have a complex view
which is identical. I can open the views and the data is as expected
and match. I can query it in several ways as detailed below. The 5th
version of the simple query below based on the second copy of the view
fails, but works under the first copy.
...
The error I get is to do with conversion of data within the view. It's
a little complex, but the view works fine. It looks to me like when I
run the 5th statement above, it re-runs the view and then finds an
error.

Without the view definition and the actual error message, I have to
resort to guessing and speculation.

One possibility is that the view defintion includes a condition like:

charcol = intcol

This could work one server and fail on other if the data in charcol
on one server does not have any values that cannot be converted.

Another possibilty could be that difference in statistics in the
underlying table causes the different query plans, but this is a
little more far-fetched, because if the data is the same you should
run into problems on both servers.

[Ryan]

Think I found the answer !

If anyone is interested, here is what I found.

The query which failed can still have an estimated execution plan run
against it, so I was able to run a comparison between the working and
none working versions. These differed somewhat.

After close examination we were able to find that a new index had been
created on the table which is used. The version that worked simply
converted the data in the final set and was quite a simple execution
plan. The none working version seperated the data and tried to convert
it before formatting the results, which makes the plan more complex
(although probably more efficient).

Only a simple difference, but it makes all the difference. As it tries
to convert the data before it excludes those that it doesn't want, it
causes a problem. I suppose better view writing would allow for this
in all honesty.

Erland, thanks for the help on that, it got me thinking and helped me
to find the solution.

Now all I have to do is explain it in numpty language to the users so
that they stop entering a load of rubbish data.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Ryan (ry********@hotmail.com) writes:

However, if I run the view as a query into a temporary table, and
query that then it works fine. If I re-create the view, that fails. I
can't see any offending data in the view so am assuming it happens
during the build of the view data.

I have tried to create a view which excludes the codes that are
causing the problem and then point my conversion view to that. Again,
it causes the same problem. I'm guessing that when a view is called,
it re-runs the query within the view instead of treating it as a
virtual table.

There are two kinds of views: indexed and regular ones. Indexed views
are materialized, so thus once the view is in place there is no
possibility that non-qualifying rows in the underlying table can
interfere. Note that indexed views requires that a couple of options
have the correct settings; else the view degrades to a regular view.
This may happens on Standard Edition, which does not have full support
for indexed views.

Regular views are basically only macros. If you say SELECT * FROM vu
or use the underlying SELECT expression, has no importance. At least
not in theory.

Whether an offending value may cause a problem, depends on the query
plan. If you are lucky, the query plan bypasses the offending value,
but there is not really any guarantee. It seems that you cannot
change the views. Thus, you can any hints to them. You should possibly
use SET FORCEPLAN ON, which encourages the optimizer to access the
tables in the order they are listed in the query. But this can
result in more conversion errors and slow query plans, so this is
more a last straw.

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

After close examination we were able to find that a new index had been
created on the table which is used. The version that worked simply
converted the data in the final set and was quite a simple execution
plan. The none working version seperated the data and tried to convert
it before formatting the results, which makes the plan more complex
(although probably more efficient).

Only a simple difference, but it makes all the difference. As it tries
to convert the data before it excludes those that it doesn't want, it
causes a problem. I suppose better view writing would allow for this
in all honesty.
Thanks for taking the time to report back! And I am glad to hear
that you were able to find an answer!
Now all I have to do is explain it in numpty language to the users so
that they stop entering a load of rubbish data.

That is the really tricky part! May I humbly suggest some validation in
the GUI?
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ryan]

Yep, passed it onto our suppliers, it's their view and they tend to
get a bit upset if I change things, but with a bit of luck they'll add
something in to it. We'll be changing the codes to a suitable format
shortly, but at least I now have a better understanding of how SQL
executes queries/views.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

Ryan (ry********@hotmail.com) writes:

After close examination we were able to find that a new index had been
created on the table which is used. The version that worked simply
converted the data in the final set and was quite a simple execution
plan. The none working version seperated the data and tried to convert
it before formatting the results, which makes the plan more complex
(although probably more efficient).

Only a simple difference, but it makes all the difference. As it tries
to convert the data before it excludes those that it doesn't want, it
causes a problem. I suppose better view writing would allow for this
in all honesty.

Thanks for taking the time to report back! And I am glad to hear
that you were able to find an answer!

Now all I have to do is explain it in numpty language to the users so
that they stop entering a load of rubbish data.

That is the really tricky part! May I humbly suggest some validation in
the GUI?