VERIFIED SOLUTION i
X

Resolve "Update Failed" error while executing an update statement on a table with filtered index in Sagent

Issue

Following error message is encountered while executing an update statement in SQL Command Transform, on a table with Filtered index :

"UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, CONCAT_NULL_YIELDS_NULL, 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."

Cause

The following commands are sent from Sagent to the database prior to performing the update:

SET ansi_nulls OFF 
SET ansi_padding ON 
SET ansi_warnings OFF 
SET concat_null_yields_null OFF 
SET quoted_identifier ON 

The variable set to OFF in the above command are causing this issue.

Resolution

UPDATED: April 26, 2017


Update Sagent Registry settings by using the following steps, and then run the plan again:- 
 
1. Go to Registry Editor
2. Go to path : HKEY_LOCAL_MACHINE\SOFTWARE\Sagent\DataMart\RW\Customize\OLE DB\Microsoft OLE DB Provider for SQL Server 
3. Right Click and Create new DWORD with name as SetAnsiNulls , SetAnsiPadding , SetAnsiWarnings , SetConcatNullYieldsNull , SetQuotedIdentifier separately. 
4. Right click on each and click on Modify to set Value data as '1'.
 
If the issue is unresolved after following these steps, contact customer support for further assistance.

Environment Details

Product Feature: Design Studio

Database: SQL Server

Downloads

  • No Downloads