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.
2/20/2018 10:56:36 am

There are two ways to do business in modern days. One is by setting up traditional brick and mortar office or shop and another one is by selling products and services online. The line between the two has been blurry and in fact the two models have merged in recent years, giving birth to businesses reaching out to millions of customers worldwide via Internet while still maintaining a physical front for local customers. Regardless the industry you are in, there are many ways for you to market your business to the world by an effective online marketing campaign.

Reply



Leave a Reply.

    Author

    Write something about yourself. No need to be fancy, just an overview.

    Archives

    June 2012

    Categories

    All