UDF based field in view is causing report to fail (only since upgrade)

[DiscoStu76]

Hi,

Using .Net 2003 and the integrated CR, I have a report based on a view in SQL Server. The report generates perfectly happily unless I include any field that takes it's value from a user defined function - this causes the 'Failed to open a rowset' error.

For example, the following function (sp_CommentProps) takes a bunch of records and turns them into a descriptive field.....

CREATE FUNCTION dbo.sp_CommentProps
( @tenant INT)
RETURNS nvarchar(255)
AS
BEGIN

DECLARE @props nvarchar(500)
DECLARE @floors nvarchar(500)
DECLARE @allprops nvarchar(500)

DECLARE Cur_Tenants CURSOR
FOR SELECT Property_Name, Floor_Description FROM vw_Comments_By_Property WHERE Comment_ID = @tenant

OPEN Cur_Tenants

SELECT @allprops = ''
FETCH NEXT FROM Cur_Tenants INTO @props, @floors
WHILE (@@Fetch_Status <> -1)
BEGIN
IF NOT @props IS NULL
BEGIN
SELECT @allprops = @allprops + @props + ' - ' + @floors + ', '
END
FETCH NEXT FROM Cur_Tenants INTO @props, @floors
END

CLOSE Cur_Tenants
DEALLOCATE Cur_Tenants

IF LEN(@allprops) > 0
BEGIN
SELECT @allprops = LEFT(@allprops, LEN(@allprops) - 1)
END
IF LEN(@allprops) = 0
BEGIN
SELECT @allprops = 'None specified'
END

RETURN (@allprops)

END

The view I have created based on this is....

SELECT TOP 100 PERCENT dbo.Tenants.Tenant_ID, dbo.Comments.Comment_ID, dbo.Comments.Comments, dbo.Comments.Comments_Date,
dbo.sp_CommentProps(dbo.Comments.Comment_ID) AS Comment_Floor
FROM dbo.Comments INNER JOIN
dbo.Tenants ON dbo.Comments.Tenant_ID = dbo.Tenants.Tenant_ID
ORDER BY dbo.Comments.Comments_Date DESC


It is the 'Comment_Floor' column that kills the report. Anyone got any idea why this might be ? It's only become a problem since I upgraded to .Net 2003, the report worked fine before.

Thanks!