Why newly created functions can not be found by SQL Server?

function, sql-server, t-sql

Solution

This is table-value function. The syntax is

declare @cats nvarchar(1000)
set @cats = 'a|b|c|d|e'

SELECT * from dbo.fncSplit(@Cats, '|')

Problem

I wonder why recently I create functions but SQL Server returns error in calling them, in a case they get added to the functions list of the database. For example for the following function I get such a error: ``` declare @cats nvarchar(1000) set @cats = 'a|b|c|d|e' SELECT dbo.fncSplit(@Cats, '|') ``` Error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fncSplit", or the name is ambiguous. Sample Function: ``` CREATE FUNCTION [dbo].fncSplit ( @RowData NVARCHAR(MAX), @Delimeter NVARCHAR(MAX) ) RETURNS @RtnValue TABLE ( ID INT IDENTITY(1,1), Data NVARCHAR(MAX) ) AS BEGIN DECLARE @Iterator INT SET @Iterator = 1 DECLARE @FoundIndex INT SET @FoundIndex = CHARINDEX(@Delimeter,@RowData) WHILE (@FoundIndex>0) BEGIN INSERT INTO @RtnValue (data) SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1))) SET @RowData = SUBSTRING(@RowData, @FoundIndex + DATALENGTH(@Delimeter) / 2, LEN(@RowData)) SET @Iterator = @Iterator + 1 SET @FoundIndex = CHARINDEX(@Delimeter, @RowData) END INSERT INTO @RtnValue (Data) SELECT Data = LTRIM(RTRIM(@RowData)) RETURN END ``` Any suggestion or solution would be highly appreciated.

Original source