Reporting Data Source is empty - no fields

[Asif]

I am using dynamic query and pivot in MS SQL stored procedure. when i run this stored procedure it returns me result set. In Reporting, when configure the data source it returns me result set on the Preview Data Source Results BUT nothing comes in Data Explorer window.
On the other side if i use SP without Pivot and Dynamic query it works fine.
Following is the SQL SP.
 
ALTER PROC dbo.GetSeminarReportHeaders
    @DiagnosticFormCode VARCHAR(7)
AS 
    BEGIN 

        SELECT DISTINCT
                DiagnosticFormCode ,
                'Section ' + CONVERT(VARCHAR(50), ds.SectionNumber) + ' - '
                + sst.Name AS Section ,
                QuestionNumber ,
                CorrectAnswer
        INTO    #header
        FROM    dbo.DiagnosticQuestions dq
                INNER JOIN dbo.DiagnosticSections ds ON ds.ID = dq.DiagnosticSectionId
                INNER JOIN dbo.SectionSubjectTypes sst ON sst.ID = ds.SectionTypeId
                INNER JOIN dbo.DiagnosticForms df ON df.ID = ds.DiagnosticFormId
         WHERE   DiagnosticFormCode = @DiagnosticFormCode 

        DECLARE @columns NVARCHAR(MAX) ,
            @query NVARCHAR(MAX)

        SELECT  @columns = COALESCE(@Columns + ',', '')
                + QUOTENAME(questionnumber)
        FROM    ( SELECT DISTINCT
                            QuestionNumber
                  FROM      #header
                ) c
        ORDER BY c.QuestionNumber

        SET @columns = REPLACE(@columns, ',[1]', '[1]')

        SET @query = N'Select distinct DiagnosticFormCode,Section,' + @columns
            + '   
FROM   
(SELECT distinct
DiagnosticFormCode,
Section,
QuestionNumber,
CorrectAnswer
FROM #header AS h
) p
PIVOT
(
max([CorrectAnswer] )
FOR QuestionNumber IN
( ' + @columns + ' )
) AS pvt '

        EXEC sp_executesql @query
--EXEC(@query)
        DROP TABLE #header
    END

[Nasko]

Hi,

This is a known limitation related to Microsoft as stated by my colleague here. Try adding the following code:

IF 1=0 BEGIN
    SET FMTONLY OFF
END

at the beginning of your stored procedure which uses temporary tables.

Let me know whether the proposed solution helps.

Regards, Nasko
Telerik