Clarity PPM

Expand all | Collapse all

db setup - what went wrong? (15.5 fresh install)

  • 1.  db setup - what went wrong? (15.5 fresh install)

    Posted 11-25-2018 04:47 AM

    I did a fresh install of 15.5.

    Everything seemed to be OK and the PMO add-in with sample data installed without hick ups.

    However, after setting up the Datamart the Datamart Extraction failed

    bg-ca.log has lines like

     

    ERROR 2018-11-25 01:21:25,559 [Dispatch 1Datamart Extraction : bg@WIN-Q361UV7IEAP (tenant=clarity)] niku.njs (clarity:kinnunenad:5127055__3C2247C6-784E-433B-A7C1-9D8DDB087F72:1Datamart Extraction) Error executing job: 5021006
    com.niku.union.persistence.PersistenceException:
    SQL error code: 229
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'.
    SQL error code: 50000
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]-20999_CMN_DB_JOB_ADD_SP Checking for existing job - error 229 return code 0

     

    I set up the db like this

     

    In MS SQL Server Management Studio
    Select Databases
    Right click and Select Restore Database
    Device and Add
    Locate the mssql_base.db (all file types)
    Select that and restore (the database name is niku and ppm_dwh)

     


    Login as sa

     

    USE niku
    GO

     

    sp_addlogin @loginame = 'niku'
         , @passwd = 'Clarity15'
         , @defdb = 'niku'

     

     

    use niku
    sp_addlogin @loginame = 'niku'
         , @passwd = 'Clarity15'
         , @defdb = 'niku'

     

         USE niku

     

    ALTER USER niku WITH LOGIN=niku

     

    USE master
    GRANT VIEW SERVER STATE to niku

     

    ALTER DATABASE niku
    SET ARITHABORT ON
    ALTER DATABASE niku
    SET ANSI_NULLS ON
    ALTER DATABASE niku
    SET QUOTED_IDENTIFIER ON

     

    EXEC SP_DBCMPTLEVEL niku, 130

     

    ALTER DATABASE niku
    SET READ_COMMITTED_SNAPSHOT ON

     

    sp_configure 'remote query timeout',0

     

    reconfigure with override

     

    What did I miss or do wrong when the datamart extraction is looking for
    database 'msdb', schema 'dbo'.



  • 2.  Re: db setup - what went wrong? (15.5 fresh install)

    Posted 11-28-2018 06:22 AM

    Hi urmas ,

     

    Can you quickly check if SQL Server Agent is enabled in SSMS. The Datamart Extraction job will not work when the SQL Server Agent is enabled in Microsoft SQL Server Management Studio.



  • 3.  Re: db setup - what went wrong? (15.5 fresh install)

    Posted 11-28-2018 06:59 AM

    Thanks. It is installed. Will make another try and verify before that, that the service is started and running.



  • 4.  Re: db setup - what went wrong? (15.5 fresh install)

    Posted 12-04-2018 07:24 PM

    Any luck Martti?



  • 5.  Re: db setup - what went wrong? (15.5 fresh install)

    Posted 12-06-2018 02:44 PM

    No luck

    I can see it is started in Services as well.

    The error suggest to me that there is something wrong with the database setup.



  • 6.  Re: db setup - what went wrong? (15.5 fresh install)

    Posted 12-06-2018 02:58 PM

    I added some SQLAgent roles to the niku login for msdb databse and that did the trick.

    Why did I have to do that?

    What was missing from my script which worked before?