select * from dba.aTbl where key in (1,2,9,10)

gave me only 2 records but if I change the criteria to

key=1 or key=2 or key=9 or key=10

then I got all 4 records, I have recently updated the database in sql 11.01. to the latest version so I could try out clr integration what can I do to fix the problem?

must the table have index built for primary key in order to use in( ) function ?

asked 18 Jul '13, 17:25

gg99's gravatar image

gg99
227293038
accept rate: 10%

edited 18 Jul '13, 20:48

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


That is a very creepy symptom... IN doesn't work, then works again after dbvalid.

You may be having a problem with bad RAM. That can masquerade (actually pretty much always DOES masquerade) as other kinds of problems like corrupt disk data. In your case, the "corruption went away" which could mean the RAM problem is transient, or the data was placed in a different (good) part of RAM in the test that worked.

If you suspect bad RAM, stop using that computer for anything important right away... data temporarily corrupted by bad RAM can easily be written back to disk making the corruption permanent.

Do a google search on "memtest" and pick one to run. Wiggling the RAM to reseat it often works. If you bought bargain-priced RAM then you got what you paid for (e.g., you let Best Buy pick the brand when upgrading :)

permanent link

answered 19 Jul '13, 07:00

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 19 Jul '13, 07:01

Breck's point about being concerned is very true - a validation won't have fixed any problems, in the way that say unloading and re-loading may fix a corrupt index. A similar sounding problem that I had some years ago WAS bad RAM, it does happen. In our case the problem was very high up in the addresses so it only happened very occasionally on that particular machine. I don't know how it works on modern OSs (this was NetWare) but it seemed that other things being equal RAM was allocated from the bottom up.

(19 Jul '13, 07:15) Justin Willey

Breck, thanks for the heads up. My PC did have a number of crashes on wake up from hibernation/sleep.

I shutdown the PC, turned off power to it, unseated and re-seated firmly the memory chips (OCX with aluminum fins, not the cheap one but supposedly designed for overclock but I did not).

I went thru memory diagnostic 4 times over, no problem turned up.

Once I turned on the PC I loaded up apps to the brink, 7.5 out 8 GB. Ran Sybase central, open isql, ran the select with in. no problem.

let it the system idled till it went hibernate at night. next afternoon I woke up the machine uneventfully. every app still functioned without hiccup including VirtualBox

Hopefully it was a loose memory chip connection that got fixed. If I can run the PC w/o further crashes up for a couple weeks, I would blame problems on the loose connection and can feel at ease using the PC a couple years more. no more creepiness :):)

(20 Jul '13, 18:40) gg99

An index is not required - without an index on key the query engine will choose a table scan and should find all of the rows. Is 'key' the primary key for the table? If yes then there is already an index built for key.

The best explanation is that you have a corrupted database. Try validating your database (i.e. run dbvalid). If the problem is a bad database then you will need to rebuild (unload/reload) the database.

permanent link

answered 18 Jul '13, 20:54

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297
accept rate: 41%

the key is the primary key for the table.

(18 Jul '13, 23:58) gg99

thanks, despite full validation was completed without any complain, the select with in works now

good thing the database was relatively small and validation ran through quickly in matter of minute or two.

(19 Jul '13, 00:00) gg99
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×69
×32
×10

question asked: 18 Jul '13, 17:25

question was seen: 2,403 times

last updated: 20 Jul '13, 18:40