Last post Oct 15, 2007 04:48 PM by mr_breaker
Oct 14, 2007 07:24 PM|mr_breaker|LINK
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.
Oct 15, 2007 02:21 AM|skurocks|LINK
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
Oct 15, 2007 04:21 AM|mr_breaker|LINK
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?
Oct 15, 2007 05:41 AM|dhimant|LINK
Can u post the code you've written in the sproc and function?
Oct 15, 2007 03:36 PM|mr_breaker|LINK
Yes, here it is:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[DistanceBetweenTEST]
-- test code
CREATE PROCEDURE [dbo].[md_UserSearchTEST]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- test code
WHERE dbo.md_DistanceBetweenTEST() > 5
-- this returns a value as expected
-- this throws an exception
Msg 4121, Level 16, State 1, Procedure md_UserSearchTEST, Line 10
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.md_DistanceBetweenTEST", or the name is ambiguous.
Oct 15, 2007 04:04 PM|ndinakar|LINK
What exactly are you trying to do? Your function name is DistanceBetweenTEST, but you are calling it with
md_DistanceBetweenTEST? You got it all messed up. Explain what you are trying to do so we can help.
Oct 15, 2007 04:48 PM|mr_breaker|LINK
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.