Sharepoint Web Analytics creates two databases, one for Web Analytics staging and the other one for Web Analytics Reporting. Setting up Web Analytics generated these errors
System.Data.SqlClient.SqlException: CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteScript(TextReader textReader, Int32 commandTimeout)
Solution: It looks like Sharepoint Analytics has Indexed views or computed columns in its Reporting database and for such objects ANSI_PADDING option needs to be set to ON. Besides ANSI_PADDING, the following settings need to be set.
SET optionsRequired valueDefault server valueDefault
OLE DB and ODBC valueDefault
DB-Library value ANSI_NULLSONONONOFF ANSI_PADDINGONONONOFF ANSI_WARNINGS*ONONONOFF ARITHABORTONONOFFOFF CONCAT_NULL_YIELDS_NULLONONONOFF NUMERIC_ROUNDABORTOFFOFFOFFOFF QUOTED_IDENTIFIERONONONOFF *Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.
Any time a new database is created, it takes the settings of the Model Database. In our enviroment ANSI_PADDING and ANSI_NULLS are off on Model database. But these settings can be changed for any individual objects. Sharepoint tried to do that, but we have DDL TRIGGER on Model database, hence on new database. This trigger fires any time a new object is created and the trigger SETS ANSI_NULLS to OFF. This was conflicting with the database creation.
After disabling the trigger, Web Analytics install ran successfully.
System.Data.SqlClient.SqlException: CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteScript(TextReader textReader, Int32 commandTimeout)
Solution: It looks like Sharepoint Analytics has Indexed views or computed columns in its Reporting database and for such objects ANSI_PADDING option needs to be set to ON. Besides ANSI_PADDING, the following settings need to be set.
SET optionsRequired valueDefault server valueDefault
OLE DB and ODBC valueDefault
DB-Library value ANSI_NULLSONONONOFF ANSI_PADDINGONONONOFF ANSI_WARNINGS*ONONONOFF ARITHABORTONONOFFOFF CONCAT_NULL_YIELDS_NULLONONONOFF NUMERIC_ROUNDABORTOFFOFFOFFOFF QUOTED_IDENTIFIERONONONOFF *Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.
Any time a new database is created, it takes the settings of the Model Database. In our enviroment ANSI_PADDING and ANSI_NULLS are off on Model database. But these settings can be changed for any individual objects. Sharepoint tried to do that, but we have DDL TRIGGER on Model database, hence on new database. This trigger fires any time a new object is created and the trigger SETS ANSI_NULLS to OFF. This was conflicting with the database creation.
After disabling the trigger, Web Analytics install ran successfully.