View Revisions: Issue #6778

Back to Issue
Summary 0006778: Debug Sybase error Explicit value specified for identity field in table ABC when 'SET IDENTITY_INSERT' is OFF
Revision 2025-03-21 02:26 by pbelov
Description Customer reported an issue with SQL Server to Sybase Compare and Repair:
Sybase side throws an error
Explicit value specified for identity field in table ABC when 'SET IDENTITY_INSERT' is OFF

In the local environment issue only happens for a specific table see below
Revision 2025-05-02 20:32 by pbelov
Description Customer reported an issue with SQL Server to Sybase Compare and Repair:
Sybase side throws an error
Explicit value specified for identity field in table ABC when 'SET IDENTITY_INSERT' is OFF

In the local environment issue only happens for a specific table see below, most likely due to an earlier error which is leaving JdbcTransaction object in the SET IDENTITY_INSERT =ON state for the PRIOR table (and only 1 table is allowed on Sybase per transaction/connection).
Revision 2025-03-21 02:26 by pbelov
Steps To Reproduce Problem table (when Performing a full load) from MS SQL Server to Sybase:

    <table name="student">
        <column name="stud_id" primaryKey="true" primaryKeySeq="1" required="true" type="NUMERIC" size="18" autoIncrement="true">
            <platform-column name="mssql2016" type="numeric() identity" size="18"/>
        </column>
        <column name="address_id" required="true" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="ssn" required="true" type="CHAR" size="9">
            <platform-column name="mssql2016" type="char" size="9"/>
        </column>
        <column name="stud_lname" required="true" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_fname" required="true" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_mi" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="dob" required="true" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="sex" required="true" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="race_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="birth_country_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="birth_city" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="birth_st_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="drivers_lic" type="CHAR" size="15">
            <platform-column name="mssql2016" type="char" size="15"/>
        </column>
        <column name="drivers_lic_st_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="no_exemptions" required="true" type="SMALLINT" size="5">
            <platform-column name="mssql2016" type="smallint" size="5"/>
        </column>
        <column name="parental_consent" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="pass_consent" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="web_theme_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="driver_lic_stat_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="driver_lic_exp_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="stud_lname_upper" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_fname_upper" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="add_user" type="CHAR" size="8">
            <platform-column name="mssql2016" type="char" size="8"/>
        </column>
        <column name="add_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="last_mod_user" type="CHAR" size="8">
            <platform-column name="mssql2016" type="char" size="8"/>
        </column>
        <column name="last_mod_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="image_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="other_name" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="drivers_lic_type_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="drivers_lic_rcvd_on_ctr" required="true" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="drivers_lic_rcvd_on_ctr_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="drivers_lic_dfnsv_class_cd" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="last_arrived_enrtype_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="accom_plan_required" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="ad_guid" type="CHAR" size="36">
            <platform-column name="mssql2016" type="char" size="36"/>
        </column>
        <column name="ad_name" type="VARCHAR" size="80">
            <platform-column name="mssql2016" type="varchar" size="80"/>
        </column>
        <column name="ad_ctr_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="ad_pass_expire_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="ad_last_login_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="alumni" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="ethnicity_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="gender_identity_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="other_gender_identity" type="VARCHAR" size="30">
            <platform-column name="mssql2016" type="varchar" size="30"/>
        </column>
        <column name="preferred_pronoun_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="other_preferred_pronoun" type="VARCHAR" size="30">
            <platform-column name="mssql2016" type="varchar" size="30"/>
        </column>
        <unique name="student_i1">
            <unique-column name="ssn"/>
        </unique>
        <index name="student_i3">
            <index-column name="stud_lname_upper"/>
        </index>
        <index name="idx_last_enrtype">
            <index-column name="last_arrived_enrtype_id"/>
        </index>
        <index name="idx_student_add_guid">
            <index-column name="ad_guid"/>
        </index>
        <index name="idx_student_add_name">
            <index-column name="ad_name"/>
        </index>
        <index name="idx_student_imageid">
            <index-column name="image_id"/>
        </index>
        <index name="idx_student4view">
            <index-column name="stud_id"/>
            <index-column name="ssn"/>
            <index-column name="dob"/>
            <index-column name="sex"/>
            <index-column name="race_cd"/>
            <index-column name="address_id"/>
        </index>
        <index name="student_ad_name">
            <index-column name="ad_name"/>
        </index>
        <index name="student_fk">
            <index-column name="address_id"/>
        </index>
        <index name="student_i2">
            <index-column name="stud_lname"/>
            <index-column name="stud_fname"/>
        </index>
        <index name="student_i4">
            <index-column name="stud_fname_upper"/>
        </index>
    </table>
Revision 2025-05-28 15:15 by pbelov
Steps To Reproduce Not able to reproduce directly. Suspecting that the JdbcTransaction gets discarded upon (unknown) exception path, leaving table in the IDENTITY_INSERT=on mode.
Since only one table per transaction can have this mode, the next table that happens to need this turned ON, fails to do so.

Problem table (when Performing a full load) from MS SQL Server to Sybase:

    <table name="student">
        <column name="stud_id" primaryKey="true" primaryKeySeq="1" required="true" type="NUMERIC" size="18" autoIncrement="true">
            <platform-column name="mssql2016" type="numeric() identity" size="18"/>
        </column>
        <column name="address_id" required="true" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="ssn" required="true" type="CHAR" size="9">
            <platform-column name="mssql2016" type="char" size="9"/>
        </column>
        <column name="stud_lname" required="true" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_fname" required="true" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_mi" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="dob" required="true" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="sex" required="true" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="race_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="birth_country_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="birth_city" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="birth_st_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="drivers_lic" type="CHAR" size="15">
            <platform-column name="mssql2016" type="char" size="15"/>
        </column>
        <column name="drivers_lic_st_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="no_exemptions" required="true" type="SMALLINT" size="5">
            <platform-column name="mssql2016" type="smallint" size="5"/>
        </column>
        <column name="parental_consent" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="pass_consent" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="web_theme_cd" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="driver_lic_stat_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="driver_lic_exp_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="stud_lname_upper" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="stud_fname_upper" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="add_user" type="CHAR" size="8">
            <platform-column name="mssql2016" type="char" size="8"/>
        </column>
        <column name="add_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="last_mod_user" type="CHAR" size="8">
            <platform-column name="mssql2016" type="char" size="8"/>
        </column>
        <column name="last_mod_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="image_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="other_name" type="CHAR" size="20">
            <platform-column name="mssql2016" type="char" size="20"/>
        </column>
        <column name="drivers_lic_type_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="drivers_lic_rcvd_on_ctr" required="true" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="drivers_lic_rcvd_on_ctr_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="drivers_lic_dfnsv_class_cd" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="last_arrived_enrtype_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="accom_plan_required" type="CHAR" size="1" default="N">
            <platform-column name="mssql2016" type="char" size="1" default="N"/>
        </column>
        <column name="ad_guid" type="CHAR" size="36">
            <platform-column name="mssql2016" type="char" size="36"/>
        </column>
        <column name="ad_name" type="VARCHAR" size="80">
            <platform-column name="mssql2016" type="varchar" size="80"/>
        </column>
        <column name="ad_ctr_id" type="NUMERIC" size="18">
            <platform-column name="mssql2016" type="numeric" size="18"/>
        </column>
        <column name="ad_pass_expire_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="ad_last_login_dt" type="TIMESTAMP" size="3">
            <platform-column name="mssql2016" type="datetime2" size="3"/>
        </column>
        <column name="alumni" type="CHAR" size="1">
            <platform-column name="mssql2016" type="char" size="1"/>
        </column>
        <column name="ethnicity_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="gender_identity_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="other_gender_identity" type="VARCHAR" size="30">
            <platform-column name="mssql2016" type="varchar" size="30"/>
        </column>
        <column name="preferred_pronoun_cd" type="INTEGER" size="10">
            <platform-column name="mssql2016" type="int" size="10"/>
        </column>
        <column name="other_preferred_pronoun" type="VARCHAR" size="30">
            <platform-column name="mssql2016" type="varchar" size="30"/>
        </column>
        <unique name="student_i1">
            <unique-column name="ssn"/>
        </unique>
        <index name="student_i3">
            <index-column name="stud_lname_upper"/>
        </index>
        <index name="idx_last_enrtype">
            <index-column name="last_arrived_enrtype_id"/>
        </index>
        <index name="idx_student_add_guid">
            <index-column name="ad_guid"/>
        </index>
        <index name="idx_student_add_name">
            <index-column name="ad_name"/>
        </index>
        <index name="idx_student_imageid">
            <index-column name="image_id"/>
        </index>
        <index name="idx_student4view">
            <index-column name="stud_id"/>
            <index-column name="ssn"/>
            <index-column name="dob"/>
            <index-column name="sex"/>
            <index-column name="race_cd"/>
            <index-column name="address_id"/>
        </index>
        <index name="student_ad_name">
            <index-column name="ad_name"/>
        </index>
        <index name="student_fk">
            <index-column name="address_id"/>
        </index>
        <index name="student_i2">
            <index-column name="stud_lname"/>
            <index-column name="stud_fname"/>
        </index>
        <index name="student_i4">
            <index-column name="stud_fname_upper"/>
        </index>
    </table>