30

I have migrated a database from oracle, and now have a few Scalar-valued Functions.

However, when I call them, I get an error saying:

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

I'm calling it like this:

SELECT dbo.chk_mgr('asdf')

What am I doing wrong?

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Madam Zu Zu
  • 6,437
  • 19
  • 83
  • 129
  • 1
    Can you show us the `CREATE` statement for the function in question? – Joe Stefanelli Sep 01 '11 at 19:36
  • 2
    Also, when you say that you're calling it, how/where are you calling it from? Maybe you're pointing to the wrong DB without realizing it? – Tom H Sep 02 '11 at 02:56
  • I just had this issue. @TomH's comment was the correct answer. The top voted answer was a red herring for me since (like the question says) I was actually using a Scalar-valued function. – Chris Bollinger Mar 28 '16 at 22:36

5 Answers5

28

Are you sure it's not a Table-Valued Function?

The reason I ask:

CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50)) 
RETURNS @mgr_table TABLE (mgr_name VARCHAR(50))
AS
BEGIN 
  INSERT @mgr_table (mgr_name) VALUES ('pointy haired boss') 
  RETURN
END 
GO

SELECT dbo.chk_mgr('asdf')
GO

Result:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function 
or aggregate "dbo.chk_mgr", or the name is ambiguous.

However... 

SELECT * FROM dbo.chk_mgr('asdf') 

mgr_name
------------------
pointy haired boss
8kb
  • 10,956
  • 7
  • 38
  • 50
20

Can do the following 

PRINT dbo.[FunctionName] ( [Parameter/Argument] )

E.g.:

PRINT dbo.StringSplit('77,54')
BJ Patel
  • 6,148
  • 11
  • 47
  • 81
13

That syntax works fine for me:

CREATE FUNCTION dbo.test_func
(@in varchar(20))
RETURNS INT
AS
BEGIN
    RETURN 1
END
GO

SELECT dbo.test_func('blah')

Are you sure that the function exists as a function and under the dbo schema?

Tom H
  • 46,766
  • 14
  • 87
  • 128
2

You are using an inline table value function. Therefore you must use Select * From function. If you want to use select function() you must use a scalar function.

https://msdn.microsoft.com/fr-fr/library/ms186755%28v=sql.120%29.aspx

Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29
-1

Make sure you have the correct database selected. You may have the master database selected if you are trying to run it in a new query window.

Kev
  • 1