Problem with dynamic sql

[Dijar]

Hello,

I have a stored procedure which has dynamic sql in it with 'EXECUTE (@sql);', and im trying to create a table in report designer but no fields show up when i configure the data source. The query executes fine within the configuration and it has the result that i want but when i finish it there are no fields in the data explorer.

[Neli]

Hi Dijar,

The issue as described is not known for us and we would like to investigate it.
Could you send us more details about the exact scenario, the code of the Stored Procedure and a runnable sample report (e.g with dummy database) so that we can test locally?

Regards,
Neli
Progress Telerik

[Dijar]

The problem appears to be when trying to use cursor variables in sql dynamic script. I have used AdventureWorks2017 database for the code in the attachment below.

[Neli]

Hello Dijar,

To be able to use stored procedures which contain cursors, you have to turn off FMTONLY in a seemingly never executed statement. In this way the data schema can be read by the Data Explorer as each state of IF-ELSE statements is evaluated when the SQL query is run by the SqlDataSource Wizard. The check will be skipped when the same SQL query is used by calling application at run-time.

You may use the approach from How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component KB article and modify the stored procedure as follows:

BEGIN
        SET NOCOUNT ON;
         IF 1=0 BEGIN
           SET FMTONLY OFF
     END
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @Next NVARCHAR(MAX)
    DECLARE db_cursor CURSOR FOR SELECT  DISTINCT Gender From HumanResources.Employee
        . . .

END
GO

Regards,
Neli
Progress Telerik

[Charanpreet]

Thanks a lot

This works well

SET NOCOUNT ON;
IF 1=0 BEGIN
SET FMTONLY OFF
END

[Dijar]

I encountered this problem again in a different stored procedure and i tried turning off FMTONLY in a seemingly never executed statement and also by using memory optimized tables but they didnt fix it. I even tried working around cursor by using loops but im facing the same problem.

[Dijar]

It only seems to occur in stored procedures which have dynamic sql string that is executed.

[Neli]

Hello Dijar,

The Reporting engine uses the specified ADO.NET provider in run-time to connect to the database, execute the query (select command) and return the data to the engine. Hence, if the provider manages to execute the query and return the data, our code should be able to display it successfully.

Please, specify in what way do you want to use the dynamic SQL string? What is the exact scenario?

You might find helpful How to use dynamic SQL string? article in Stack Overflow. 

Regards,
Neli
Progress Telerik

[Charanpreet]

This works
SET NOCOUNT ON;
IF 1=0 BEGIN
SET FMTONLY OFF
END