Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Syntax to call a function correct in one databse but not another? (both SQL 2008 R2) Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 5:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
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...

Can anybody suggest why it works in one case but not another?
Post #1450372
Posted Tuesday, May 7, 2013 8:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
Try the proper one:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Post #1450386
Posted Tuesday, May 7, 2013 9:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
Found the answer...

I changed the database compatibility from 2000 to 2008 and it now works.
Post #1450394
Posted Tuesday, May 7, 2013 9:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311


Thanks for that. I'll have a read.
Post #1450395
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse