Frustrating...PLEASE HELP! Format field data type.

[SmileyFace]

I am really frustrated with this. PLEASE HELP! I have a field in my report which is an 'unmapped field'. Now this comes from a view in a SQL database. Here's the problem. I did a refresh on the report after changing something on the view. After that I lost this field coz it was unmapped. So then I had to bring it in again. In doing so, the field gets truncated and doesn't display the data in its entirety. I am using Version 8.5. I read somewhere that if a field is defined as 'Varchar' in the database, Crystal automatically converts it to a 'String'. But before I had refresed the report it was in 'Memo' format. There is a lot of text in there which is not getting displayed as it is in String (255) format. I don't need to use it in a formula anywhere so I don't care if it stays unmapped. How do I get it back to the 'Memo' format it was in??

 

[synapsevampire]

Ask your dba if they changed the data type.

Also including what database you're working with will help.

Once you determine the data type, you may be able to use a SQL Expression to convert it to the equivalent of a memo type datatype in there, and use it in CR.

I've seen several of your posts on this, but it's difficult to help you don't share the actual data type as defined in the database, nor what database and version you're using.

-k

 

[SmileyFace]

Well, I mentioned it is defined as 'Varchar 4000' in my table. The version of SQL is SQL Enterprise Version:8. There must be some way of forcibly converting this 'Varchar 4000' into a 'Memo' field as that was how it was before. If you know the SQL statement for that please advice. Thanks again SynapseVampire for replying. I've noticed you've been replying to a lot of my posts. Excuse me if they sound silly.

 

[Naith]

They don't sound silly at all.

I've actually spent a lot of time looking into this lately, because I've noticed similar behaviour in my own environments. I'm connecting to a Sybase 12 database, but am restricted to native connectivity. If you're using a native connection, it may be in your interest to try switching to ODBC and verifying the impact.

In my instance, it seems that verification of all stored procedure datasources using alphanumeric null values, or data definitions of Varchar would be incorrectly represented by Crystal. If your anomaly was like mine, you'll probably have noticed that the length of your Varchar strings in Crystal are capped at the length of your longest record on your first Verify Database. (e.g. A database verification on a Varchar(255) where the longest record returned in that particular dataset was 122 would result in Crystal representing the datasource as Varchar(122).

I guess I can understand this because Varchars, by definition only use as much space as necessary for the field, and truncate the additional unused length. Chars do not do this. They pad out the difference.

So, I switched the Varchar fields on the database to Char, which 'solved' the problem. Because your field is 4000 in length, I'd be surprised if your DBA didn't throw a tantrum if you ask him to try this solution. But for me, it was the only fix.

In my case, this anomaly occured all of a sudden. Varchars were correctly interpreted on the same datasource until about a week ago, so I'm still investigating the cause of the issue. When - if - I find it, I'll let you know, Smiley.

Naith

 

[SmileyFace]

Thanks Naith. I don't want to change anything on the database formats as it might affect other reports. Is there anyway of replying to a person directly or atleast letting the person know that I have a reply for him or does he just have to log into the previous posts to read replies??

 

[Naith]

We pretty much log in and catch up with the thread when we can, Smiley.

By the way, it's the introduction of dynamic SQL in my sproc which made the problem arise in the first place, in my example. Just mentioned it in case you've started using dynamic SQL yourself.

Naith

 

[SmileyFace]

Oh ok. Well, did you read my other post? Not sure how to view my report through the browser because of that problem. I just selected an already created data source this time, so didn't alter anything except the query but when I run it from the browser it now says 'Error reading records: Sever not yet opened'! Do you know whats going on?? Thanks for your replies.

 

[Naith]

Try to ping your database, or otherwise connect to it by opening a session directly on your database from your client.

You may have connectivity problems between your client and the database, or the database may be down.

Naith