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.AdviserLastN ame, AH.AdviserFirst Name
FROM AgentHierarchy AH
WHERE AdviserId = 6069819
/*3 Statement below works*/
SELECT *, AH.AdviserLastN ame + ', '+ AH.AdviserFirst Name
FROM AgentHierarchy AH
WHERE AdviserId = 6069819
/*4 Statement below works*/
SELECT AH.AdviserLastN ame + ', '+ AH.AdviserFirst Name
FROM AgentHierarchy AH
/*5 Statement below fails*/
SELECT AH.AdviserLastN ame + ', '+ AH.AdviserFirst Name
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. 4 2346
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********@hot mail.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.
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********@hot mail.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.
Ryan (ry********@hot mail.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
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********@hot mail.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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 than one vendor:
select item_num,count(*)
|
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 message is all about. Any
ideas?
error: invalid initialization of non-const reference of
|
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 here.
Conversion seems OK and I start the app. BUT . . .
Mainform doesn't work. Form comes up but none of the buttons react.
Why not? I go to design view and see that code is not compiled. (compile-option is active)
So I compile and go to normal...
|
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 the results of that query.
This worked fine and I am able to scroll throught the results of the
query in my form.
|
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 have no
clue why.
But if I store the MethodInfo in a local variable I works as expected.
I do not understand why this is so, shouldn't both ways be semantically
equal?
| |
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) +313
System.Web.UI.Control.BuildProfileTree(String parentId, Boolean
calcViewState) +201
System.Web.UI.Control.BuildProfileTree(String parentId, Boolean
calcViewState) +263
|
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
sorted by Numb.
Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.
CREATE VIEW...
|
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()
{
|
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 justified), and page links
(right justfied) all on the same line. After changing to SSI, the
right side links are correct, but the left side copyright looks to be
wrapping to the next line. I can fix this in IE by moving it back up
in line with the...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |