Crystal 10 not returning all columns from a stored procedure (SQL2008)

[Clownkiller]

Hi - I hope there is someone that will be able to help with my odd little situation with SQL2008 and Crystal Reports 10.

I have a stored procedure that returns a whole bunch of columns when run in SQL, and I am happy that it is working fine.

However...when Crystal Reports is set to use this procedure, it is only giving me the one column from the dataset.

The background is this is a customers statement report which was working fine, they then decided they needed to produce the statement using a different set of parameters, which required us to change the underlying procedure. The dataset should remain the same as the temporary table being used to produce the dataset has not been changed, the only difference are the parameters and a bit of code elsewhere in the procedure to change the criteria in the select statements.

When I use the set location in Crystal it brings up the mapping screen and only gives me one available column to map to - despite me unticking the 'Map Type' box. It would appear that Crystal thinks there is literally one column in the dataset.

Confused!!

 

[IanWaterman]

As a test create a new report and use a command which executes the SP. See if that brings back all columns.

The mapping screen only usually appears if a column name or data type has changed and Crystal can not find a match. Are you saying that in left hand pane all the report fields are present and there is only one filed in the right hand results pane?

Ian

 

[Clownkiller]

Hi Ian,

Thanks for your time.

I have tried creating a new report pointing at the stored procedure, and I am only getting the one column.

With regards your question, yes the left hand panel has all the columns in it, the right hand one contains only the one.

However...I have been playing around with it, and if I comment out one section of the SQL (it is calling a secondary procedure) I am able to point Crystal at it anf get all the columns, if I uncomment it, I get the problem I describe.

It seems that Crystal doesn't like the nested procedure for some reason...despite it apparently executing happily in SQL...

Cheers,
Martin.

 

[Clownkiller]

Yay - sorted it!

After a day and a half, its come down to the nested stored procedure having a Begin Transaction and a commit/rollback depending on the results. It would seem that Crystal doesn't like this. And handily for me, the developers only had this in for debugging and it can be removed.

Happy days.