Stored Procedure not showing fields in Crystal reports

12,180

Solution 1

I got it by not accepting the default selection crystal makes in the subreport links on the left side. I had to change it to my own and no tuse the defaults.

That worked. Thanks

Solution 2

HOW TO update the Stored Procedure in Crystal Report after altering the Stored procedure in the database.

This procedure can also be used when you have added a parameter to the Stored Procedure in the database.

  1. Make sure your SP returns a dataset not via another SP inside your SP.

    ex. this will not work: @sql = 'select * from sometable' exec(@sql)

    If this is the case, temporarily add a select statement at the end of your SP

    @sql = 'select column1, column2 from table1' exec(@sql)

    select column1, column2 from table1

  2. In Crystal Report, Fields Explorer, right click your SP and Set DataSource Location. Fill in the connection details to the DB.

    • select your the existing SP and the SP from the new DB connection, then click the Update button.
  3. In CR menu, Database->Verify Database

  4. After Database has been verified, remove the temporary select statement from Step1.

Share:
12,180
aMazing
Author by

aMazing

I am just another software developer writing code...

Updated on June 14, 2022

Comments

  • aMazing
    aMazing over 1 year

    Using: CR in VS2010, SQL Server 2008R2

    I have a SP that returns results when run in SQL Server, but when I add this to a CR it doesnt show any fields in the Field Explorer. But if I try any other SP, it works fine I can see its fields. Steps I am performing:

    1. Open existing CR
    2. Right CLick Database Fields --> Database Expert --> remove the old SP --> Add the new SP
    3. A window with Enter Values pops-up, I set all to NULL and click OOK
    4. The New SP shows up in the Database expert right side
    5. I can see the new SP in the Database Fields Section in the Field Explorer.
    6. However there is no PLUS + Sign next to it. That means it doesnt show its fields.

    Infact the new SP and the old have both have the same number of fields. Just some sql changed.

    Pissed of with this one..Please help.

    Thanks

    • RThomas
      RThomas over 11 years
      Can you show the tsql of the stored procedure. Does it use dynamic sql or other approach that would obscure its results.
  • aMazing
    aMazing over 10 years
    Thanks for your answer. It is highly appreciated.
  • Tahir Raza
    Tahir Raza over 4 years
    This could be a comment, please read how to write a good answer