Double PK values with leading zeros

Question

Post reply

Double PK values with leading zeros

LaanemetsArvi

Valued Member

Points: 65
More actions
March 12, 2020 at 11:25 am

#3733540

Hi
Because we need to read info from 3rd-party databases which uses dbf-files, we have a 32-bit SQL Server Express 2005. It is working for several years without problems, until somewhere in last month we have a problem with one table we read in.
The table has string field which is indexed as an unique index for table, and is read into SQL database table as primary key (varchar field). Lately the procedure returns an error "Violation of PRIMARY KEY constraint <PrimaryKeyName>. Cannot insert duplicate key in object <TableName>".
When checking for double values, I didn't get any. But I got ~30 pairs of values like "014" and "0014". Those are old entries mostly from years 2004 - 2005, and aren't edited for several years anymore.
So it looks like in last 30 days something happened with our SQL Server Express! Has someone an idea, for what to look after?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

Is it possible that your incoming data is too long for for your varchar PK and is getting truncated?

for example

001401926 becomes 0014 and you get a duplicate pk value

MVDBA

LaanemetsArvi Valued Member Points: 65 More actions · Answer 2

Longest value is "0000001", the SQL Database table field is varchar(15).

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

most likely nothing has happened to the installation of your database server.

More than likely it is the input file or the input process

how about using an "if not exists then insert else log the bad value" type scenario.. you may have to be brutal and swap to a cursor based approach (jeff moden don't shout) in order to debug the data issue.

so pseudo code

put data in a staging table
take each row 1 at a time and try an insert
log failures
analyse failure data
scrap everything from steps 1-4 and go back to the normal way you do it

MVDBA

LaanemetsArvi Valued Member Points: 65 More actions · Answer 4

It gets weirder and weirder. The row with PK value "0000001" did have 2 another fields which were empty. This did make this row faulty in real-life (but from table structure's point of view was all OK). I edited the query and excluded this row (and there wasn't any another row with ID like this) - and the query did run without any errors now. (Btw, it looks like ID field length in dbf-file is 7)

Anyway, for me the case is closed now - whatever the reason is that this did work!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

LaanemetsArvi wrote:

It gets weirder and weirder. The row with PK value "0000001" did have 2 another fields which were empty. This did make this row faulty in real-life (but from table structure's point of view was all OK). I edited the query and excluded this row (and there wasn't any another row with ID like this) - and the query did run without any errors now. (Btw, it looks like ID field length in dbf-file is 7)
Anyway, for me the case is closed now - whatever the reason is that this did work!

maybe have a chat with the provider of the data source, see if there is a truncation issue at their end.. ???? but putting field length 7 into field length 15 looks to be a mismatch

MVDBA

LaanemetsArvi Valued Member Points: 65 More actions · Answer 6

The field length for SQL table is set with perspective - this is preson's identificator in employee management DB, and the goal is to get in future together employee info for all sites of company (currently we get info about 3 sites). Sites are situated in different countries, and they use different employee management databases.