Problem with getting fields from SQL Stored Procedures

  • 3p
    azoccolillo3p 3 months, 1 week ago

    I have a report that connects to SQL Server and runs a stored procedure. Everything worked. I went in and renamed my stored procedure in SQL and then edited the dataset in AR Designer 11 changing to the new procedure name. It took it, but then cleared all the fields. I tried manually typing in all the fields but it’s still not working.

    I even tried making a new dataset from this same stored procedure but the field names won’t come in. I also tried the exec procedure with params as text and while it ran and returned data in the editor, the field names never repopulated.

    All I did was change the stored procedures name!

  • 3p
    azoccolillo3p 3 months, 1 week ago

    I managed to get this to work after allot of opening and closing and deteting datasets and recreating datasets, etc. Definitely buggy.

  • 12p
    Ruchir Agarwal12p 3 months, 1 week ago

    Hello,

    Once you change the name of StoredProcedure in SQL, you just need to modify the dataset in ActiveReport for the new StoredProcedure name, just like you did and doing this ActiveReports should validate the StoredProcedure.
    We tried replicating your scenario but could not reproduce the issue at our end. After modifying the name of StoredProcedure in SQL and we modified the ActiveReports DataSet too, and ActiveReports displayed the field names as desired.
    Is there anything else that we should know in order of replicate the issue at our end?

    Thanks & Regards
    Ruchir Agarwal

  • 3p
    azoccolillo3p 3 months, 1 week ago

    @Ruchir.Agarwal@grapecity.com said:
    Hello,

    Once you change the name of StoredProcedure in SQL, you just need to modify the dataset in ActiveReport for the new StoredProcedure name, just like you did and doing this ActiveReports should validate the StoredProcedure.
    We tried replicating your scenario but could not reproduce the issue at our end. After modifying the name of StoredProcedure in SQL and we modified the ActiveReports DataSet too, and ActiveReports displayed the field names as desired.
    Is there anything else that we should know in order of replicate the issue at our end?

    Thanks & Regards
    Ruchir Agarwal

    I’m using the downloaded ActiveReports 11 with the ActiveReports Report Designer and lately I’m gettiing this issue where if I change the stored procedure and revalidate fields are coming up empty. I’ll try rebooting and clearing temp data but it’s not working as you say it would on my computer.

  • 3p
    azoccolillo3p 3 months, 1 week ago

    Rebooting and clearing temp directories didn’t solve this problem. I end up doing it this way. I run the stored procedure as text with EXEC and hard code some parameters, go under the edit query tool and run it and save. This usually (not always) gets me the field names. I then save and close and open the rdl file in notepad and change the XML for the dataset and reopen. This has worked a few times. Have you tested this with SQL Server stored procedures? That’s when I have problems.

  • 12p
    Ruchir Agarwal12p 3 months, 1 week ago

    Hi,

    We too have tested the issue using SQL Server Stored Procedure. Attached are some screen-shots of the SQL Server Stored Procedure and ActiveReports along with their output.

    Regards
    Ruchir Agarwal

  • 3p
    azoccolillo3p 3 months, 1 week ago

    @Ruchir.Agarwal@grapecity.com said:
    Hi,

    We too have tested the issue using SQL Server Stored Procedure. Attached are some screen-shots of the SQL Server Stored Procedure and ActiveReports along with their output.

    Regards
    Ruchir Agarwal

    Yes, when I use the EXEC sp_myprocedure @patam1, @param2 etc with CommadYtype=Text I can get fields. But, when I change the CommandType to StoredProcedrue and just put the stored procedure name as the Querty value, it clears the fields and doesn’t work. Try changing your stored procedure by adding a new field, then change CommandType to StoredProcedure and Query to “GetstudentnameinOPutputVariable” and see what happens (or doesn’t happen).

  • 12p
    Ruchir Agarwal12p 3 months, 1 week ago

    Hi,

    For getting the fields in report, you need to set report’s “CommandType” to “Text” instead of “StoredProcedure”, just like for Oracle data source.

    Thanks
    Ruchir Agarwal

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.