SQLServer and UDFs
UDFs are a cute little things in any database. Just use them in a query and you can loop through the tables and find out without the application bothering about requerying etc.
But in SQLServer, I experienced this glitch. When we created the UDF, it has to be called as owner.functionName unless it is a system defined function.
In our application, we had a generic data access component targetting multiple databases namely three as of now. So this was presenting a piquant situation.
After a deal of small R&D, I found out some of the following workarounds for this:
- Have the application discover the owner and prefix the same. This being similar to a metadata query, will need a roundtrip to the database. May be as an optimization, we can have a Caching other than for the first request.
- After the initial seed data has been run for the database, by the setup scripts, make sure that you also reset all owners to the negotiated user login. You can use the system defined stored procedure
sp_changeobjectowner @objName='GetAppPath', @newowner='lavanya'
It was quite an interesting experience with SQLServer UDFs. But of late, I also came across this URL of having the function behave like System functions:
http://www.winnetmag.com/SQLServer/Articles/ArticleID/15544/pg/2/2.html