none
SSRS Could not update list of Fields for the query

    Question

  • I have a Stored Procedure dbo.SSRS_Report

    I am trying to get the Data Properties but getting an error.

    "Could not update list of Fields for the query. Verify that you can connect to the Data Source and that your query syntax is Correct"

    SP looks something Like this:

           SELECT  a.Id ,
                    a.idd ,
                    MIN(Trandate) AS TranDate_min ,
                    MAX(Trandate) AS TranDate_max ,
                    ct = COUNT(*)
            INTO    #t1
            FROM    Table1 AS t
                    JOIN Table2 AS a ON ( a.id = t.id )
            WHERE   a.s = 'OPEN'
            GROUP BY a.id ,
                    a.idd
            HAVING  COUNT(*) <= 3
                    AND MAX(Trandate) >= bdateadd(-2,
                                                                 CAST(GETDATE() AS DATE),
                                                                 0)
                    AND MIN(Trandate) >= DATEADD(dd, -30,
                                                  CAST(GETDATE() AS DATE))
            SELECT  *
            INTO    #t2
            FROM    #t1 AS t
            WHERE   NOT EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no NOT IN ( '424631',
                                                                  '426684',
                                                                  '400022',
                                                                  '430154',
                                                                  '438857',
                                                                  '414720',
                                                                  '549104',
                                                                  '540168',
                                                                  '431228 ' ) )
                    AND EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no IS NOT NULL )
            SELECT  a.Id ,
                    a.Name ,
                    a.Prod ,
                    a.Date ,
                    t.no ,
                    t.tdate ,
                    t.Amt
            FROM    table1 AS t
                    JOIN #t2 AS s ON s.Id = t.Id
                    JOIN table2 AS a ON ( a.Id = t.Id )
            WHERE   a.s = 'OPEN'
            ORDER BY a.Id ,
                    t.tdate

    Thursday, August 23, 2012 12:04 AM

Answers

  • Hi Sonic !

    The issue is due you returning multiple datasets from your query. Please remember when using sp's you need to make sure that you only return one dataset i.e; you only have one SELECT in your sp which will then become the final dataset for your SSRS.

    In your sp above you are only returning one dataset. Since you are able to execute your query in SSMS without having any difficulties but i did see some mistakes in there unless you have defined some custom udf in your databse;

    Please try to execute below code;

    SELECT  a.Id ,
                    a.idd ,
                    MIN(Trandate) AS TranDate_min ,
                    MAX(Trandate) AS TranDate_max ,
                    ct = COUNT(*)
            INTO    #t1
            FROM    Table1 AS t
                    JOIN Table2 AS a ON ( a.id = t.id )
            WHERE   a.s = 'OPEN'
            GROUP BY a.id ,
                    a.idd
            HAVING  COUNT(*) <= 3
                    AND MAX(Trandate) >= dateadd(dd, CAST(GETDATE() AS DATE), 0)
                    AND MIN(Trandate) >= DATEADD(dd, -30, CAST(GETDATE() AS DATE))
            SELECT  *
            INTO    #t2
            FROM    #t1 AS t
            WHERE   NOT EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no NOT IN ( '424631',
                                                                  '426684',
                                                                  '400022',
                                                                  '430154',
                                                                  '438857',
                                                                  '414720',
                                                                  '549104',
                                                                  '540168',
                                                                  '431228 ' ) )
                    AND EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no IS NOT NULL )
            SELECT  a.Id ,
                    a.Name ,
                    a.Prod ,
                    a.Date ,
                    t.no ,
                    t.tdate ,
                    t.Amt
            FROM    table1 AS t
                    JOIN #t2 AS s ON s.Id = t.Id
                    JOIN table2 AS a ON ( a.Id = t.Id )
            WHERE   a.s = 'OPEN'
            ORDER BY a.Id ,
                    t.tdate

    Note : I have changed "bdateadd(-2, CAST(GETDATE() AS DATE),  0)" to "dateadd(dd, CAST(GETDATE() AS DATE),  0)"  in your code, not sure if you have created a custom function name "bdateadd". If thats the case make sure you have created it on your production database as well.

    Please let me know if this helps. Hopefully i have answered you correctly.

    Thanks, Hasham Niaz

    Saturday, August 25, 2012 9:48 PM

All replies

  • Hi There

    Thanks for your posting. Did you try to run the SP in SSMS. If it is running there then can you please try change the query type to "Text", and then use the script to execute the Store Procedure:

    SET FMTONLY ON;

    EXEC <Stored Procedure>

    SET FMTONLY OFF;

    If you have any questions please let me know

    Many Thanks

    Syed Qazafi Anjum

    Thursday, August 23, 2012 12:14 AM
  • Syed, Thanks for you response.

    Yes In SSMS it runcs successfully. I also tried the below link - it does not work.

    http://arcanecode.com/2010/07/30/ssrs-quick-tip-an-item-with-the-same-key-has-already-been-added/

    I have also tried to RUN is as TEXT as you have shown and refresh the Fields the error still Persists.

    Any other thoughts on this please

    Thanks much


    • Edited by Sonic.Jessy Thursday, August 23, 2012 12:21 AM
    Thursday, August 23, 2012 12:20 AM
  • Any thoughts?
    Friday, August 24, 2012 4:41 PM
  • Hi Sonic !

    The issue is due you returning multiple datasets from your query. Please remember when using sp's you need to make sure that you only return one dataset i.e; you only have one SELECT in your sp which will then become the final dataset for your SSRS.

    In your sp above you are only returning one dataset. Since you are able to execute your query in SSMS without having any difficulties but i did see some mistakes in there unless you have defined some custom udf in your databse;

    Please try to execute below code;

    SELECT  a.Id ,
                    a.idd ,
                    MIN(Trandate) AS TranDate_min ,
                    MAX(Trandate) AS TranDate_max ,
                    ct = COUNT(*)
            INTO    #t1
            FROM    Table1 AS t
                    JOIN Table2 AS a ON ( a.id = t.id )
            WHERE   a.s = 'OPEN'
            GROUP BY a.id ,
                    a.idd
            HAVING  COUNT(*) <= 3
                    AND MAX(Trandate) >= dateadd(dd, CAST(GETDATE() AS DATE), 0)
                    AND MIN(Trandate) >= DATEADD(dd, -30, CAST(GETDATE() AS DATE))
            SELECT  *
            INTO    #t2
            FROM    #t1 AS t
            WHERE   NOT EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no NOT IN ( '424631',
                                                                  '426684',
                                                                  '400022',
                                                                  '430154',
                                                                  '438857',
                                                                  '414720',
                                                                  '549104',
                                                                  '540168',
                                                                  '431228 ' ) )
                    AND EXISTS ( SELECT '*'
                                 FROM   Table1
                                 WHERE  Id = t.Id
                                        AND no IS NOT NULL )
            SELECT  a.Id ,
                    a.Name ,
                    a.Prod ,
                    a.Date ,
                    t.no ,
                    t.tdate ,
                    t.Amt
            FROM    table1 AS t
                    JOIN #t2 AS s ON s.Id = t.Id
                    JOIN table2 AS a ON ( a.Id = t.Id )
            WHERE   a.s = 'OPEN'
            ORDER BY a.Id ,
                    t.tdate

    Note : I have changed "bdateadd(-2, CAST(GETDATE() AS DATE),  0)" to "dateadd(dd, CAST(GETDATE() AS DATE),  0)"  in your code, not sure if you have created a custom function name "bdateadd". If thats the case make sure you have created it on your production database as well.

    Please let me know if this helps. Hopefully i have answered you correctly.

    Thanks, Hasham Niaz

    Saturday, August 25, 2012 9:48 PM
  • In short - I removed the temp tables and replaced the #temp tables by their creation queries. It Workedd!!!!

    Still cannot figure out what the exact problem was but i feel you might be right that something to do with the function. coz i have created tons of SSRS reports and temp tables never gave me an issue.

    Thankss....

    Tuesday, August 28, 2012 2:55 AM