Data magically appears
-
Hi,
I have a table which has some data which was imported a long time ago from a .CSV file using a third party Borland Delphi tool.
I got a call from a colleague saying that they had run a report and couldn't see this data. So I ran the same report, and verified the data was not there.
Then I lifted the TSQL Query out of the report and ran it in query analyser, and got the data I was expecting.
I then re-ran the report in my application, and this time I got the data in the report as well.
I was thinking maybe indexing problems or out of stats might have this effect, but the data has been there for several years, and we have changed servers (and SQLServer versions) since then. Autoupdate stats is on.
I need an explanation for this urgently, any ideas?
David
If it ain't broke, don't fix it...
-
To my though, there is no magic that makes it works. I think you or your coleage accidently re-import the data without knowing it or similiar effect.
mom
-
When you say "Data" are you talking large chunks of data? or just a row or 2?
I've never seen such a thing. Is there any application side caching, that would have left the data out.
Did U use the same parameters both times?
Sorry, This may remain an Unsolved Mystery
-
Hi Ray,
Several hundred rows. All imported in the same run and sharing a date in common. When I ran a report which should have included this data, it didn't show.
Can data be omitted from an index and results set? Would an execution plan cause data to be omitted from a results set?
David
If it ain't broke, don't fix it...
-
Hi,
I do have cases when users call and tell us they don't see the data they think should be there. It is mostly a report parameter issue or report code issue. Examples from last 5 day: Date selection box on the report is at the bottom of the screen so you need to use a scrollbar and default date is 2003. OR one parameter make sense only if another parameter is also selected OR a superuser changed one role OR (I, myself!!!) was connected to the test database that was a restore from a couple of month ago.
And I even did not start to talk about execution security context.
Yelena
Regards,Yelena Varsha
-
Maybe an unfinished transaction too...
-
Hi
re: unfinished transaction. The data is extracted to a temporary table with a query using the (nolock) locking hint. Usually the application whould freeze if it encountered a block, hence the temporary table/ nolock strategy.
David
If it ain't broke, don't fix it...
-
Glad to hear it can't be that... but that doesn't help much in telling what did happen
. -
I don't believe it was a user error, because it was reported to me and I was able to verify it.
I ran the report, the data wasn't there.
I extracted the query from the report and ran it in query analyser with the same parameters, the data appears!
I re-ran the report, the data is now there, and persists in being there
Unfortunately I have to explain this to the auditors!
David
If it ain't broke, don't fix it...
-
I have been thinking that the indexes may be the chief suspects, and wondered if they can get into a state where some rows are invisible to them.
Another suspect is the main view which drives the query. It is a multi-part union statement, and I am wondering if for some reason it needed refreshing.
But why would the simple act of running a query in query analyser instead of through the Delphi application cause the data to appear?
David
If it ain't broke, don't fix it...
-
auditors - so what you need is a plausible explanation - one that shouldnt re-occur!
tell then there was a comms problem - the IP transact configuration matrix was being updated which shouldnt have generated any network errors but on closed analysis it was dicovered there is a 'feature' in the process that advises suitable downtime - and that next time you will aware of the situation!
-
Hi Rich,
A nice thought! Blind them with jargon
Actually I do have a handy server problem which was occurring at about the same time on our separate application server.
"The server was unable to allocate from the system nonpaged pool because the pool was empty".
Darn, must have forgotten to fill it!
Symptom is the server stops responding to any requests. This apparently occurs when a TCPIP sockets application runs away with the memory, my chief suspect here is Veritas Backup Exec, which was using 1.4Mb of the NP pool instead of the 8K it usually uses.
Unfortunately, this would explain database blocking from abandoned sessions, or multiple application faults, but does not explain an empty report.
Besides, this data has apparently never appeared until the moment I chose to run the query in Query analyser.
David
If it ain't broke, don't fix it...
-
Found this link on the SQL Server Mag website (I am allowed to post this please Moderator?)
http://www.windowsitpro.com/SQLServer/Article/ArticleID/24600/24600.html
It refers to the fact that complex views, especially those involving several levels of hierarchy (view A calls view B calls view C) may return incomplete record sets due to changes in the underlying table structure.
It doesn't really provide an explanation how certain records within a particular date range would not be returned though.
David
If it ain't broke, don't fix it...
-
Another snippet of information,
the data was invisible until I ran the query in query analyser, when it 'magically' appeared. I have found that some of the settings which are ON in my query analyser environment, such as ANSI_WARNINGS and ARITHABORT can cause a query recompilation.
David
If it ain't broke, don't fix it...
-
I don't know this report generator you are using but a similar situation in Crystal Reports happens when you save the data with the report i.e caching the data. You have to refresh the dataset inside Crystal Reports. Maybe you have the same situation going on here.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply