Views referencing views (problem)
-
Hi,
I've got a problem and I need some help.
I have a view (view 1), which references a view (view 2), which in turn references another view (view 3). all works fine, but in a very rare occurance when my dts uses the top level view to generate a dataset instead of working correctly, it returns millions of rows where it should only return say 1000. The only way to resolve the issue is to re-create the views from the bottom of the chain upwards (view 3, then 2, then 1). This then returns the correct dataset.
Has anyone seen this issue before? and if so what caused it and how was it resolved?
Thanks
-
No I haven't seen that... could you post the view definitions? I don't think we can look for a solution if we have no idea what the views do.
-
Views of views of views to feed DTS... I once raised my arm over my head in a very funny way and it hurt like hell. I went to the doctor and said "When I raise my arm like this, it hurts like hell." The doctor said, "Stop doing that and it won't hurt."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was searching for a nice way to put it... and it was just that 
. -
Amen!
-
Still... why should the results fluctuate on him?
(Assuming all conditions are the same and they're not dependent on something variable like getdate)
If he's simply doing a Select he shouldn't have to recreate the views.
Could it be that, between one run (where it works) and the next, you dropped and recreated one of the dependent tables/views?
-
Cache. It's not supposed to happen but I've seen it before. Could be something else but I'd have to see all of the code to figure it out.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey Nic, how about posting the DDLs of them views?
-
Views are query rewrites only persisted so the person needs to post the DML(data manipulation language). Hope this helps
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply