useBulkCopyForBatchInsert : no ouput / slower / error

[JDBC-0]

Driver version

12.8.1

SQL Server version

Microsoft SQL Server 2019 (RTM-CU28-GDR) (KB5042749) - 15.0.4390.2 (X64) Aug 12 2024 13:08:42 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10 Pro

JAVA/JVM version

21.0.4

Problem description

After we adjusted some tables from DATETIME to DATETIME2, changed all our prepared stored procedures to prepared INSERT statements, following all restrictions in "Known limitations" (https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver16#known-limitations) and switching URL parameter "useBulkCopyForBatchInsert" between true and false, we don't see any effect of this parameter!

Expected behavior

Batch INSERTs are faster with useBulkCopyForBatchInsert=true or info/warning message is logged if driver falls back to default batch INSERTs. As "Known Limitations" saiys:

"If the query fails because of errors unrelated to the SQL Server instance, the driver logs the error message and falls back to the original logic for batch insert."

Actual behavior

"errorCode=4022, message=Massenladedaten wurden erwartet, aber nicht gesendet. Der Batch wird beendet., SQLState=S0001, cause=null"
(4022: Bulk load data was expected but not sent. The batch will be terminated.)

Switching back to useBulkCopyForBatchInsert=false, no error occurs.

The problem interferes with #1784 (Bulk copy: fails with combination sendStringParametersAsUnicode=false). Also setting sendStringParametersAsUnicode=true , this error does not occur.

With
sendStringParametersAsUnicode=true;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=true
no error occurs, but the repeated test runs are SLOWER!

To avoid unexpected errors, we want to stay with sendStringParametersAsUnicode=false , but this causes the error above.

If we then set sendTemporalDataTypesAsStringForBulkCopy=false , the error occurs again.

We have no idea what other parameters in which combinations are required/necessary for bulk insert to work. We nocied that noo error occurs when alle 3 parameters

sendStringParametersAsUnicode=true;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=true

are either all "false" or either all "true". None of them show any performance improvements.

Any other details that can be helpful

JDBC URL: jdbc:sqlserver://{DBSERVERADR};databaseName=myDB;applicationName=myServer-{HOSTID};workstationID={HOSTID};integratedSecurity=true;sendStringParametersAsUnicode=false;encrypt=false;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=false

The mass INSERTs are done on connections with autocommit OFF and committing after each executeBatch().

[lilgreenbird]

this looks to be a duplicate of #1784 which is still under investigation. Please limit all correspondence to that issue to avoid any duplication.

[JDBC-0]

@lilgreenbird : it's not the same: #1784 is a special case with sendStringParametersAsUnicode .... this ticket here #2527 is a general ticket about bulk copy: how to detect what the driver is doing? We don't see any log output if the driver somehow decides not to use bulk copy feature.

It would be helpful to have at least a warning log entry why it is not used. And if it is used, an info log message would also be helpful to indicate that bulk copy feature is actually used. And if log messages are not feasable, at least some API to get the state of the bulk copy usage would be helpful. E.g. after the batch inserts, an API method offers the bulk copy state: either the state is "bulk copy was used" or "bulk copy wasn't used due to this reason: ....".

[JDBC-0]

Is there any kind of documentation how to get this working? We set ab our tables and replaced DATE/DATETIME with DATETIME2 because this is probably a requirement for bulk insert to work. What URL Parameters are required with what value? And what URL Parameters do hurt/deactivate bulk insert? How can we output/log/inspect if the driver is actually using bulk insert?

[JDBC-0]

I tested the bulk insert feature again with additional URL parameters:

...;integratedSecurity=true;encrypt=false;sendStringParametersAsUnicode=false;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=false;cacheBulkCopyMetadata=true

and get this error again:

com.microsoft.sqlserver.jdbc.SQLServerException: Massenladedaten wurden erwartet, aber nicht gesendet. Der Batch wird beendet. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:270) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1735) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:3025) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2873) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4391) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:276) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2264)

Error message translated: "Bulk load data was expected but not sent. The batch will be terminated.". Changing ;sendStringParametersAsUnicode=true didn't help (and we wouldn't want this anyway).

We changed out table columns (for a specific task) to replace DATE/DATETIME with DATETIME2 and user batch INSERT statements.

This was tested with driver 12.8.1 ... but tests with latest 12.10.0 shows the same error.

Info: our application connects to a database "dbA", calls procedurs from this database "A" but the destination tables the mass data is stored to is in database "dbB", so, our prepared statement looks like

INSERT INTO dbB.dbo.MyTable VALUES (?,?,?,...)

Might this be a problem?