set ansi_warnings off before .BulkMerge()

[smitty-codes]

Hi, I'm trying to do a .BulkMerge against SQL Server and running into string truncation error "InvalidOperationException: String or binary data would be truncated.".

We have alot of bad data but our nvarchar columns are sized correctly for what we expect for good data and anything longer should just be auto truncated.

SQL Server has this option you can set for the transaction but I can't seem to make it work (also see https://www.sqlshack.com/sql-truncate-enhancement-silent-data-truncation-in-sql-server-2019/):
SET ANSI_WARNINGS OFF

What I'm doing in C#

using var dbConnection = GetConnection(agencyId);
if (dbConnection.State != ConnectionState.Open)
	dbConnection.Open();

trans = dbConnection.BeginTransaction();

var cmd = dbConnection.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SET ANSI_WARNINGS OFF;";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

trans.BulkMerge(values); /* error on this line despite setting ansi warnings off above: "InvalidOperationException: String or binary data would be truncated" */ 

trans.Commit();

[JonathanMagnan]

Hello @smitty-codes ,

We have something coming on the next release on Tuesday that might help you.

I will wait for this release and give you a better answer to explain how you could fix it without having to set this configuration.

Best Regards,

Jon

[smitty-codes]

@JonathanMagnan Cool, I’ll check back Tuesday.

it’s a lot like this issue someone posted on SO https://stackoverflow.com/a/65943205/194872

There are many of these transaction scope settings that would be nice to have access to but I’ll wait for the idea :-)

[JonathanMagnan]

Hello @smitty-codes ,

The v4.0.23 has been released with a fix for the AutoTruncate option.

The default value for AutoTruncate is null (no value). so when nothing is specified:

• The SqlCommand will always work
• The SqlBulkCopy will throw an error when the length is greater than the one in the database

When AutoTruncate = true:

• If the length is invalid, no error will be thrown (SqlCommand and SqlBulkCopy). The value is automatically truncated.

When AutoTruncate = false:

• If the length is invalid, an error should always be thrown (SqlCommand and SqlBulkCopy)

In your case, I believe you are looking for the AutoTruncate = true.

Do you still wish to be able to execute some commands before we fire anything on our side? As from what I understand, you might want to set some other configuration.

Best Regards,

Jon

[smitty-codes]

@JonathanMagnan That new AutoTruncate option might cover me for the time being. Will give it a try tomorrow or Friday, thanks.

[JonathanMagnan]

Hello @smitty-codes,

Since our last conversation, we haven't heard from you.

How is your test going?

Let me know if you need further assistance.

Best regards,
Jon

[JonathanMagnan]

Hello again!

a simple reminder that we are here to assist you.

Don't hesitate to contact us for further assistance.

BEst regards,

Jon

[smitty-codes]

Hey @JonathanMagnan - it is working as expected, thanks. I ripped the logic from #81 (comment)