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.
This topic has been locked.
5 replies Latest Post - 2013-02-20T16:34:10Z by DanielWagemann
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 PostsACCEPTED ANSWER
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-15T21:14:18Z in response to SantoshGagaconfirm 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:27Z in response to neniThank you for your reply.
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 PostsACCEPTED ANSWER
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-15T21:56:51Z in response to SantoshGagaThe 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:27Z in response to DanielWagemannThanks Daniel for your reply.
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 PostsACCEPTED ANSWER
Re: Use of '+' as the concatenate operator in report studio in Cognos10.1.12013-02-20T16:34:10Z in response to SantoshGagaThe error message references the ODBC driver, does the Pervasive Control Center use the ODBC protocol to run the SQL?
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.