Double PK values with leading zeros

  • LaanemetsArvi

    Valued Member

    Points: 65

    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?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

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

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

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

    MVDBA

  • LaanemetsArvi

    Valued Member

    Points: 65

    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: 21757

    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

    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.

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

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