Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drhoades32
Starting Member
8 Posts |
 Posted - 2008-11-21 : 16:51:32
|
| I'm somewhat new to Crystal and I'm writing reports based on stored procedures which is not the norm here so when I run into problems, I hear "I've never written a Crystal report from a stored procedure." I have 2 questions:1) Does anybody routinely do this?2) I wrote a rather complex stored procedure, created a report and it was working and published on Lawson's dashboard for testing. I changed something and changed the proc name (I don't remember why) and the report will not work now. And I have renamed the proc, created a new report based on the proc and it still won't work. Does anyone have any ideas on what could be the issue. The users that tested it want it bad and I am stuck. Any thoughts/suggestions would be greatly appreciated.drhoades32 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-11-25 : 13:19:55
|
| 1) This is the way I would always write reports in crystal.2) Does the sproc work when you run it in Query Analyser? |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-25 : 15:41:33
|
| 1) This is the way I would always write reports in crystal (too).2) Does the sproc work when you run it in Query Analyser (too)?Personally, I think writing sql statements in Crystal is insane.If it is not practically useful, then it is practically useless. |
|
|
|
drhoades32
Starting Member
8 Posts |
Posted - 2008-11-25 : 17:38:55
|
Thanks for the replies. The proc was working in SQL Server but IF you looked at the Message tab rather than results, there was a data conversion error. But I was getting results so I thought it was fine. I fixed that and then Crystal worked fine. A good lesson but boy, did it take days and several people.And I'm with you--a stored proc is the only way to go! Even if it was painful this past week or so.  drhoades32 |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-11-29 : 09:40:00
|
| OK, I just spotted this one today, so useless to you now. Glad to see it in this 'other' forum section though - didn't know it was here. Glad the report's working nowI always see data conversion problems between the two, eg. dates tend to be 2008/11/01 in SQL and 01/11/2008 in Crystal... and loads more types. I remember seeing that error you describe in messages - as long as you get the results, who cares? From what you said, if you changed the SP name, you did re-import it into the report, didn't you? Otherwise it wouldn't have been recognised. Another related problem I've had is that if you do alter the name or remove such an object - SP, view, function or table, etc, if you're not careful how you reintroduce a replacement, you can sometimes lose all your formatting and have to build it back up again. So remap the fields, or at least save everything before you make the changes... I thought this kind of trouble would be fixed in version 2008, but NO |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-29 : 23:39:51
|
quote:
Originally posted by mikebird
I always see data conversion problems between the two, eg. dates tend to be 2008/11/01 in SQL and 01/11/2008 in Crystal...
Excuse me? Dates are stored in a numeric format in SQL Server. Not as strings.Same goes for Crystal as far as I remember.You are confusing content with display.If it is not practically useful, then it is practically useless. |
|
|
|
drhoades32
Starting Member
8 Posts |
Posted - 2008-12-01 : 09:56:23
|
| Yes, I've found that I had to do dates as text and then convert them in SQL Server.I'm going to avoid changing proc names because I've found that to be horrible, as you have evidently experienced. And yes, I do update the data source when I change the proc--only way to see the changes. Thanks for the replies.drhoades32 |
|
|
|
|
|
|
|
|
SQL Server and Crystal Reports