Difficult SQL 7 Problem

[chrismilnenaq]

Hello,
I'm using W2K sp2 and SQL 7 sp3. I have a table that supposedly has duplicate rows. I run this query to list the duplicate rows 'SELECT symbol, ddate FROM histdata GROUP BY symbol, ddate HAVING Count(*)>1)'. I delete the duplicate rows and run the above duplicate row statement again. And it gives me more duplicate rows. This cycle keeps on going on for a while, until there are supposedly no more duplicate rows. I then try to create an index on that table and it gives an error saying that there are duplicate rows and the index can't be created. It will say the duplicate row is <NULL> but I specified that those two columns can't be null way before. So I search for null values in those two rows and the query comes back with nothing. I'm puzzled, hopefully you can help. Thanks in advance.

Chris
chris@naq.com

 

[sguslan]

What are the data types of ddate and symbol and is any an identity column?

 

[TruthAndBeauty]

I suggest you also include the count in the SELECT output as well as in the condition so you can tell how many duplicates (more than 2) there are to be deleted.

I'm not sure where or when you &quot;specified&quot; the nulls. Be sure the table field property is set to no allow nulls or empty strings. (&quot;&quot.

Are you using a composite (field field) for an index. If so evaluate each column separately for nulls or empty strings. Watch out for other fields (columns) that contain null when they should not. Sometimes an error message indicates the wrong bad guy.

When the cause of a problem is hard to isolate, start breaking down the playing area. copy the table then use this copy to delete groups of records and then try to index it. If the problem remains, delete some more. If the problem is gone, start adding records.

Good luck.

Robert

 

[chrismilnenaq]

Thanks for your fast reply.
The data types for the first column is varchar and the second column is smalldatetime. I'm using these because the source table is set up this way. Neither column is an identity column.

I've used the count column is the select output and it always turns up as 2. I create the no null property when I create the table before the dts transaction. When the index gives me an error for null values in the primary key I run a statement to delete the lines with null and it comes back with 0 rows deleted. I know that the other 6 rows (excluding the 2 primary key fields) have nulls. I've tried to delete those, and it does but it still won't create the index.
I have tried breaking down the playin area as far as I can go, but the loop still continues.
It's wierd sometimes after trying to create the index, I'll reboot the computer and it will find no duplicates and create the index. Other times it will find other duplicates. Say I run the query to find duplicate rows twice in a row, both times it finds different rows.
Keep on giving me suggestions, I appreciate it.
Thanks.

 

[TruthAndBeauty]

Have you done a compact and repaire database; if not...

If the problem occurs on different databases, your ACCESS software may ned to be uninstalled and reinstalled. If this only occurs on one database then try this.
Keep a copy of you current database and use a copy to start deleting everything (forms, tables, fields, code, etc.) that is not absolutely need or associated with this problem. Then do a compact and repire again. Does the problem persist?
If so delete some more. If the problem appears to have gone and stays gone after several more runs and variations then start adding the most relwative items and test several more times before adding additional items. Some trend will show.

Good luck.