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: 5021006com.niku.union.persistence.PersistenceException: SQL error code: 229Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'.SQL error code: 50000Error 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 StudioSelect DatabasesRight click and Select Restore DatabaseDevice and AddLocate the mssql_base.db (all file types)Select that and restore (the database name is niku and ppm_dwh)
Login as sa
sp_addlogin @loginame = 'niku' , @passwd = 'Clarity15' , @defdb = 'niku'
use nikusp_addlogin @loginame = 'niku' , @passwd = 'Clarity15' , @defdb = 'niku'
ALTER USER niku WITH LOGIN=niku
USE masterGRANT VIEW SERVER STATE to niku
ALTER DATABASE nikuSET ARITHABORT ONALTER DATABASE nikuSET ANSI_NULLS ONALTER DATABASE nikuSET QUOTED_IDENTIFIER ON
EXEC SP_DBCMPTLEVEL niku, 130
ALTER DATABASE nikuSET 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'.
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.
Thanks. It is installed. Will make another try and verify before that, that the service is started and running.
Any luck Martti?
I can see it is started in Services as well.
The error suggest to me that there is something wrong with the database setup.
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?