Cannot Run UDF 1

[BikeToWork]

I should point out first that I am a relative newbie to SQL Server, with more experience in MS Access. I do not understand why I get a "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.ParseString", or the name is ambiguous." message whenever I try to use a table-value function I created in a query. I am logging into SQL Server using Windows authentication and have already given my login all the server role permissions on the database which contains the function. Even the intellisense works, as the function "ParseString" shows up as one of the choices after I first type dbo. Am I missing something simple here? Any advice is greatly appreciated.

 

[simian336]

Try...

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF', 'P')

See if it is in the results. If not it may not be in your database.

If it is go to Programmability>Functions> and you should see it there.

Simi

 

[SQLBill]

Can you provide the script or the part of the script where you are calling that function? Perhaps there is a typo or other syntax error.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[BikeToWork]

The function shows up in Programmability/Table Valued Functions and has two parameters. It is used for splitting a string with delimiters. The function is:

ALTER function [dbo].[ParseString](@String varchar(500), @Delimiter char(1))
returns table
as


return
(
with Results as
(select 1 as Ordinal,
ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
UNION ALL
select Ordinal+1,
ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
from Results
where len(Remaining) > 0)
select Ordinal,
StringValue
from Results
)
GO

I'm calling the function like: Select dbo.ParseString('abc,def,ghi,jkl', ',')

As I said before, I am a SQL Server newbie but it would seem that if the function shows up in SSMS under programmability and shows up in intellisense, it should work in a query, right?

Thanks for helping with this.

 

[SQLBill]

Yes it should, but you might not have permission to use it. You might try granting execute permissions on it to your login to see if that works.

Also, I didn't need to see the function itself...I asked you for the script where you are calling the function. I wanted to see if there might be a typo in the way you are using/calling the function.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

Your function returns a table. So you should treat it like a table in your SQL statements.

Try SELECT * FROM dbo.ParseString('abc,def,ghi,jkl', ',')





-----------
With business clients like mine, you'd be better off herding cats.

 

[BikeToWork]

Thanks, @philhege. That works like a charm!

 

[philhege]

OK, now it works, do you understand the error that SQL returned?

When you issue "SELECT [something]", SQL expects [something] to be a table field or a scalar-valued function.

-----------
With business clients like mine, you'd be better off herding cats.

 

[BikeToWork]

Yes, I understand now that if a function returns a table, the SQL syntax calling the function must refer to it as a table. Thanks for pointing that out. It makes sense now.