This knowledge article covers the use of repository variables that rely on SQL expressions.
If you try to publish a job and the portal reports back "TDMServiceException: PublishEngine : CreateConnectionFromProfile : Error in fetching connection profile information", it's possible the issue is coming from one of your global variables defined in the Portal. In this case, you will need to review the use of the variable.
Variables that are created in the TDM Portal are global in nature, so a variable defined in the portal will be evaluated for any job that is executed.
If you are using an @sql command in a variable, you will need to make sure that the underlying connection profile is also globally available to users who will publish jobs in the portal.
Release: TDM 4.3, 4.4, 4.5, 4.6, 4.7, 4.8, 4.9, 4.10
Component: TDM Portal
If you run into this error, review your TDMPublish.log that is located in your log folder for the TDM Portal, you should find an error similar to the one below. In this case it's an execsqlcount expression from a variable that caused this issue.
com.ca.tdm.servicecommon.TDMServiceException: resolve_meta_data_item: TDMServiceException: execsqlcount: TDMServiceException: PublishEngine : CreateConnectionFromProfile : Error in fetching connection profile information
or similar like:
com.ca.tdm.servicecommon.TDMServiceException: resolve_meta_data_item: TDMServiceException: sqllist: TDMServiceException: PublishEngine : CreateConnectionFromProfile : Error in fetching connection profile information
Go through each of your variables for a data painter SQL function that showed up in your error.
Once you find the variable(s) that is causing the issue, you will need to address the problem by:
a) Making the connection profile global to all users and jobs.
b) Re-evaluate the variable to see if this is the most appropriate place for it, or if its not used, remove the variable.
Once you have addressed the issue with the variable, you should no longer see the error.
This is by design. Variables are global in nature and need to be evaluated at the start of all jobs in the TDM Portal, so if there is SQL tied to the variable the connection profile for that variable should also be global.