Bug: Incorrect syntax near the keyword 'WITH' BulkMergeAsync

[stefandevo]

I tried

var mergedRows = await connection.BulkMergeAsync(syncConsumers, batchSize: 1000, hints: SqlServerTableHints.NoLock, bulkCopyTimeout: 300);

This caused

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'AS'.

If I remove the hints then it worked. Note that I used the same hint for the BulkInsert command with success.

Is there an option to get the last generated SQL statement in such cases to further see what could be wrong?

BTW: Great lib!

[mikependon]

Hey, thanks for reporting, we will look into this. When do you need the fix?

[stefandevo]

Not urgent; just wanted to report; I removed the hints for now as it's not crucial at the moment.

[mikependon]

@stefandevo - I had investigated this one. The fix is very simple, we just have to switch the line 526 and line 527 from the SqlConnectionExtension.cs class.

Would you be able to provide a PR for this? Your help is highly appreciated - that matters and counts. TIA

[stefandevo]

Hi @mikependon no problem, but investigated the source and I see same for GetBulkUpdateSqlText, GetBulkDeleteSqlText. The Insert statement is the only one where the table alias is not place, and so the hints are added after the tableName.

[mikependon]

Cool and thanks for sleuthing around ;) - see my Tweet here. Therefore, you do not need to worry the other methods IMO.

Also, the RepoDb.SqlServer method CreateMerge has the proper ordering, unlike the one in the BulkOperations.

[mikependon]

The fixes for this is now available on the version >= RepoDb.SqlServer.BulkOperations v1.1.2-beta1. Please upgrade your copy by simply typing the command below in your PCM.

> Install-Package RepoDb.SqlServer.BulkOperations -version 1.1.2-beta1

Thank you for the PR in relation to this issue.

[mikependon]

Closing this one now.