If you are unable to create a new account, please email support@bspsoftware.com

 

SQL Server function is not a recognized built-in function name.

Started by MarkV, 12 Mar 2019 11:31:07 AM

Previous topic - Next topic

MarkV

I have a user-defined SQL Server function I've imported into my model (DQM).  I added this function to a model query subject as it's own query item.  When in the screen to edit the query item, the Validate and Test Sample buttons work fine (the query item validates and I see sample data in the Results tab).  However, once I click OK to adding the query item to the query subject, I get the following error:
XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: '<function name>' is not a recognized built-in function name.

I've also tried adding a query item that references the imported function but I always get the same results:  the individual query item validates and returns sample data.  But, the query subject fails with the 'is not recognized' error.

I am able to add the function call in the data source query subject by calling it straight in the query.  However, this creates performance issues because the SQL Server function always runs when pulling any column from this data source query subject.

The function in SQL Server is under the same owner and schema as all the tables that work successfully in the model.

Any idea why the function is not recognized at the query subject level?  Any help is appreciated.  Thanks!

CognosPaul

Out of curiosity, does it still fail if you publish the package? I've seen a few cases where Framework returns an error, but the calculations still work in Cognos.

MarkV

Thanks for the suggestion CognosPaul.  That did the trick.

For completeness...

In addition to publishing the package with the validation error, since we have a layered model (data source layer, business layer and presentation layer), I also had to locate the function in the business layer (as opposed to the data source layer) and then made that available in the package definition (hide component).  Additionally, although the function in the model does show the proper result data type being returned (decimal), the data type of the query item in the presentation layer that references the function is blank, I had to explicitly indicate that it's a Fact and I had to explicitly indicate that the Regular Aggregate property was set to Sum.

With all that set in FWM, the backend SQL being generated looks correct and the call to the function only shows up when using the query item that references the function.

Thanks again CognosPaul for the suggestion!