We have around 500 Cognos 10.1.1 report studio reports in which most of the reports use '+' as the concatenatation operator in the calculated columns of the reports. Of late we encontered with a very bizzare problem where in the reports are absolutley working fine for one data source but not for the other. Both the data sources are in the same server (Database: PERVASIVE).
Example: Column expression
FName + ' Adding some text'
where as FName is a character type column in the table.
We could change the report expressions to use concat() function instead of '+', but looking at the volume of the report under consideration, we would rather try to find out any inexpensive solution rather than fixing each of them, also at the same time would like to know as to why this problem happens at the first place.
ERROR: An error occurred while performing operation 'sqlPrepareWithOptions' status='-69'.
ERROR DETAIL: UDA-SQL-0043 The underlying database detected an error during processing the SQL request.PervasiveODBC Client InterfaceLNAPervasiveODBC Engine InterfaceSyntax Error: select ........<rest are removed for being confidential in nature>
Any help will be highly appreciated.
Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Pinned topic Use of '+' as the concatenate operator in report studio in Cognos10.1.1
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-20T16:34:10Z at 2013-02-20T16:34:10Z by DanielWagemann
neni 270002EYYP42 Posts
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-15T21:14:18ZThis is the accepted answer. This is the accepted answer.confirm the follwoing:
1)did the report worked fine when you changed the expression form '+' to Concat() ?.
2)does the report points to two different data sources or you mean to say that the report works in your Development Environment and not in your Production ?
please check the data type of the Fname in the table in both databases(Whether it is same or not).
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-15T21:31:27ZThis is the accepted answer. This is the accepted answer.
- neni 270002EYYP
My answers are
1. Yes, The report worked fine when I used the concat() function.
2. We have had one data source in the server(Pervasive database engine), recently they created one more data source in the same sever with some additional colums to the tables of the new data source to cater the need for the existing application. Eventully the old data source will be made obsolete after all the existing reports are tested for the new data source.
I checked the data type in both the tables and they are the same. I checked at the FM-Model level also.
thank you again!
DanielWagemann 2700058MAJ411 Posts
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-15T21:56:51ZThis is the accepted answer. This is the accepted answer.The way I read this is that you have two databases on a single server, one allows you to do the + to combine two strings and the other does not.
The error you are getting is coming straight from the ODBC driver. If I was faced with this issue, I would do an ODBC trace for the same calculation against both databases.
I would then compare the prepare statements from the one that works to the one that does not and compare the differences.
If you need help deciphering the ODBC traces, please post them and I will have a look. Keep in mind that they will contain the SQL to the database which you have deemed confidential for some reason.
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-18T20:30:27ZThis is the accepted answer. This is the accepted answer.
- DanielWagemann 2700058MAJ
What you said about the databses and servers, those are true statements.
Now when you ask to do an ODBC trace for the same calculation, what do you exactly mean by that? Did you mean, to run the Sequel obtained from the cognos report against both the databases? If Yes, then I have done that in the Pervasive control center and successfully ran the SQL against both the databases without any issue. I got that sequel from the Congos report that ran successfully in Report Studio and changed the datasource names while running against two different databases. So that part is over, If you mean something else, please let me know as to how to do that. Do I need to get hold of the cognos admin guys to get me the ODBC traces from the COGNOS servers? etc.
Appreciate your help!
DanielWagemann 2700058MAJ411 Posts
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-20T16:34:10ZThis is the accepted answer. This is the accepted answer.
- SantoshGaga 270003WCKA
If not or you are unsure, you could repeat the SQL test connecting through the ODBC driver using an ODBCTest or WINSQL utility.
As posted earlier, the ODBC Trace files between the scenarios would help shed some light on the issue.