Support Article
Report definition fails, cannot find user defined functions
Summary
A new MSSQL Server database installed in the same database server as other Pega environments is encountering issues, where report defintions are not able to run with an unexposed column.
Error Messages
Error:
** An error occured on executing the query for the report definition - There was a problem getting a list: code: 4121 SQLState: S0001 Message: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.pr_read_from_stream", or the name is ambiguous. DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.pr_read_from_stream", or the name is ambiguous. | SQL Code: 4121 | SQL State: S0001
Steps to Reproduce
Attempt to run a report definition with an unexposed column.
Root Cause
The User Defined Functions (UDFs) must be installed into MSSQL Server database - they are not shared between logical databases installed onto the same database server.
Resolution
Perform the following local-change steps:
1. Take a backup of the existing DB.
2: Ensure existing User-defined functions are not present.
DATASCHEMA."PR_READ_INT_FROM_STREAM";
DATASCHEMA."PR_READ_FROM_STREAM";
DATASCHEMA."PR_READ_DECIMAL_FROM_STREAM";
RULESSCHEMA."PR_READ_INT_FROM_STREAM";
RULESSCHEMA."PR_READ_FROM_STREAM";
RULESSCHEMA."PR_READ_DECIMAL_FROM_STREAM";
3: Cross check that CLR is enabled on your database.
If it is not enabled, the following command enables Common Language Runtime (CLR)
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
4: Ensure your database user has deployment privileges.
These privileges are outlined in the Deployment Guide.
5: Map your DB properties
In the scripts folder in your PRPC media configure the setupDatabase.properties file to map it to the PRPC schema where required to install UDF.
pega.jdbc.driver.jar=JDBCDriver
pega.database.type=mssql
pega.jdbc.url=DatabaseConnectionURL
pega.jdbc.username=DeploymentUser
pega.jdbc.password=password
6: Execute the generateudf.bat or sh script:
From the scripts directory, run generateudf.bat --action install
Published May 4, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.