|
|
You're in the
sql server (alternate)
group:Missing Record - Phantom Record
sql server (alternate):
Have come across something weird and am after some help. Say i run this query where rec_id is a column of table arlhrl, select * from arlhrl where rec_id >= 14260 This returns to me 2 records with rec_id's of 14260 and 14261 Then I run this query select * from arlhrl where rec_id >= 14263 This returns 7 records with rec_ids of 14263 up. How come the first query doesn't return the records returned by the 2nd query also? If I select for 14262 no records are returned. It is like this is a phantom record or has an end of file character in it. I tried re-creating the indexes but to no avail. If anyone has any ideas about what could be causing it or how to fix it it would be much appreciated. Thanks in advance,
[quoted text, click to view]
Andrew wrote: > Hi All, > > Have come across something weird and am after some help. > > Say i run this query where rec_id is a column of table arlhrl, > > select * from arlhrl where rec_id >= 14260 > > This returns to me 2 records with rec_id's of 14260 and 14261 > > Then I run this query > > select * from arlhrl where rec_id >= 14263 > > This returns 7 records with rec_ids of 14263 up. > > How come the first query doesn't return the records returned by the > 2nd query also? > > If I select for 14262 no records are returned. It is like this is a > phantom record or has an end of file character in it. > > I tried re-creating the indexes but to no avail. If anyone has any > ideas about what could be causing it or how to fix it it would be much > appreciated. > > Thanks in advance, > > Andrew Hi, First, stupid question - is the field 'rec_id' of integer type? Why i am asking is because i had a similar example myself when i started with my new job - i was quering an id field and got weird results as you do. Then i found that some 'smart ass' made this comlumn a varchar for no reason - just because she was doing like that in Access all the time before :) Second, what i'd do when i get into an unexplainable glitch: SELECT * INTO <new table> FROM <your table> And try to query the records from the new table without setting any indexes - just as is - as you know SELECT INTO just copies raw data without any underlying stuff. See what you'll get. From my experience there are a of of people who are allowed to mess with SQL databases but don't have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never imagine what another person could do - believe me, i just got quite a few awsome examples within the last month since i got this job :) Let me know how it works!
[quoted text, click to view]
Andrey <leyandrew@yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@attbi_s51>... > Andrew wrote: > > Hi All, > > > > Have come across something weird and am after some help. > > > > Say i run this query where rec_id is a column of table arlhrl, > > > > select * from arlhrl where rec_id >= 14260 > > > > This returns to me 2 records with rec_id's of 14260 and 14261 > > > > Then I run this query > > > > select * from arlhrl where rec_id >= 14263 > > > > This returns 7 records with rec_ids of 14263 up. > > > > How come the first query doesn't return the records returned by the > > 2nd query also? > > > > If I select for 14262 no records are returned. It is like this is a > > phantom record or has an end of file character in it. > > > > I tried re-creating the indexes but to no avail. If anyone has any > > ideas about what could be causing it or how to fix it it would be much > > appreciated. > > > > Thanks in advance, > > > > Andrew > > > Hi, > > First, stupid question - is the field 'rec_id' of integer type? > Why i am asking is because i had a similar example myself when i started with my new job - i was > quering an id field and got weird results as you do. Then i found that some 'smart ass' made this > comlumn a varchar for no reason - just because she was doing like that in Access all the time before :) > > > Second, what i'd do when i get into an unexplainable glitch: > > SELECT * INTO <new table> FROM <your table> > > And try to query the records from the new table without setting any indexes - just as is - as you > know SELECT INTO just copies raw data without any underlying stuff. > See what you'll get. > From my experience there are a of of people who are allowed to mess with SQL databases but don't > have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just > hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never > imagine what another person could do - believe me, i just got quite a few awsome examples within the > last month since i got this job :) > > Let me know how it works! > > Andrey Hi Andrey, Thanks for your reply. I tried as you mentioned, inserting into new table etc but to no avail. I did figure out what the problem was though. This particular table had been upsized from a foxpro table. One of the columns in the foxpro table had a maximum value of numeric 9999. Somehow, someone had tried to insert a value large than this so foxpro put in ****. On the upsize, and I can only assume here, sql must have thought 'hang on, you must mean infinity here' and put a bit-wise pattern (1.#INF) for infinity into this particular column for the record. This only became evident when using Enterprise Manager and returning all rows on the given table, it did display the record with the value 1.#INF in the column for the 'missing' record. As to why it displayed in EM and not Query Analyser is anyone's guess, but surely the queries that led me to this initial discovery shouldn't have behaved like this!!?? posting http://groups.google.com/groups?q=%23inf&hl=en&lr=&ie=UTF-8&group=comp.databases.ms-sqlserver&selm=ff18380f.0204191128.3778bcc5%40posting.google.com&rnum=1 gives some ideas. Thanks anyway,
[quoted text, click to view]
Andrew wrote: > Andrey <leyandrew@yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@attbi_s51>... > >>Andrew wrote: >> >>>Hi All, >>> >>>Have come across something weird and am after some help. >>> >>>Say i run this query where rec_id is a column of table arlhrl, >>> >>>select * from arlhrl where rec_id >= 14260 >>> >>>This returns to me 2 records with rec_id's of 14260 and 14261 >>> >>>Then I run this query >>> >>>select * from arlhrl where rec_id >= 14263 >>> >>>This returns 7 records with rec_ids of 14263 up. >>> >>>How come the first query doesn't return the records returned by the >>>2nd query also? >>> >>>If I select for 14262 no records are returned. It is like this is a >>>phantom record or has an end of file character in it. >>> >>>I tried re-creating the indexes but to no avail. If anyone has any >>>ideas about what could be causing it or how to fix it it would be much >>>appreciated. >>> >>>Thanks in advance, >>> >>>Andrew >> >> >>Hi, >> >>First, stupid question - is the field 'rec_id' of integer type? >>Why i am asking is because i had a similar example myself when i started with my new job - i was >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :) >> >> >>Second, what i'd do when i get into an unexplainable glitch: >> >>SELECT * INTO <new table> FROM <your table> >> >>And try to query the records from the new table without setting any indexes - just as is - as you >>know SELECT INTO just copies raw data without any underlying stuff. >>See what you'll get. >> From my experience there are a of of people who are allowed to mess with SQL databases but don't >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never >>imagine what another person could do - believe me, i just got quite a few awsome examples within the >>last month since i got this job :) >> >>Let me know how it works! >> >>Andrey > > > Hi Andrey, > > Thanks for your reply. I tried as you mentioned, inserting into new > table etc but to no avail. I did figure out what the problem was > though. > > This particular table had been upsized from a foxpro table. One of the > columns in the foxpro table had a maximum value of numeric 9999. > Somehow, someone had tried to insert a value large than this so foxpro > put in ****. On the upsize, and I can only assume here, sql must have > thought 'hang on, you must mean infinity here' and put a bit-wise > pattern (1.#INF) for infinity into this particular column for the > record. > > This only became evident when using Enterprise Manager and returning > all rows on the given table, it did display the record with the value > 1.#INF in the column for the 'missing' record. As to why it displayed > in EM and not Query Analyser is anyone's guess, but surely the queries > that led me to this initial discovery shouldn't have behaved like > this!!?? > > posting > > http://groups.google.com/groups?q=%23inf&hl=en&lr=&ie=UTF-8&group=comp.databases.ms-sqlserver&selm=ff18380f.0204191128.3778bcc5%40posting.google.com&rnum=1 > > gives some ideas. > > Thanks anyway, > > Andrew Well, EM and QA might show you diferent results because they are using diferent methods of 'talking' to sql server. QA is using isql.com, precisely it's isqlw.com version, which is an old DB lib based way of connection. EM, i guess, is using ODBC or OLEDB connection. I also had a headache not long time ago, when i used sql console tools to make Python work with sql server. I had a table with varcha fields which had around couple thousand characters of text each. When i used isql.com to retreive those text records, text returned truncated, around 300 to 600 characters left.. SO i started using osql.com instead, and no headache. So resume is - every time you're in doubt, use both EM and QA PS. BTW, I didn't know sql server can store 'infinity' values. Thanks for the info! WYGL,
[quoted text, click to view]
Andrew wrote: > Andrey <leyandrew@yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@attbi_s51>... > >>Andrew wrote: >> >>>Hi All, >>> >>>Have come across something weird and am after some help. >>> >>>Say i run this query where rec_id is a column of table arlhrl, >>> >>>select * from arlhrl where rec_id >= 14260 >>> >>>This returns to me 2 records with rec_id's of 14260 and 14261 >>> >>>Then I run this query >>> >>>select * from arlhrl where rec_id >= 14263 >>> >>>This returns 7 records with rec_ids of 14263 up. >>> >>>How come the first query doesn't return the records returned by the >>>2nd query also? >>> >>>If I select for 14262 no records are returned. It is like this is a >>>phantom record or has an end of file character in it. >>> >>>I tried re-creating the indexes but to no avail. If anyone has any >>>ideas about what could be causing it or how to fix it it would be much >>>appreciated. >>> >>>Thanks in advance, >>> >>>Andrew >> >> >>Hi, >> >>First, stupid question - is the field 'rec_id' of integer type? >>Why i am asking is because i had a similar example myself when i started with my new job - i was >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :) >> >> >>Second, what i'd do when i get into an unexplainable glitch: >> >>SELECT * INTO <new table> FROM <your table> >> >>And try to query the records from the new table without setting any indexes - just as is - as you >>know SELECT INTO just copies raw data without any underlying stuff. >>See what you'll get. >> From my experience there are a of of people who are allowed to mess with SQL databases but don't >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never >>imagine what another person could do - believe me, i just got quite a few awsome examples within the >>last month since i got this job :) >> >>Let me know how it works! >> >>Andrey > > > Hi Andrey, > > Thanks for your reply. I tried as you mentioned, inserting into new > table etc but to no avail. I did figure out what the problem was > though. > > This particular table had been upsized from a foxpro table. One of the > columns in the foxpro table had a maximum value of numeric 9999. > Somehow, someone had tried to insert a value large than this so foxpro > put in ****. On the upsize, and I can only assume here, sql must have > thought 'hang on, you must mean infinity here' and put a bit-wise > pattern (1.#INF) for infinity into this particular column for the > record. > > This only became evident when using Enterprise Manager and returning > all rows on the given table, it did display the record with the value > 1.#INF in the column for the 'missing' record. As to why it displayed > in EM and not Query Analyser is anyone's guess, but surely the queries > that led me to this initial discovery shouldn't have behaved like > this!!?? > > posting > > http://groups.google.com/groups?q=%23inf&hl=en&lr=&ie=UTF-8&group=comp.databases.ms-sqlserver&selm=ff18380f.0204191128.3778bcc5%40posting.google.com&rnum=1 > > gives some ideas. > > Thanks anyway, > > Andrew And how did you get rid of that infinity value in the in field?
I got rid of the infinity value using EM open table then typed in the
value I wanted. [quoted text, click to view] Andrey <leyandrew@yahoo.com> wrote in message news:<pir4d.28770$wV.19066@attbi_s54>...
> Andrew wrote: > > > Andrey <leyandrew@yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@attbi_s51>... > > > >>Andrew wrote: > >> > >>>Hi All, > >>> > >>>Have come across something weird and am after some help. > >>> > >>>Say i run this query where rec_id is a column of table arlhrl, > >>> > >>>select * from arlhrl where rec_id >= 14260 > >>> > >>>This returns to me 2 records with rec_id's of 14260 and 14261 > >>> > >>>Then I run this query > >>> > >>>select * from arlhrl where rec_id >= 14263 > >>> > >>>This returns 7 records with rec_ids of 14263 up. > >>> > >>>How come the first query doesn't return the records returned by the > >>>2nd query also? > >>> > >>>If I select for 14262 no records are returned. It is like this is a > >>>phantom record or has an end of file character in it. > >>> > >>>I tried re-creating the indexes but to no avail. If anyone has any > >>>ideas about what could be causing it or how to fix it it would be much > >>>appreciated. > >>> > >>>Thanks in advance, > >>> > >>>Andrew > >> > >> > >>Hi, > >> > >>First, stupid question - is the field 'rec_id' of integer type? > >>Why i am asking is because i had a similar example myself when i started with my new job - i was > >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this > >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :) > >> > >> > >>Second, what i'd do when i get into an unexplainable glitch: > >> > >>SELECT * INTO <new table> FROM <your table> > >> > >>And try to query the records from the new table without setting any indexes - just as is - as you > >>know SELECT INTO just copies raw data without any underlying stuff. > >>See what you'll get. > >> From my experience there are a of of people who are allowed to mess with SQL databases but don't > >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just > >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never > >>imagine what another person could do - believe me, i just got quite a few awsome examples within the > >>last month since i got this job :) > >> > >>Let me know how it works! > >> > >>Andrey > > > > > > Hi Andrey, > > > > Thanks for your reply. I tried as you mentioned, inserting into new > > table etc but to no avail. I did figure out what the problem was > > though. > > > > This particular table had been upsized from a foxpro table. One of the > > columns in the foxpro table had a maximum value of numeric 9999. > > Somehow, someone had tried to insert a value large than this so foxpro > > put in ****. On the upsize, and I can only assume here, sql must have > > thought 'hang on, you must mean infinity here' and put a bit-wise > > pattern (1.#INF) for infinity into this particular column for the > > record. > > > > This only became evident when using Enterprise Manager and returning > > all rows on the given table, it did display the record with the value > > 1.#INF in the column for the 'missing' record. As to why it displayed > > in EM and not Query Analyser is anyone's guess, but surely the queries > > that led me to this initial discovery shouldn't have behaved like > > this!!?? > > > > posting > > > > http://groups.google.com/groups?q=%23inf&hl=en&lr=&ie=UTF-8&group=comp.databases.ms-sqlserver&selm=ff18380f.0204191128.3778bcc5%40posting.google.com&rnum=1 > > > > gives some ideas. > > > > Thanks anyway, > > > > Andrew > >
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |