Skip to Content
-1

Calling an ms sql server function from database in universe idt for BO 4.2 trows an error

May 15 at 02:48 PM

53

avatar image
Former Member

Hello Experts,

I am trying to call a function defined in ms sql database to use in universe.

When I run the function in the ms sql database I get result but when I run in IDT using the SQL Assistant, I get an error " [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fxnavgOdFcy", or the name is ambiguous."

any help will be greatly appreciated.

I am using BO 4.2 SP 4 and IDT

MS SQL 2012

Regards,

Prince Britwum

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Dell Stinnett-Christy May 15 at 04:38 PM
0

This is happening when you validate it in the IDT because you're just calling the function and it's not attached to a table. So the IDT sends something like this to the server:

Select dbo.fxnAvgDepLcy (2017,1,'9205502') from

which fails because there is no table. When you add the dimension/measure to a webi report, it's part of a larger query, so it works.

-Dell

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Dell for your response,

I added the table to the function and i am now getting a new error;

The expression "select dbo.fxnAvgDepLcy (2017,1,'9205502') from DimCustomer_EOD, mydateperiod" contains multiple attributes. This syntax is not supported in this version. (IES 00011)

Please suggest what else I can do to solve this.

Kind Regards,

Prince

0

What is the expected return value from the function?

-Dell

0
Former Member

The function is suppose to return a figure.

I observed somthing,

I created a new universe using only the tables i used for the first one only and it validated but if i try to validate in the original universe i get the same error.

Please find the img attached

not-validated.png (59.0 kB)
validated.png (48.5 kB)
0

Are both universes using the same connection with the same credentials?

I can't see all of the "Select" from either of the images. Are you sure you have the correct commas and did you close the parentheses on the one that isn't working? You might try copying the one that is working and paste it to replace the one that isn't working.

-Dell

0
netsanet kebede
May 15 at 03:20 PM
0

Give us more information on the function.

What type of function do you have?

Is this a table-valued function?

How are you calling the function from IDT?

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Sonet,

The function is a scalar valued function that take three arguments and return a single value,

Example ; dbo.fxnAvgDepLcy (2017,1,'9205502') and its supposed to return a single value.

I using the same function when calling in IDT,

I just realized that the function works in webi but when i use in IDT i get this error

,[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fxnavgOdFcy", or the name is ambiguous.


Kind Regards,

Prince Britwum



0
Former Member

Hi Sonet,

Could you suggest a solution if you have faced a similar issue or how i can work around the issue .

Kind Regards,

Pince

0