Thanks for replying. A little more info about my setup:
I'm using version 4.0 Final September 2nd, 2013.
The error messages below are displayed in the Errors & Warnings window when the model is validated (the udf is added to the "Table Valued Functions" under the schema in the Relational Model Data treeview:
Error message 1:
Message type: Error
Message text: The Table Valued Function 'System.GetDaySequence' doesn't have a return element defined
Source: System.GetDaySequence
Created on: 26. september 2013 13:57
Corrections/suggestions
Choice 1: Open 'System.GetDaySequence' in its editor and manually correct the problem.
Error message 2:
Message type: Error
Message text: For the database with driver 'SQL Server 2000/2005/2008/2008R2/2012/Express Driver (SqlClient)', the table valued function call 'System.GetDaySequence' has no mapping defined for the return element.
Source: System.GetDaySequence mapping
Created on: 26. september 2013 13:57
Corrections/suggestions
Choice 1: Open 'System.GetDaySequence' in its editor and manually correct the problem.
Sql database version:
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
The udf:
CREATE FUNCTION [dbo].[GetDaySequence]
( @from DATETIME, @to DATETIME )
RETURNS @days TABLE ( [Date] DATETIME, [DayCounter] INT )
WITH SCHEMABINDING
-- test: SELECT * FROM dbo.GetDaySequence('2013-01-01', '2013-01-31')
AS BEGIN
DECLARE @work SMALLDATETIME;
DECLARE @counter INT;
SET @work = DATEADD(dd, DATEPART(dy, @from) -1, CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy,@from)) + '-01-01'));
SET @counter = 1;
WHILE @work <= @to
BEGIN
INSERT INTO @days ([Date], [DayCounter]) VALUES (@work, @counter);
SET @work = DATEADD (dd, 1, @work);
SET @counter = @counter +1;
END
RETURN
END
