Ok, guys,
the problem was really really ugly.
But, first, thank you for your prompt replies and support.
As you can imagine, my first try was to see if the problem was with ODS so I just ran the update for OL_CHUNK_LOG table as many of your pointed out. Unfortunately, this did not work.
My next step, meant increasing the logging level of ETL runtime, so I could actually see what is happening. Well, guess what - levelOfLog option showed that UPDATE command was pretty much ok, except for missing one field (the one that had filter added to it).
Strange, I thought... Since there is no way to really see all SQL commands that etl is spewing out (do not believe the manual that says you can set it to _ALL_:9 and get it all), I use SQL Enterprise Trace to see what exactly our beloved ETL was doing. And Lo' and Behold! It was ALTERING my VIEWS!!!!
Yes, it was ALTERING views and ... get this.... tried to add a new field to it. The problem was that the new field already existed in the view, and thus, SQL server would return the error and ETL would not transfer the data.
This made me think... Why was ETL adding a new field if all I did was add a filter to it? So, I ran some experiments (yes, my girlfriend is far away and thus I have all this time on my hands) to see what is happening.
Here is what I found:
whenever you add a filter to a layout, ETL treats it as a new field and goes out of its way to propagate it to all the views. But, should something go wrong during the propagation, it DOES NOT ROLLBACK all the previous changes, so you end up with views that have the new field and some that don't!
Looking back through ETL and DMA and DataSourcer release notes, I ran across the latest release 7.2.100.x that said that now ETL tries to use existing fields instead of making the new ones...
Genesys Support also suggested that we just upgrade and hope that the problem will go away. Of course, this still leaves one question out there: how can something like this (adding filters to fields) cause so much damage so late into release. We are talking over 4 years here since ETL was introduced and still have this bug that late into the version for something this widely used... I don't get it.
I will keep you update of the progress, but I hope that it will work.
Here is what I learned:
1. DO NOT TURN OFF DATASOURCER when modifying layouts
2. stop ETL runtime when modifying layouts
3. have a backup of everything before you do something
(well, I knew (3), but still #1 and #2... especially #2 is a bit strange...)
Vic
P.S. BTW, I have smited Cavagnaro for being jealous that Koldun got +1.
