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

Hello i have this query and I havent been able to solve it

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

I have tried with dbo and withou dbo, specifyng columns inside it,

Thanks

select a.nmempresa,a.nmtipocompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,

b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia,

g.DSOPCIONESCALA, g.PESO

from

tgsc_tiposcompetencias a, TGSC_TIPOSCOMPETENCIASxcargo b, tgsc_cargos c, tgsc_ciclos d,

tgsc_competenciasxcargo e, tgsc_competencias f,

(select

dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g

where a.nmempresa=72 and a.nmciclo=9

and b.nmtipocompetencia=a.nmtipocompetencia

and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9

and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9

and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72

and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia

and f.nmtipocompetencia=b.nmtipocompetencia

Is FN_NIVELCOMPETENCIACARGO a table valued function? The select you are using is for scalar functions. If it is a TVF, change

(select

dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g

to

dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia) as g

|||

I changed to this

select a.nmempresa,a.nmtipocompetencia, e.nmcompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,

b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia

from

tgsc_tiposcompetencias a,

TGSC_TIPOSCOMPETENCIASxcargo b,

tgsc_cargos c,

tgsc_ciclos d,

tgsc_competenciasxcargo e, tgsc_competencias f,

dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia) as g

where a.nmempresa=72 and a.nmciclo=9

and b.nmtipocompetencia=a.nmtipocompetencia

and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9

and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9

and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72

and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia

and f.nmtipocompetencia=b.nmtipocompetencia

Now the error is this

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "a.nmempresa" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "a.nmciclo" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "a.nmtipocompetencia" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "c.nmcargo" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "f.nmcompetencia" could not be bound.

|||Is dbo.FN_NIVELCOMPETENCIACARGO a TVF or scalar function?|||TVF|||

If your intent is to execute the TVF for each row returned by your query, then this will work.

Change to

select tvf1.*, t1.* from (

select a.nmempresa,a.nmtipocompetencia, e.nmcompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,

b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia

from

tgsc_tiposcompetencias a,

TGSC_TIPOSCOMPETENCIASxcargo b,

tgsc_cargos c,

tgsc_ciclos d,

tgsc_competenciasxcargo e, tgsc_competencias f

where a.nmempresa=72 and a.nmciclo=9

and b.nmtipocompetencia=a.nmtipocompetencia

and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9

and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9

and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72

and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia

and f.nmtipocompetencia=b.nmtipocompetencia) as t1 CROSS APPLY dbo.FN_NIVELCOMPETENCIACARGO(t1.nmempresa,t1.nmciclo, t1.nmtipocompetencia, t1.nmcargo,t1.nmcompetencia) as tvf1

|||Thank you very much ; I will try and If i am having problems I will come back here.

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.