Skip to content

Cannot copy table when one specific column is included #1436

@tommy38hk

Description

@tommy38hk

I have a very strange error when using SQLServerBulkCopy for SQL Server 2017

A test table was created in source and destination databases as follows
CREATE TABLE dbo.TEST(
[col_1] [numeric](22, 0) NOT NULL,
[col_2] [numeric](22, 0) NOT NULL,
[col_3] [numeric](22, 0) NULL,
[col_4] nvarchar NULL,
[col_5] nvarchar NULL,
[col_6] nvarchar NULL,
[col_7] nvarchar NULL,
[col_8] nvarchar NULL,
[col_9] nvarchar NULL,
[col_10] varchar NULL,
[col_11] varchar NULL,
[col_12] nvarchar NULL,
[col_13] nvarchar NULL,
[col_14] nvarchar NULL,
[col_15] [numeric](15, 2) NULL,
[col_16] nvarchar NULL,
[col_17] [datetime] NULL,
[col_18] [datetime] NULL,
[col_19] nvarchar NULL,
[col_20] [numeric](7, 0) NULL,
[col_21] [numeric](3, 0) NULL,
[col_22] nvarchar NULL,
[col_23] nvarchar NULL,
[col_24] nvarchar NULL,
[col_25] [numeric](15, 2) NULL,
[col_26] [numeric](15, 2) NULL,
[col_27] nvarchar NULL,
[col_28] [numeric](10, 0) NULL,
[col_29] nvarchar NULL,
[col_30] nvarchar NULL,
[col_31] [numeric](15, 2) NULL,
[col_32] [numeric](15, 2) NULL,
[col_33] nvarchar NULL,
[MONTHLY_BILLING ] [numeric](15, 2) NULL,
[col_35] nvarchar NULL,
[col_36] nvarchar NULL,
[col_37] [numeric](2, 0) NULL,
[col_38] nvarchar NULL,
[col_39] nvarchar NULL,
[col_40] [numeric](7, 2) NULL,
[col_41] [numeric](5, 0) NULL,
[col_42] [numeric](5, 0) NULL,
[col_43] [numeric](5, 0) NULL,
[col_44] nvarchar NULL,
[col_45] [numeric](5, 0) NULL,
[col_46] [numeric](15, 2) NULL,
[col_47] [numeric](5, 0) NULL,
[col_48] [numeric](5, 0) NULL,
[col_49] nvarchar NULL,
CONSTRAINT [test_PK] PRIMARY KEY CLUSTERED
(
[col_1] ASC,
[col_2] ASC
)
)

But when I call the bulk insert including the MONTHLY_BILLING column for some reason, it throws the following exception
2020-09-24 11:30:57.998 INFO 22416 --- [ main] com.x.x.x.CopyService : mydb.dbo.TEST

2020-09-24 11:30:57.998 INFO 22416 --- [ main] com.x.x.x.CopyService : select [col_1],[col_2],[col_3],[col_4],[col_5],[col_6],[col_7],[col_8],[col_9],[col_10],[col_11],[col_12],[col_13],[col_14],[col_15],[col_16],[col_17],[col_18],[col_19],[col_20],[col_21],[col_22],[col_23],[col_24],[col_25],[col_26],[col_27],[col_28],[col_29],[col_30],[col_31],[col_32],[col_33],[MONTHLY_BILLING],[col_35],[col_36],[col_37],[col_38],[col_39],[col_40],[col_41],[col_42],[col_43],[col_44],[col_45],[col_46],[col_47],[col_48],[col_49] from mydb.dbo.TEST

java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Column MONTHLY_BILLING is invalid. Please check your column mappings.

But if I exclude that column, then it returns normally.

Any idea?

Here the code
public void bulkInsert (String catalog, String schema, String tableName) throws SQLException {
ResultSet rsSourceData;
String selectColumns = getSelectColumns(catalog, schema, tableName);
String fullTableName = catalog+"."+schema+"."+tableName;
String sourceSQL="select "+selectColumns+" from "+fullTableName;

    try (Connection sourceConnection = jdbcTemplateSource.getDataSource().getConnection();
         Connection destinationConnection = jdbcTemplateTarget.getDataSource().getConnection();

         Statement stmt = sourceConnection.createStatement();

         SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection)) {
        log.info(fullTableName);
        log.info(sourceSQL);
       
        rsSourceData = stmt.executeQuery(sourceSQL);
        ResultSetMetaData rsmd = rsSourceData.getMetaData();
        int columnCount = rsmd.getColumnCount();

        for (int i = 1; i <= columnCount; i++ ) {
            String name =rsmd.getColumnName(i);
            //if (!name.equalsIgnoreCase("MONTHLY_BILLING")) {
                bulkCopy.addColumnMapping(name,name);

           // }
            // Do stuff with name
        }

        bulkCopy.setDestinationTableName(fullTableName);

        SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
        copyOptions.isKeepIdentity();
        copyOptions.setBatchSize(102400);
        copyOptions.setCheckConstraints(true);
        copyOptions.setBulkCopyTimeout(0);
        bulkCopy.setBulkCopyOptions(copyOptions);
        // Write from the source to the destination.
        bulkCopy.writeToServer(rsSourceData);

        // Perform a final count on the destination
        // table to see how many rows were added.
        int countAfter = getRowCount(stmt, fullTableName);
    } catch (SQLException e) {
        if (e.toString().indexOf("Violation of PRIMARY KEY constraint")!=-1) {
            log.warn("Ignroing "+e.toString());
        }
        else {
            throw new SQLException(e);
        }
    }
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    BugA bug in the driver. A high priority item that one can expect to be addressed quickly.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions