Truncated text in Crystal Reports

[Former Member]

Hi Folks,

I'm running into a strange problem where seemingly random text strings are being truncated by one character. The report renders fine when run within Crystal Reports. But when the RPT file is uploaded to the server, and scheduled/run inside the server, we see the issue. Its only happening to two of our reports. In one report, its the rightmost field, in the other its the group by heading.

Environment:

Authoring reports in Crystal XI

Reports deployed to Business Objects XI Release 2

BO is running on Red Hat Linux

I've received some feedback that this sort of thing can happen with a mis-matched printer driver between the development workstation Crystal is installed on and the machine the BO server is installed on. But I'm doubtful this could be the case when the server is running on Linux.

Anyone see this behavior before?

Thanks,

Rich

[Former Member]

Just noticed this was still open and I wanted to close it.

Basically, BO support said there is no solution because I'm using a database that is not officially supported (postgres). We implemented a work around. Believe it or not, this works: in the SQL the report is based on we append a single space to the field we are selecting. BO still trunctates the field, but it truncates the space!

[Former Member]

This can indeed be the case when client and server use different printers, or the server has no printer configured at all. Have you tried unsetting the printer (File > Page Setup) before exporting the report to Enterprise?

Caroline

[Former Member]

Hi Caroline,

Thanks for taking the time to answer my post.

Yeah, we've already tried what you suggested. Change the report to an unspecified printer. Saved the RPT file. Uploaded it. Ran inside BO. Same dang issue. Frustrating...

Thanks again,

Rich

[Former Member]

I've been working with BO support, and doing research on my own. I've narrowed down a few things, but still no solution. I'm hoping someone on the forums will recognize some of the following symptoms:

the single character truncate is occurring only on fields with a data type of TEXT, or VARCHAR longer than 254 characters). All other fields are fine. The reports work fine in Crystal, and when I export to PDF within Crystal (on a windows desktop). However, when the reports are run in the BO server, I see the truncate. Supposedly, my research shows that putting this in my odbc.ini should solve the issue:

TextAsLongVarchar=1
MaxLongVarcharSize=8190

But its not.

The strange thing is that there is a linux command line tool (called isql) that lets you connect to the database using the ODBC connection. When I do that, I see no truncation. I set up a simple test with two fields: TEXT, and varchar(128):

[bob@yaris bobje]$ isql mavmail
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from bo_test;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+
| id         | text_field                                                                                &a mp;n bsp;                                                                                | varchar_field                                                                                |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+
| 1          | This a string in the text_field                                                                                &a mp;n bsp;                                                            | This is a string in the varchar_field                                                                                |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> 

No truncation on the above, however a crystal report deployed to the BO server truncates the data in text_feild by one character. Also, I'm not sure if the following is part of the same issue or not, but its more information that may trigger something:

The only reports we can get to run are ones based on SQL commands. If we try to build a report using the Database Expert, they run fine within Crystal, but as soon as we try to run them from the Business Objects Server, we get an error saying something like this:

The table 'bo_test' could not be found. Error in File /tmp/{3C71B031-1254-11DE-8AC0-0019B9262D70}.rpt: The table could not be found. 

If I look at the queries Business Objects is trying to run, I see four meta-date queries:

select '' as PROCEDURE_CAT, nspname as PROCEDURE_SCHEM, proname as PROCEDURE_NAME,'' as NUM_INPUT_PARAMS, '' as NUM_OUTPUT_PARAMS, '' as NUM_RESULT_SETS, '' as REMARKS, case when prorettype = 0 then 1::int2 else 2::int2 end as PROCEDURE_TYPE from pg_catalog.pg_namespace, pg_catalog.pg_proc where pg_proc.pronamespace = pg_namespace.oid and nspname like 'staging' and proname like 'public.bo\_test';

 select '' as PROCEDURE_CAT, nspname as PROCEDURE_SCHEM, proname as PROCEDURE_NAME, '' as NUM_INPUT_PARAMS, '' as NUM_OUTPUT_PARAMS, '' as NUM_RESULT_SETS, '' as REMARKS, case when prorettype = 0 then 1::int2 else 2::int2 end as PROCEDURE_TYPE from pg_catalog.pg_namespace, pg_catalog.pg_proc where pg_proc.pronamespace = pg_namespace.oid and nspname like 'staging' and proname like 'public.bo\_test';

SELECT DISTINCT tt.relname, tt.nspname, tt.relkind, COALESCE(d.description,'') from (SELECT c.oid as oid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN  pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v')  and nspname like 'staging' and relname like  'public.bo\_test' and relname !~ '^pg_|^dd_' ) AS tt LEFT JOIN pg_catalog.pg_description d  ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0) order by nspname, relname;

 SELECT DISTINCT tt.relname, tt.nspname, tt.relkind, COALESCE(d.description,'') from  (SELECT c.oid as oid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v')  and nspname like 'staging' and relname like  'public.bo\_test' and relname !~ '^pg_|^dd_' ) AS tt LEFT JOIN pg_catalog.pg_description d  ON (tt.oid = d.objoid AND  tt.tableoid = d.classoid AND d.objsubid = 0) order by nspname, relname;

As you can see from below, none of those queries return data:

staging=# select '' as PROCEDURE_CAT, nspname as PROCEDURE_SCHEM, proname as PROCEDURE_NAME,'' as NUM_INPUT_PARAMS, '' as NUM_OUTPUT_PARAMS, '' as NUM_RESULT_SETS, '' as REMARKS, case when prorettype = 0 then 1::int2 else 2::int2 end as PROCEDURE_TYPE from pg_catalog.pg_namespace, pg_catalog.pg_proc where pg_proc.pronamespace = pg_namespace.oid and nspname like 'staging' and proname like 'public.bo\_test';
 procedure_cat | procedure_schem | procedure_name | num_input_params | num_output_params | num_result_sets | remarks | procedure_type
---------------+-----------------+----------------+------------------+-------------------+-----------------+---------+----------------
(0 rows)

staging=#  select '' as PROCEDURE_CAT, nspname as PROCEDURE_SCHEM, proname as PROCEDURE_NAME, '' as NUM_INPUT_PARAMS, '' as NUM_OUTPUT_PARAMS, '' as NUM_RESULT_SETS, '' as REMARKS, case when prorettype = 0 then 1::int2 else 2::int2 end as PROCEDURE_TYPE from pg_catalog.pg_namespace, pg_catalog.pg_proc where pg_proc.pronamespace = pg_namespace.oid and nspname like 'staging' and proname like 'public.bo\_test';
 procedure_cat | procedure_schem | procedure_name | num_input_params | num_output_params | num_result_sets | remarks | procedure_type
---------------+-----------------+----------------+------------------+-------------------+-----------------+---------+----------------
(0 rows)

staging=# SELECT DISTINCT tt.relname, tt.nspname, tt.relkind, COALESCE(d.description,'') from (SELECT c.oid as oid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN  pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v')  and nspname like 'staging' and relname like  'public.bo\_test' and relname !~ '^pg_|^dd_' ) AS tt LEFT JOIN pg_catalog.pg_description d  ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0) order by nspname, relname;
 relname | nspname | relkind | coalesce
---------+---------+---------+----------
(0 rows)

staging=#  SELECT DISTINCT tt.relname, tt.nspname, tt.relkind, COALESCE(d.description,'') from  (SELECT c.oid as oid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v')  and nspname like 'staging' and relname like  'public.bo\_test' and relname !~ '^pg_|^dd_' ) AS tt LEFT JOIN pg_catalog.pg_description d  ON (tt.oid = d.objoid AND  tt.tableoid = d.classoid AND d.objsubid = 0) order by nspname, relname;
 relname | nspname | relkind | coalesce
---------+---------+---------+----------
(0 rows)