May 7, 2013 at 5:45 pm
Hi All
I have a function that works in one database but not another. Both databases are SQL2008 R2 64bit.
The function: (taken from here: )
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);
GO
In both cases the function has been created by me in the dbo schema. I can call and successfully execute the function directly with:
SELECT * FROM [dbo].[SplitStrings] ('ABC;DEF',';')
The syntax for calling the function is:
SELECT t.id, t.[priority], f.item
FROM dbo.t1 AS t
CROSS APPLY dbo.SplitStrings(t.[priority], ';') f
In my test database it works perfectly. but on my server i get:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.
Same function, same user, same test table, same syntax. One works one doesn't... :angry:
Can anybody suggest why it works in one case but not another?
May 7, 2013 at 8:12 pm
Try the proper one:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
😎
_____________
Code for TallyGenerator
May 7, 2013 at 9:43 pm
Found the answer...
I changed the database compatibility from 2000 to 2008 and it now works. :blink:
May 7, 2013 at 9:46 pm
Sergiy (5/7/2013)
Try the proper one:http://www.sqlservercentral.com/articles/Tally+Table/72993/
😎
Thanks for that. I'll have a read. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply