473,320 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Strange view results

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.
Jul 20 '05 #1
4 2320
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.

Jul 20 '05 #2
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.

Jul 20 '05 #3
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
Jul 20 '05 #4
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?

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Michael Krzepkowski | last post by:
All, I have a view that returns the following values: Item Vendor 70807 1234 70807 5678 If I am looking for items that have more...
13
by: Neil Zanella | last post by:
Hello, I wonder whether anyone has ever come across the following g++ compiler error message. I don't recall ever seeing it before. I solved my problem but I am still not sure about what this...
10
by: Arno R | last post by:
Hi all Yesterday I found a strange corruption-issue that I can't solve yet or actually point my finger at. I converted an A97 app to A2k. I have done this often enough so I didn't expect trouble...
9
by: David White | last post by:
Hello everyone, I'm new to access, so if this is a dumb question I apologize in advance. I created a query that requires a "parameter value" to be entered. I then created a form to display...
5
by: cody | last post by:
I have a very funny/strange effect here. if I let the delegate do "return prop.GetGetMethod().Invoke(info.AudioHeader, null);" then I get wrong results, that is, a wrong method is called and I...
5
by: Nathan Sokalski | last post by:
When I view my index.aspx page any time after the first time, I recieve the following error: System.Web.TraceContext.AddNewControl(String id, String parentId, String type, Int32 viewStateSize)...
104
by: Beowulf | last post by:
I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through...
0
by: Wescotte | last post by:
<?php global $TABLE_GL_DATA; global $connect; $PREPARED_SQL = odbc_prepare($connect, "INSERT INTO $TABLE_GL_DATA VALUES (?,?,?,?,?,?,?)"); function Generate_GL_Data() {
10
by: silverbob | last post by:
I am replacing my Javascript-called files with SSI, and I'm getting an unexpected result with my page footer. I can really use some help with this. The footer contains a copyright (left...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.