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!
Thanks to Nika for helping me to resolve this!
DWH is now showing as Available and when I choose Save there are no error messages.
Some of my parameters were incorrect in NSA and once we corrected those everything was ok.
Thanks again to all for your help with this one,
I am having the exact same issue and have performed everything in this thread. Still to no avail. What were the settings that needed adjusting in the NSA?
I had the same problem creating the DBLink from CSA.
As workaround I created the DBLINK directly on the database with the scripts from the installation manual.
After that, just use the same DBLINK name previously created.
Hope it helps.
if DWH database shows as not Available in CSA, this will be due to the database connection, not the dblink. In this case, ensure you enter the correct parameters to connect to the database. Here is an example screenshot:
You have to specify the user login ppm_dwh, check if that's what you are entering.
I am showing available in both the Reporting and Data Warehouse environments.
The log file says can’t find CMN_DBLINK_SP. I have validated it is there and ran the command as PPM_DWH just like it says in this thread. It says it completes successfully, but I can’t get any results when I use the select count (1) command.
Okay let's see : so if you run this query on the DWH database:
then you have no results? Or do you have any error message? Please note that the first 'niku' is your actual database name, so it can vary if you set up something else.
Received this error:
OLE DB provider “SQLNCLI10 for linked server “PPMDBLINK” returned message “Login timeout”
OLE DB provider “SQLNCLI10 for linked server “PPMDBLINK” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.”
Msg 53, Level 15, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server .
It seems that it's either an issue with the database connection or the link you specified.
What is the exact command you used to create the link? Try to use :
You have to run this statement on ppm_dwh, connected as ppm_dwh. Ensure you specify the correct server name.
Re-running the command is fine, the stored procedure is going to drop and recreate the existing link. Please could you try and then re-run the SQL query above.
I ran the command as you had it, it did not work.
I made sure I used PPM_DWH and had to add the “ppm_dwh.” in front of the command to get it to run.
I am now not getting an error and getting results when I run the select command. However, I am still getting the error from the NSA that it can’t find the CMN_DBLINK_SP stored procedure.
Good. Please could you check what is the exact error in your nsa-ca.log and let me know? It points to a permission issue but you were able to successfully run the SP, that's why we have to check what will be the exact error message in there.
Error message: [SQLServer JDBC Driver][SQLServer] Could not find stored procedure ‘CMN_DBLINK_SP’
I can see the call for it and it is using the right parameters that I just used:
Schema=niku, username=niku, hostname TPADWCSQL001, password=, clarityDBLinkName=CLARITYDBLINK
It should be okay to access it if it worked for you to run it as above... Did you create it as PPMDBLINK or CLARITYDBLINK? The reference should be the same in NSA as the one you used in the command. So if you left it as PPMDBLINK, you should indicate it in NSA as Database link name.
Is Linked Server the only option we have to connect the dwh and Clarity databases? Our company opposes the use of Linked Servers due to performance and security concerns. Are there other supported ways that we can configure Jaspersoft and Clarity without using Linked Server?
Unfortunately this is not possible, because the link will actually help to retrieve information from PPM database to DWH. So without the link neither the OOTB reports or Datawarehouse jobs will actually work. I would recommend you submit the use case to your company so they make an exception. The link should be available only to the DWH user to use.
Hope this helps.
Kind RegardsNika Hadzhikidi
I'm having issues with creating the PPMDBLINK. Within the CSA the data warehouse is showing as available but I get an error when clicking Save.
The SQL executed fine to create the DBLINK, so I'm guessing that isn't the issue.
When executing the select query I recieve an error that the niku user cannot login. The PPM_DWH has all the necessary access rights. Any ideas?
The issue has remained unresolved for me after reading this post.
First of all, if anyone can summarise or if CA publishes a TEC doc, it would be very helpful.
The database link could be created by two ways.
1> From CSA Dataware house Tab
2> Using SQL Server Studio using the stored procedure.
NIKU, NIKU has been used few times while defining the link and while running the SELECT statement.
MS SQL Server database has certain terminology, like server, instance, service, schema, database, owner, user, login, port number, etc.
The fields on screen on CSA - Data warehouse tab does not appear to be consistent with the documentation CA PPM System Administration Guide Connection details on Page 29.
Appreciate if more information is shared by those who uses non-default port number.
Can you please go through Data Warehouse Job Fail , where discussions have been made on below error:
OLE DB provider "SQLNCLI11" for linked server "ppmdblink" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "ppmdblink" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". Msg 2, Level 16, State 1, Line 3 Named Pipes Provider: Could not open a connection to SQL Server .
Would appreciate if you can log a new thread, once a thread is mark as answered there are chances to miss out the thread. We have a documentation site called Docops" CA PPM - CA PPM - 15.3 - CA Technologies Documentation "
See this link has a detail to configure DB link "https://docops.ca.com/ca-ppm/15-3/en/installing-and-upgrading/install-ca-ppm/install-and-configure-the-data-warehouse "
Got resolved as follows:
Using SQL server studio, logged into ppm_dwh database as ppm_dwh user.
Ran following command in SQL Query window:
CMN_DBLINK_SP 'PPMDBLINK', 'VM0001\sqlservice', 'Clarity_143','Clarity','******'
ppmdblink can be any name of the link
VM0001 is the host name where SQL Server runs
sqlservice is the SQL service on which the database was created.
Clarity_143 is the database name
Clarity is the user that logs into the Clarity_143 database with a password ******.
The link got created successfully.
Ran SELECT count(1) FROM PPMDBLINK.Clarity_143.niku.srm_resources.
niku is the schema of Clarity_143 database objects.