How to get result set of a stored procedure using a temp table while creating .xsd in reportsapplication

Question

I have a stored procedure in SQL Server which runs correctly, I need to use the table returned by it as my data for my report in .rdlc format.

EXEC my_stored_procedure @name, @begindate, @enddate

The stored procedure starts like:

BEGIN
    DECLARE ...some variables..

    IF OBJECT_ID('tempdb..#proptbl') IS NOT NULL
        DROP TABLE #proptbl

END

I am trying to add a dataset using an .xsd to my Report Application in SQL Server Data Tools 2015.

I've created the .rdlc and need to add an .xsd and connect to rdlc.

I added .xsd to the project, added a "table adapter" in it, connected to my database, selected "Use Existing Stored Procedure" as command type, set my procedure in the "select" segment (parameters are not coming, see picture below), check "Fill a datatable" and "Return a datatable". At this point when I click next button, I am get this error:

So there is no data returned. Stacked here, any help would be appreciated. Thanks.

Is there even a need to attempt to drop the temp table? As the temp table is only scoped within the stored proc it won't exist anyway. — Alan Schofield, Jul 12 '19 at 14:59
@AlanSchofield well, just tried to remove those lines, same error persists anyway. — Eray Balkanli, Jul 12 '19 at 15:05

score 0 · Answer 1 · answered Jul 12 '19 at 15:13

0

Found my answer here: TableAdapter Configuration Wizard doesn't like temp tables in SP

Adding this after "AS" keyword at the beginning of the SP helped:

IF 1=0 BEGIN
    SET FMTONLY OFF
END

answered Jul 12 '19 at 15:13

Eray Balkanli

7,752
11
48
82

How to get result set of a stored procedure using a temp table while creating .xsd in reportsapplication

1 Answers1