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.
