A silly issue, but may bug you if you are not a regular sql server udf user.

Whenever a scalar-valued user defined function is invoked just using the name of the function, single part, you will get an error like -

Msg 195, Level 15, State 10, Line 1
'function_name' is not a recognized function name.

To resolve this use at least two part function name (by prefixing it with owner).

example :
(1) Select MyScalarFunction() -- wrong usage
(2) Select dbo.MyScalarFunction() -- correct usage

Important thing to note is that table-valued function, unlike scalar-valued, can be called using single part name.

Tip: to avoid using multi part naming in sql server 2005, you can use synonyms.

[more reading on user defined functions]

No matter how much we try to avoid dynamic sqls in our queries, there are times when we have no other choice than to use it. I have experienced that in general all programmers are not very well aware of the truths and myths related to dynamic sql queries and at times they end up implementing a solution that might not be the best one for their scenario.

I am not going to write about the truths and myths of dynamic sql here, but will point you to this article "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog, which I came across long back. The article is very informative and is updated with latest changes in SQL Server. There are other articles from Erland on the same web and they are also equally informative and exhaustive. Also, there are other very useful links on the site that are related to SQL Server. A site - must to be in the 'favorites list' of any MS SQL programmer.