User-Defined Table Function Error: MSG 4121 1

[Fhwqhgads]

I noticed a couple other people getting this same error, but none of the solutions offered appear to be relevant to my case.

When I make a new table-valued UDF, whether in-line or multi-statement, I always get the following error when trying to use it:

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

Note, I am in the correct database and I can make scalar-valued functions and use them just fine.

Here is an example of a very simple test case:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[testtfunction] (@in smallint)
Returns @test table (twice smallint)
as
Begin
Insert @test
values (2*@in)
Return
End

Another, more complicated one:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[TfA](@TID smallint)
Returns table 
as
Return(
	Select NID As TID
	From Keyfile
	Where AB in (select "AB ID" From Extract where "Unique ID"=@TID))

And a third, multi-statement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[TD](@from smallint, @in smallint)
Returns @TIDDistance Table (AID smallint, Distance float)
As
Begin
declare @TIDs Table (TID smallint)
Insert Into @TIDs 
	Select NID As TID
	From Keyfile
	Where AB in (select "AB ID" From Extract where "Unique ID"=@in)
Insert @TIDDistance 
	Select TID,dbo.Distance(@from,TID)
	From @TIDs Where TID<>@in
Return
End

All three give the same error.

Any help would be appreciated.
-David

 

[SQLDenis]

you don't prefix table fuctions with dbo.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

And for what it's worth it's often better to use joins than IN clauses as IN is syntactically equivalent to a bunch of OR statements. Although perhaps the optimizer can convert it to a join, I don't know. Testing is in order.

IN is an easy way to think about some things, but if you don't know how to make it happen with JOINs then it's probably a good idea to learn.

 

[Fhwqhgads]

Denis,
Thank you for your reply...but I do not understand.
Do you mean you do not prefix with dbo in the create command or when executing or at all?

The references I have seen show prefixing with the owner, which in this case is dbo [I assume]. I am looking at

http://www.samspublishing.com/articles/article.asp?p=31673&seqNum=2&rl=1

and

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=58&rl=1

Lastly, the MSDN site has an example where a table-valued UDF is used, and they show the "dbo." prefix in the execution part of the function.

link:

http://msdn2.microsoft.com/en-us/library/ms175562.aspx

Code from that link:

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

When I try not prefixing with dbo, I get "blah blah is not a built in function.

 

[bborissov]

Are you sure you create the function in YOUR DataBase? Check to see if the function is not created in MASTER DB.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

example

create Function [dbo].[testtfunction] (@in smallint)
Returns @test table (twice smallint)
as
Begin
Insert @test
values (2*@in)
Return
End
GO

these 2 both work (but dbo. is not needed)
select * from testtfunction(1)
select * from dbo.testtfunction(1)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Fhwqhgads]

Ah,
That explains what is going wrong.
I was just blithely assuming that I could say "select table_function (parameter)" to get the table.

I was typing:

select TfA(100)

Using the logic "select can be used to return the value of a function, so why not just return the value of my table."

Thanks!