Sql data intact, but can't display in application

Question

Sql data intact, but can't display in application

paul 4478

SSC Rookie

Points: 40
More actions
July 16, 2011 at 3:14 am

#239882

Hi All
I'm a bit new to SQL but I've been given a problem to solve
We are using SQL 2008 r2 on a Server 2008 Enterprise
We have our own application to view data collected and stored in a sql database
The problem I have...
Data is collected every 10 minutes and stored in a table along with the time&date
The table appears intact, i.e no rubbish data stored
but I am missing 3 x 10 minute data i.e 30 mins of data, which in our app causes an alarm
We have thousands of these installed and have never seen this problem before
the only odd thing is it's installed in Slovinia (only one in that country)!!
I'm not sure if this a sql problem as the data appears to be correct for the period
but our app has never exhibited this before
Any Thoughts

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Does the table have a IDENTITY column? If so, are the values stored in that column for the period in question consecutive? If so, the data (most probably) were never inserted, meaning there was no INSERT statement executed with those values. If there's no identity column, the database is in full recovery mode, a backup exists before the time in question and there is an unbroken transaction log chain you could run a point in time recovery to see if the data have been inserted but were deleted afterwards.

Another option would be to analyze the transaction log file. AFAIK there are tools available to do that but I've never tried one...

Maybe one of the gurus will stop by with another idea.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul 4478 SSC Rookie Points: 40 More actions · Answer 2

Hi thanks for the reply

There is no identity column

just two columns

Date & Time (in 10 minute intervals)

Value (it's actually temperature data)

The table has data in the columns for the period in question, but I can't access it

using our app

It's all really strange as the data looks intact

LutzM SSC Guru Points: 107049 More actions · Answer 3

So, if you select the data using Management Studio, the data are there but when you try to query it via the app, the data cannot be displayed? Weird, indeed.

You might want to run a profiler trace and capture the statement the app sends to SQL Server. Copy that statement and run it in Management studio.

If the data are returned properly, it's an issue of the app logic.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul 4478 SSC Rookie Points: 40 More actions · Answer 4

Thanks for that

I am a real newbie to sql .... whats a profiler trace?

and how do I run one?

Cheers

LutzM SSC Guru Points: 107049 More actions · Answer 5

Here's a link that might help you to get started using SQL Profiler:

http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul 4478 SSC Rookie Points: 40 More actions · Answer 6

thanks again, running tutorials now

profiler looks useful

I have set the trace running for 7 days... might run out of disk space though

I will ask for more space on Monday

If the error occures then I can see which query is causing it! great tool

If no errors occure then I will run it again until I find the culprit

Many Thanks

Paul

LutzM SSC Guru Points: 107049 More actions · Answer 7

If you want to run it permanently on your prod server I recommend to go to the next level: server side trace.

But what I meant originally was to set up Profiler limited to queries against the table in question and then run the function in your app showing the incomplete data. Then stop Profiler and look at the query as it is sent by the app. Re-run in in Management studio and see if you get all results.

I would not recommend to let the profiler run on a prod system without a deeper understanding what it does performance wise...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul 4478 SSC Rookie Points: 40 More actions · Answer 8

I'm on a sat link to the server

access is only available for 5 hours per day, well thats what i'm told

The site admin is only contactable via email and for some reason he sleeps!!! hehe

The remote site is self contained, so there shouldn't be any problems

I am running the same system at home, with the same qty off sensors

I could run the trace at home, but I'm sure there would be no issues

Maybe it's a time diff issue, gmt vs Slovinia? don't know

our s/w is supposed to use UTC

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

LutzM (7/16/2011)
Another option would be to analyze the transaction log file. AFAIK there are tools available to do that but I've never tried one...

I used Lumigent Log Explorer briefly but not since 2003.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

bsreddy00 SSC Rookie Points: 30 More actions · Answer 10

http://sharepointsolution2010.blogspot.com/2011/07/2000-2005-2008-sql-server-version.html

LutzM SSC Guru Points: 107049 More actions · Answer 11

bsreddy00 (7/17/2011)

http://xxxx/2011/07/2000-2005-2008-sql-server-version.html%5B/quote%5D

And what is your question / comment?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul 4478 SSC Rookie Points: 40 More actions · Answer 12

Hi again

Thanks for the advice, tomorrow i'm going to replicate

the config on my own hardware and do some tests

The profiling is very helpful, I think I may be able

to replicate the problem and throw it back towards

our dev team.

Good fun this SQL stuff

Cheers