SET Options issue on SQL Server 2014

Transact-SQL
1

Hello, I am creating a table which has a persisted computed HashByte column. After the table is created when I try to insert data from CSV to that table it complains: INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Some of the columns in CSV has data more than 8000 length so before inserting data in table I have Set ANSI_WARNINGS OFF so that it does not complain about data truncation. This was working perfectly until there was a requirement to include computed column.

So the question is How can I keep persisted computed column and at same time Set ANSI_WARNINGS OFF property enabled?

2

I hope this helps .. I dont know the answer :slight_smile:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-2017#considerations-when-you-use-the-set-statements

Image

image
image.png3420×1185 415 KB

3

You can't - the requirement for computed columns means you need to address those columns in a different manner. If you don't care about the data being truncated then make sure you truncate the data before trying to insert it into the table.

If you are using SSIS - then you can add a data conversion task to convert those columns, or you can do the conversion in a derived column transformation.

The better option would be to utilize the right data type to hold that data - which would be a varchar(max) - that way you get all of the expected data and don't get an error.