Hi,

I have a database with a table "Shipments" with 180,000 records, the datbase is on a windows Vista computer.
When I do a simple SELECT (below) on the table it returns 160 rows, which is what I am expecting.

I have backed up the database and on another computer (Windows 7) restored it. The "Shipments" table on the Windows 7 database has exactly the same amount of records. But when I run the same SELECT statement on that machine I only get 6 records returned.

I have tried repairing the table and removing and rebuilding all of the indexes. I have even tried it without indexes!

But I still get the smae results.

Am I going mad or am I missing something here?

Any help would be gratefully appreciated.


--------------------------------------------------------
SELECT
Load_Place_Name,
Vessel_Type,
DWT
FROM
Shipments
WHERE
Load_Dte > DATE '201-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'
--------------------------------------------------------
Nigel

Nigel wrote:

> Load_Dte > DATE '201-08-01'

Well that year looks a little wrong, so I suspect this wasn't copied
and pasted. However, the first thing I'd be looking at is locales, and
seeing if the data format is different on the two machines. 08-01 might
me 01-08 on the other.

Noting of course the Windows bug where the initial locale can be broken
on first change.

--

Matthew Jones

Sorry slight typo in the above SQL it should read:
--------------------------------------------------------
SELECT
Load_Place_Name,
Vessel_Type,
DWT
FROM
Shipments
WHERE
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'
--------------------------------------------------------
Nigel

"Matthew Jones" wrote:

Nigel wrote:

> Load_Dte > DATE '201-08-01'

Well that year looks a little wrong, so I suspect this wasn't copied
and pasted. However, the first thing I'd be looking at is locales, and
seeing if the data format is different on the two machines. 08-01 might
me 01-08 on the other.

Noting of course the Windows bug where the initial locale can be broken
on first change.

--

Thanks for the reply Mathew,

I have checked the locale settings on both computers and they are both the same for languge/shortdate/long date and time.

Matthew Jones
Nigel

Nigel wrote:

Hi,

I have a database with a table "Shipments" with 180,000 records, the datbase is on a windows Vista computer.
When I do a simple SELECT (below) on the table it returns 160 rows, which is what I am expecting.

I have backed up the database and on another computer (Windows 7) restored it. The "Shipments" table on the Windows 7 database has exactly the same amount of records. But when I run the same SELECT statement on that machine I only get 6 records returned.

I have tried repairing the table and removing and rebuilding all of the indexes. I have even tried it without indexes!

But I still get the smae results.

Am I going mad or am I missing something here?

Any help would be gratefully appreciated.


--------------------------------------------------------
SELECT
Load_Place_Name,
Vessel_Type,
DWT
FROM
Shipments
WHERE
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'
--------------------------------------------------------
Nigel


OK, I have managed to get the same results on both computers now.

I exported the "Shipments" table and then imported it to the other computer, as opposed to using the database backup/restore and it appears to be working now.

I have another issue but I will start a new thread for that one.

Thanks.
Nigel

Nigel

The problem reads like a corrupted index file.


If you have the time can you try the backup/restore again. If it still produces bad results can you try deleting the index file from the table and then run REPAIR. This will force the index to be rebuilt. I'm pretty sure that this was addressed but my repair code still dumps the index file.

If this fixes things it still leaves the question of what happened to corrupt the index file initially.

Roy Lambert

Nigel wrote:

> I have checked the locale settings on both computers and they are
> both the same for languge/shortdate/long date and time.

Change them to something different, then reboot, change them back,
reboot. Try again. Windows has a stupid bug where it can show one
thing, but use another. Or ignore me of course, if you are sure it
isn't that!

--

Matthew Jones

Matthew


I don't see how location can be the problem. In ElevateDB ALL dates are in the standard format YYYY-MM-DD so the location makes no difference once you have the date in the query. It could, I suppose, make a difference if you're supplying it as a parameter.

Roy Lambert

Nigel


I just re-read your post and saw that you've tried without indices so forget my comments.

Is the backup small enough, and not commercially sensitive so that you could post it to the binaries for others to have a look at?

Roy Lambert

Roy Lambert wrote:

> Matthew
>
>
> I don't see how location can be the problem. In ElevateDB ALL dates
> are in the standard format YYYY-MM-DD so the location makes no
> difference once you have the date in the query. It could, I suppose,
> make a difference if you're supplying it as a parameter.

Good point. It was just a possibility depending on where the data came
from.

--

Matthew Jones