SQL Server doesn't find my function

Question

I use SQL Server 2008 with Borland Delphi in order to develop my applications. Since recently I'm getting a very weird error. I have created several scalar functions that I use in my application, but I'm having a problem with a customer, in his company my software returns the following error when I call my scalar functions:

Cannot find either column “dbo” or the user-defined function or aggregate “dbo.FunctionName”, or the name is ambiguous."

I've already searched a lot, even here, so keep in mind that:

1. The function exists;

2. I'm quering the correct database;

3. There's no typos;

4. Owner schema is dbo;

5. This problem occurs with ALL MY FUNCTIONS;

   And the weirdest...

6. It only happens when I call them from my application, if i run the EXACTLY SAME code at the Query Analyzer using the same user, it will run just fine.

I have this same functions in several other customers, and they don't have any problem. Could it be a SQL Server problem?

Ps: Sorry for my poor English, first question here.

Answer 1

I don't know how QueryAnalyzer calls your functions, but I know this error.
Usually, when you have user-defined functions, you need to prefix the function with the schema name.

So if your function is in schema "dbo", and the name is "fnPadLeft", you need to call the function in code like this:

SELECT 
   id
   ,some_field
   ,dbo.fnPadLeft(some_other_field) 
FROM YOUR_TABLE_NAME

If you call it like this:

SELECT 
   id
   ,some_field
   ,fnPadLeft(some_other_field)  -- lacks dbo.
FROM YOUR_TABLE_NAME

Then you'll get "no such function".
This only happens to scalar functions btw. (you specifically mentioned this), table-valued functions (and all other non-function things) are not affected by this "feature".

It might also be that you have the same functionname in two schemas (also take a look at the functions in the master database). Maybe your "other functions" are table valued functions.