Hi there,
I’ve got a UDF created on a SQL server which I want to use in a universe I’ve created. The problem I’ve got is that I can’t see the function when creating the object.
Any help would be greatly appreciated.
Stuart.
Smithsc (BOB member since 2008-10-08)
The UDF will have to be called in an object, and to view the value returned by the UDF associate a table to it
.
haider 
(BOB member since 2005-07-18)
Tried to call the UDF in an object:
@GetWorkingDays(EndOfLease.eofl_CollectionDate,EndOfLease.eofl_termination)
but keep getting error message
Must declare the variable ‘@GetWorkingDays’
Smithsc (BOB member since 2008-10-08)
Remove the @ prefixed to the function name
Associate a table to see the result
.
haider (BOB member since 2005-07-18)
Sorry for being stupid, but what do you mean by Associate a table to see the result?
Smithsc (BOB member since 2008-10-08)
I meant to see the output of the function, click on the tables button in the object definition (or it might have already been selected)
.
haider (BOB member since 2005-07-18)
Removed the @ from the function and now get an error:
GetWorkingDays not a recognised function.
I’ve attached a doc which shows the user defined function SQL just incase there’s something wrong there.
Thanks for all your help with this.
UDF.doc (61.0 KB)
Smithsc (BOB member since 2008-10-08)
This error is thrown when the specified UDF is not accessible using the connection details given in the universe parameters
Check the connection and see that you are atleast able to call the function first
.
haider (BOB member since 2005-07-18)
Hi Smithsc,
I guess you have created the function in the Database with your ID…
So you need to give the execute access to the User Id which you are using in the Business Object Designer to Create the Universe.
I have used the UDF functions in the Universe & they work properly…
I hope this helps…
Cheers,
Ranjul
ranjul.gupta 
(BOB member since 2008-10-16)
Sorry about this. Which connection should I be checking. I’ve looked at the ODBC connection and the parameters option in the universe and neither mention user defined functions.
Smithsc (BOB member since 2008-10-08)
You will not know the UDF exists or not, by just seeing the universe connection parameters
Go to SQL server Enterprise manager and check the UDF under functions option for the specific database
See that execute permissions have been assigned to the database user (as given in universe connection parameters)
.
haider (BOB member since 2005-07-18)
I’ve checked the userid on the SQL server Enterprise Manager and the EXEC column is ticked for the UDF I require. In fact, I’ve checked all of the users on the Enterprise Manager and they’ve all been ticked.
The login id on the odbc agrees with the login id in the Enterprse Manager and the passwords are both the same.
But still the UDF doesn’t show in the universe… 
Smithsc (BOB member since 2008-10-08)
Hi Smithsc,
The Function won’t show up in the Universe. In the Universe you can only see th functions that are available with BO.
You directly need to use the function in the objects.
First try to run the UDF in the SQL Server with the same User ID you are using in the Designer for the Universe.
Then use the same syntax in the object of the Universe…
I hope this helps…
Cheers,
Ranjul
ranjul.gupta (BOB member since 2008-10-16)
To haider and Ranjul.
Thankyou both very much for your help. I am now able to use the UDF in my universes.
Best Wishes
Stuart.
Smithsc (BOB member since 2008-10-08)