473,390 Members | 1,104 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,390 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 2328
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: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

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.