Stored Procedure not showing fields in Crystal reports

Question

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

Answer 1

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.

Answer 2

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