SQLdmDatabaseInstall.sql incorrect settings: 'ANSI_WARNINGS'

Hello,

can you help me with IDERA SQL Diagnostic Manager Installation please ? My boss asked me to try and choose best monitoring tool for our SQL Servers. I am trying IDERA now, downloaded trial version but i am not able to install Diagnostic Manager.

 

Product: IDERA SQL Diagnostic Manager (x64) -- Error 27506.Error executing SQL script SQLdmDatabaseInstall.sql. Line 103. SELECT 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. (1934)

 

We are using MS SQL 2016 (13.0.4435) on Windows Server 2016. (64 logical CPU, 1 TB RAM)

 

Thnak you for your help

  • Here is an KB article which explains what is happening:

    Solution Title Installation error: INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'
    Solution Details
    SYMPTOM:

    SQLdm installation fails with the following error:

    "Error 27506. Error executing SQL script SQLdmDatabaseInstall.sql.Line 307. 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 index..."

    CAUSE:

    This error message is an indication that there's a system database customization that's impacting the SQLdm installation kit's ability to run a CREATE DATABASE command.

    Filters or triggers set up in the model database will cause the CREATE DATABASE statement in our repository installation script to incorporate them into the new SQLdmRepository database. These triggers cause the above error to be thrown when completing the database installation.

    System level (system database) other than those on the model database could also impact the install in this way.

    SOLUTION:

    Either temporarily remove the triggers from the model database so that the new database can be created "clean" or create a new database called "SQLdmRepository", remove any triggers that get created, and then run the database installation script manually against it.
  • Also check Server Objects to see if there are any triggers there.