Cannot find either column "dbo" or the user-defined function

Hi,

I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure:

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

However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0)

Anyone have idea? I can't figure it out.

Make sure that you have deployed the function ins the right DataBase from which you want to access...Some time this happens that the function will be deployed to a different DataBase or to the master DB by mistake.Also make sure that you grant the exeute permission on function to the user with which you currently trying to execute

|||

Thanks for the reply. It's definitely in the right database, I see it when I run

SELECT * FROM INFORMATION_SCHEMA.Routines

I'm pretty sure the permissions are OK, I am able to execute the function in a SQL query, but it's not working when I execute a stored procedure that uses it.

Any other ideas?

|||

Can u post the code you've written in the sproc and function?

|||

Yes, here it is:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoCREATE FUNCTION [dbo].[DistanceBetweenTEST]()RETURNSfloatASBEGIN-- test codereturn 10.2345ENDCREATE PROCEDURE [dbo].[md_UserSearchTEST]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- test codeSELECT1WHERE dbo.md_DistanceBetweenTEST() > 5END-- this returns a value as expectedselect dbo.DistanceBetweenTEST()-- this throws an exceptionexec md_UserSearchTEST/*Msg 4121, Level 16, State 1, Procedure md_UserSearchTEST, Line 10Cannot find either column "dbo" or the user-defined function or aggregate "dbo.md_DistanceBetweenTEST", or the name is ambiguous.*/

|||

What exactly are you trying to do? Your function name is DistanceBetweenTEST, but you are calling it withmd_DistanceBetweenTEST? You got it all messed up. Explain what you are trying to do so we can help.

|||

WOW. I don't know why I didn't see that. That was the problem right there.

I think I've been looking at this for too long. Thanks.