Problem with dynamic sql

7 posts, 0 answers
  1. Dijar
    Dijar avatar
    5 posts
    Member since:
    May 2018

    Posted 22 May Link to this post

    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.

  2. Neli
    Admin
    Neli avatar
    190 posts

    Posted 27 May Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Dijar
    Dijar avatar
    5 posts
    Member since:
    May 2018

    Posted 28 May in reply to Neli Link to this post

    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.
  4. Neli
    Admin
    Neli avatar
    190 posts

    Posted 31 May Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

  5. Dijar
    Dijar avatar
    5 posts
    Member since:
    May 2018

    Posted 20 Jun in reply to Neli Link to this post

    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.
  6. Dijar
    Dijar avatar
    5 posts
    Member since:
    May 2018

    Posted 20 Jun in reply to Dijar Link to this post

    It only seems to occur in stored procedures which have dynamic sql string that is executed.
  7. Neli
    Admin
    Neli avatar
    190 posts

    Posted 25 Jun Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top