ADO 2.5 calling SQL Server stored procedure returning blank data from scalar function

Question

I have a legacy app written in VB6 talking to a SQL Server 2008 database using ADO 2.5. Within it is a report that gets data from a stored procedure. The stored procedure calls a UDF that returns a varchar result as one column of the result set. When it is called from SQL Server Management Studio it correctly populates that column, but when it is called from within the VB6 application it returns an empty value. Are there any compatibility issues that might be causing this?

Here is the UDF:

ALTER FUNCTION dbo.TransferNoteAgreementSummary(@ClientID int, @Year int) 
RETURNS varchar(3000) AS
BEGIN
    DECLARE @ret varchar(3000)
    SET @ret=''

    SELECT @ret = @ret +  
        + CONVERT(varchar, F.NumBins) + ' of ' + B.[Name]+', '+CONVERT(varchar, F.DaysPerWeek)+' lifts, ' + CF.Frequency 
        + ' from ' + CONVERT(varchar, F.StartDate,106)+' to '+CONVERT(varchar, F.EndDate, 106) + dbo.CrLf()
    FROM Accounts A
        JOIN AccountFragments F ON A.AccountID=F.AccountID
        JOIN BinTypes B ON A.BinTypeID=B.BinTypeID
        JOIN ContractFrequencies CF ON F.FrequencyID=CF.FrequencyID
    WHERE A.ClientID=@ClientID AND A.ContractYear=@Year
    ORDER BY B.[Name], A.AccountID, F.StartDate

    RETURN @ret
END

Here is the relevant part of the stored procedure:

ALTER PROC [dbo].[sp_PrintRoundsReport]
AS

          DECLARE @Year int
    DECLARE @Results TABLE([Route] VARCHAR(100), Town VARCHAR(200), ClientID INT, Customer VARCHAR(400), [Address] VARCHAR(MAX),
                                                    [Item Code] VARCHAR(50), Bins INT, [Type] VARCHAR(40), Volume INT, BinDesc VARCHAR(3000), 
                                                    CollectDay VARCHAR(20), DayOrder int)

[ irrelevant lines removed ]                                                    

    SELECT [Route], Town, Customer, [Address], [Item Code], Bins, [Type], Volume, 
        dbo.TransferNoteAgreementSummary(ClientID, @Year) AS BinDesc, CollectDay
    FROM @Results
    ORDER BY DayOrder, [Route], Town, [Address], Volume

And finally, here is the code being called:

  Set cmd = New ADODB.Command
  Set cmd.ActiveConnection = GetConnectionString() 

  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "sp_PrintRoundsReport"

  Dim rs As New Recordset

  Call rs.Open(cmd)

The problem seems to be with the function. If I change it to return a constant string then the value is passes through correctly

Does this ring any bells for anybody?

Answer 1

Don't u are missing?

cmd.Parameters.Add("@RETURN_VALUE")