CR 8.5 using SQL Stored Proc - Replace function??

[jolamb42]

All,

I have a report in Crystal 8.5 that displays data from a SQL 2000 stored procedure by passing in parameters to the SQL server. This report has been working fine for several months.

The stored proc passes back Name, Address, etc. to print on the report. This report is mailed by our system users and the Post Office is requesting upper case, no punctuation in this information.

I PREFER to do all of my altering to data within the stored proc versus Crystal. I can do an UPPER() function in my SQL stored proc and get the desired results in my Crystal report.

If I do a REPLACE(SP.Name1, '.', '') AS Name1 in my SQL stored proc, I can run that stored proc perfectly in SQL and get the desired data. It even recognizes the column name as Name1 since I identified it "AS Name1".

However, as soon as I try to do a Verify Database in Crystal, it recognizes the stored proc changes, but no longer sees Name1??? I don't understand why Crystal would have a problem with it when SQL server is doing the work and passing back the data to the report??

Does this sound familiar to anyone? Any ides??

Thanks!

Jolynn

 

[synapsevampire]

It's probably showing up, the problem is that it's now a type Memo, so it doesn't show up in any formula creation areas.

Try casting it:

cast(replace(atsaddrs.chvAddr1, '.', '-----') as varchar(60)) as Name1

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jolamb42]

synapsevampire -

That worked like a charm.... It never crossed my mind that Crystal was now seeing it as a memo field.

Thanks so much for your quick response!

Jolynn