I have imported the base dwh image and restored to database ppm_dwh.
When I try to set up the dblink I get an error:
"Failed to update properties on the Jaspersoft server. Could not login to http://server:8080/reportservice with username ppmjasperadmin"
Can anyone please advise what I need to do? Thanks.
The dataware house db link and Jaspersoft error is different. Can you please confirm that you have given the below rights
GRANT ALTER ANY LOGIN TO PPM_DWH
GRANT ALTER ANY LINKED SERVER TO PPM_DWH
Common procedures can be used to create DBLINK for both Microsoft SQL and Oracle from CA Clarity PPM. The procedures can be called directly from the Data Warehouse schema if necessary.
CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),
Example: Microsoft SQL DBLINK Creation
Follow these steps:
1. Verify that the following privileges are granted to the PPM_DWH schema:
2. To verify that PPMDBLINK is working, run the following query from the PPM_DWH schema:
SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources
If the link works, data is returned.
Thank you Suman,
I granted the ALTER ANY LOGIN and ALTER ANY LINKED SERVER rights to PPM_DWH
returns an error:
Must declare the scalar variable "@P_DB_LINK_NAME"
Could not find stored procedure 'CMN_DBLINK_SP'
Can you please advise?
Did the import worked fine?
I deleted my first attempt and started again.
The import and restore of ppm_dwh has completed successfully.
Now I am at the setup login name stage.
"Set up a valid login name for use by CA Clarity PPM. This login name and password are the values that you specify in CA Clarity System Administration to log in to the database. The login name and password can be any combination. Set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server."
So Im going to Security -> Logins -> New Login
Login name: ppm_dwh
SQL Server Authentication
default database: should this be ppm_dwh or master?
Where do I set the sql server database schema as niku>
It should be DWH
Ok, and where do I "set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server." ?
I should probably add that I have a separate server from my Clarity db.
has anyone else run into this problem?
I have granted all the necessary access to ppm_dwh
but when I try to set up my ppmdblink I cannot get past this:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@P_DB_LINK_NAME".
This query will not run because none of the variables are declared, this is normal. In fact, this is just an example of the query and the parameters you have to supply prior to running it in the Installation Guide. If you look further, here is the example:
Example: Microsoft SQL DBLINK Creation
So you have just to supply the correct parameters for it as per the example and run it on your DWH database, and this should work fine. Keep in mind hostname is db hostname for Clarity, schema, user and password are also for Clarity, and your schema might be different from niku.
Once the link creates fine, run this query to check if the link is working (provided your schema name is niku, otherwise please replace the first niku reference):
SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources
Please let me know how it goes.
Principal Support Engineer
Thank you Nika.
So if I understand correctly my command will look like this:
when I run this however I get:
"Could not find stored procedure 'CMN_DBLINK_SP'"
When I do a search for all stored procedures in the ppm_dwh database however, the cmn_dblink_sp stored procedure is listed.
I suspect your Microsoft SQL Server installation does not have the correct server level collation; it is meant to be a case-insensitive collation setting (we in fact only support one collation, SQL_Latin1_General_CP1_CI_AS): CA PPM 14.2 On Premise Installation Guide (restricted content): Configure SQL Server
When you have a case sensitive server-level collation, it applies to the 'identifiers' in the database too (e.g. function names, stored procedures, and even table/column names, and some cross-database scoped variables), it isn't just a data setting.
Thank you Nick.
I checked the server collation and it appears to be ok:
Is there anything else I should be looking for?
Not exactly, it's actually the Clarity database you would specify, example:
You basically have to create the link on ppm_dwh database to niku database. Since your database collation appears to be correct, could you please check you run it on the correct database. You have to run this statement on ppm_dwh, connected as ppm_dwh.
Thank you for your patience Nika!
I think I have it now.
I have a separate server (I'll call it new_server here) from my database server (old_server)
So in my command should I be pointing at my old_server or my new_server ?
And I guess it will be the niku password on my db server I should use?
Let's be more precise and say we need to link new database (DWH) to the existing database Niku (Clarity). So you should use Clarity server hostname, schema, user and password as parameters, and execute the command on DWH database whilst connected with ppm_dwh user.
Ok so I think I have my dwh communicating with my db now.
I ran the script to test the link and got results back:
In the NSA, when I try to save my parameters the status will not change to 'Available' however. Even though it would appear that my dwh and db are communicating?
My port is 1433 and is definitely open.
I believe my parameters are correct also.
Login Name: I set this as ppm_dwh, the account is unlocked and password is ok.
Hostname: I tried both my data warehouse server name and its IP here.
Database name: Should this be the name of my Clarity db or my data warehouse db (ppm_dwh)
Service name: ?
I think I might need to set up an ODBC connection between my dwh server and my Clarity db server? Whatever I call this is what will be populated in the service name above?
I'm glad it's not the collation problem, firstly.
An ODBC connection shouldn't be needed, if you check the contents of the cmn_dblink_sp then the procedure and parameters it uses to create the link is being done specifically with the 'sqlncli' provider (SQL Native Interface), it's not using an ODBC driven provider.
So the parameters are (in order):
1. A personal 'name' or reference for your link (e.g. 'PPMDBLINK')
2. The hostname of the db server where your main 'niku' schema for Clarity resides (this has to be a hostname that your DWH database server recognises and can resolve).
3. Clarity schema name of the db server from step 2 (which will be 'niku').
4. The username / login of the db server from step 3
5. The password for logging in as the user in step 4.
Thank you so much for all of your assistance guys.
In my NSA I now see my DHW Status as available and my PPMDBLINK appears to be working.
May have spoken too soon!
OK - so I was able to run CMN_DBLINK_SP 'PPMDBLINK','ClarityDbServer','NIKU','NIKU','password'
against the ppm_dwh database whilst logged in as the ppm_dwh login user
This completed successfully.
However when I tried to verify using - SELECT count(1) FROM SRM_RESOURCES@PPMDBLINK
I get the following: -
Msg 208, Level 16, State 1, Line 1
Invalid object name 'SRM_RESOURCES@PPMDBLINK'.
This is what's confusing though - when I went to my NSA and populated the parameters as Nick suggested my status has actually changed to 'Available'.
But, when I save I still get a connection type error - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.
So it would appear that my dwh and db are not communicating properly just yet.
OK, some further progress.
I created the DBLink OK, and now when I run the verification check I no longer get an error, "
Invalid object name 'SRM_RESOURCES@PPMDBLINK'."
When I log into my NSA and look at the Data Warehouse Tab it says the status is 'Available'
Again though, when I chose Save it tells me that - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.
I'm unsure as to whether the DBLink is set up correctly or not!
How about: select count(1) from niku.SRM_RESOURCES@PPMDBLINK
I haven't given this a try myself yet, but just suspecting since the dwh database user's default schema isn't going to be 'niku', that you may have to qualify it explicitly.
It looks like now your link is set up properly, since the results are returned. It might be something else that causes the message. Please check the following permissions have been granted to ppm_dwh user:
GRANT VIEW SERVER STATE to PPM_DWHGRANT ALTER ANY LOGIN TO PPM_DWHGRANT ALTER ANY LINKED SERVER TO PPM_DWH
Then please retry and let me know if it works. If it does not, I see you have opened a Support case : we can set up a webex to resolve this further on a call.
Nika HadzhikidiCA TechnologiesPrincipal Support Engineer
The ppm_dwh user definitely has those permissions applied. It's strange as I say because data is being returned, but when I choose Save in NSA it's telling me that the link could not be created successfully.
Can we please set up a webex session? I feel that it is probably just some small issue that could be resolved quicker over a phone call. Thank you!