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);
}
}
}
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;