Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Different select results from same database on different computers |
Thu, Aug 28 2014 4:09 AM | Permanent Link |
Nigel Informa | 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 |
Thu, Aug 28 2014 4:20 AM | Permanent Link |
Matthew Jones | 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 |
Thu, Aug 28 2014 4:27 AM | Permanent Link |
Nigel Informa | 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 |
Thu, Aug 28 2014 4:33 AM | Permanent Link |
Nigel Informa | "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 |
Thu, Aug 28 2014 4:45 AM | Permanent Link |
Nigel Informa | 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 |
Thu, Aug 28 2014 5:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 28 2014 5:44 AM | Permanent Link |
Matthew Jones | 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 |
Thu, Aug 28 2014 7:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 28 2014 7:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 28 2014 8:04 AM | Permanent Link |
Matthew Jones | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |