Added Stored Proc, Not Seeing Columns 1

[MMock]

I am running Crystal Reports in VS2005 as of a few weeks ago so I'm not too skilled yet at troubleshooting problems I am having.

I have a blank report and I set the DataSource and chose a stored procedure. This stored procedure should be expandable so I can indicate which columns I want to appear on my report. I had this working, then I changed the stored procedure and tried to update/refresh it and started having problems. I started all over. So basically what I am looking at now is a blank report and a Field Explorer which is showing me, under Database Fields, my stored proc, but that's it. Where do I go from here?

Thanks.

 

[synapsevampire]

Use Database->Verify database to update Crystal.

You cannot write an SP to change the columns coming into the report, or Crystal will choke when it changes from it's original understanding.

Of course that depends on what you mean by "This stored procedure should be expandable so I can indicate which columns I want to appear on my report."

You're better served to show examples than descriptions of your data and the requirements.

-k

 

[MMock]

Yes, I have done all the updating and verifying I thought was required.

What I think it might be and am trying now, is the last step in my sp is a select * from a temp table. My boss said Crystal probably doesn't know the structure/column names from a temp table by doing a select *. I am changing the * to the explicit column names. He doesn't know Crystal, but that made sense to me. I'll post back if it's resolved.

Thanks. And what I meant by should be expandable is it has no plus sign to expand it into the column names the sp returns.

 

[MMock]

Well that didn't matter. I have a report that works that uses a stored procedure and that procedure selects * from a temp table with no problem. And making the select explicit didn't help...

 

[synapsevampire]

SPs do work, and doing a select from a temp table in the end should ALWAYS work, usless the select doesn't return any columns.

Could be many things. Does the SP return columns if you run it in a query tool?

Also try posting your database and the connectivity being used.

-k

 

[MMock]

My database is SQL Server 2005.

I know I just saw it somewhere, but where do I see what my connectivity is? I think it was ADO, if that's specific enough.

I played around with the sp to no avail. Yes, I have tested it. It return one row because it is a summary report. I had it return only one column and still could not get it to work. If I create a new report and use the wizard and pick the sp, it never expands and shows me my columns. How do I insert a screen shot to show you, or attach a file?

Thanks.

 

[MMock]

UPDATE: I had created a backup copy of this stored procedure when I had a working report. I reverted to that backup and it is working. I am tryng to find out exactly what was added to the stored procedure that broke it (because I do need the additional data). It seems to have something to do with a statement like this:

Update #TmpTable3
SET AvgDaysWithoutComponents = @AvgCompletionDays


That is, I created TmpTable3 with a column called AvgDaysWithoutComponents set to zero. Then I update it later. I calc @AvgCompletionDays like this:

set @AvgCompletionDays =
(
SELECT
avg(CompletionDays)
from #tmptable2
WHERE ([Completion Date] >= @sDateFrom And [Completion Date] < @sDateTo + 1)
AND (IR = 0 AND [OR]= 0 AND Cage = 0 AND Misc = 0 AND IRG = 0 AND ORG = 0)
)
Then try the Update. While the sp runs fine, when I plug it into my Crystal report, it won't show me columns. If I just say something like this:

Update #TmpTable3
SET AvgDaysWithoutComponents = 55

then SELECT * from #TmpTable3, all is well.

Is this bizarre? Or do you see something obviously wrong with my sp code?

Thanks.

 

[MMock]

This issue is resolved. I had to explicitly define my AvgDaysWithoutComponents column AS int when I inserted it into the database table. It must've been defaulting to something that confused Crystal, thereby not allowing Crystal to discover any of the columns returned by the stored proc.

Not so obvious after all!

Thank you!

Marlene

 

[LowellM]

I am encountering something very similar to what you had and have still not found the answer. In my case I brought up a new database server with a like configuration to the old one (Windows 2003 / SQL Server 2005). I moved a database from old server to new server. My DSN has been update to point to the new server. In Crystal I go to Database / Set Data Source and can update the source to the stored procedure on the new server. When I verify the database it sees the parameters that are part of the proc, but none of the fields can be viewed in Object Explorer.

I can create a new report based on a table and the data comes in just fine. Try a new report against a stored procedure and it fails - cannot see the fields in the proc.