UCCX 8.5.1 & Crystal Reports

[JAY ALLEN]

We are wanting to use Crystal Reports to generate customer UCCX Reports.  The 1st level of effort was to locate, install and configure the Informix ODBC driver without loosing more hair.  Once that was done we were able to connect with Crystal reports and assume that all was good... However!  We wanted to leverage the resource table to in our customer report but got weird error messages:

     "Failed to retrieve data from the database. 

       Details: 42000:[informix][informix ODBC Driver][Informix]A syntax error has occurred. [Database Vendor Code: 201]

General searches on the internet we all over the place... so I tried browsing the data with Excel using the Informix ODBC Driver and got the following error when accessing the resoure table in UCCX:

     "[Informix][Informix ODBC Driver]Restricted data type attribute violation.

This message seems to point to a permissions issue, so i checked the privs on the table and uccxhruser has "select" permissions 

Has anyone else run into this issue?  Any and all assistance is appreciated.

j.

[Aaron Harrison]

Hi Jay

I've not had that specific issue, but the way you would normally use Crystal is that you would generate a stored procedure in the DB that would query the various tables and generate a single table that is returned and used in the template.

What query are you using against the DB?

Aaron

[JAY ALLEN]

Aaron,

Many thanks for your response.  After review, i think the response does not focus on my core issue.  Your response assumes that I am wanting to the use Crystal Reports tool to "create a customer report" for use in the Standard Historical reporting tool for UCCX.  This is NOT the case....

What i'm wanting to do is .... use the IBM Informix ODBC driver to access the "tables" of the Informix database.... using the uccxhruser account.   To repeat and clarify... when I attempt to access tables of the db_cra who have select permissions associated w/ uccxhruser most are able to viewed / exported accross the ODBC connector... However when I attempt to access the "resouces" table via the ODBC connect the following error is generated:

     "Failed to retrieve data from the database.

Details: 42000:[informix][informix ODBC Driver][Informix]A syntax error has occurred. [Database Vendor Code: 201]

So i think my core issue or question that needs to be answered is: Why can't i access the "resource" table under db_cra via the ODBC connector?

All answers are greatly appreciated. 

j. allen

[Aaron Harrison]

Hi

So what query are you using? That response indicates a syntax error rather than a permissions one.

I'm able to query those tables on my current 9.x system using ODBC... I don't have an 8.x handy just now.

Aaron

[JAY ALLEN]

Hi,

The Query is:

          SELECT * FROM informix.resource

Nothing fancy at all.  This is the query via Excel and is the same query string used in Crystal reports. 

j.

[Aaron Harrison]

I don't normally bother prefixing the tables - have you tried just 'select * from resource'?

I'm wondering if there is something odd about that table's ownership...

Aaron

[JAY ALLEN]

That was a good test ... however it did not provide any positive results.  What i did notice is that when edited the query in Excel query wizard I was amazed to see that it provided preview of the data.  However the data is not presented when the query is executed

Weird!!!

j

[Aaron Harrison]

Hmm.. have you tried any other tools?

I would suggest trying RazorSQL - it has a free trial mode, and will allow you to connect using built-in JDBC drivers, or through ODBC for comparison...

Aaron

[Ronald Reif]

Hi Jay - Just reviewing this string.  Not sure if you are an enduser or a Cisco partner - but I just wanted to offer our expertise in this field.  We are a 3rd party developer who has interfaced with the UCCX since 3.0 and have created a solution that is easy to create and manage standard and custom reports.  Please let us know if we can assist you.

Thanks, Ron Reif / SMSI / 651-635-2822

[uzuluaga]

Hi Jay,

I'm having the same issue and it looks like it's related to the following bug CSCtl02126

The solution does not really work for me but I'm sure that if you manage to connect to the DB using the JDBC connection or informix driver instead of ODBC and you use "informix"."resource" it'll work. (easyer said than done )

if you use RazorSQL, SquirrelSQL or the IBM SQL studio the query will work just fine but it's probably because you'll be using the informix driver or JDBC connection to the UCCX db.

did you ever get it fixed? if so I would be really interested in knowing how.

http://www.cisco.com/image/gif/paws/112103/ipcc-data-00.pdf

UCCX Historical Reporting Database Access Error on Resource Table

With Cisco UCCX 8.0, you cannot view data from any field within the resource table and no data is returned

from any field. There is no issue adding fields or viewing data from other tables within the database. After

creating a report with only the resource table and when attempting to add any field to the report, this error

message is received:

Failed to open a rowset.

Details:42000[Informix][Informix ODBC Driver][Informix]

A syntax error has occurred.

This issue is documented by Cisco bug ID CSCtl02126 (registered customers only) .

Solution

In UCCX 8.0, the resource table is holding the agent information. The same name resource is also an Informix

key word. Therefore, when querying the resource table, the query statement cannot use the resource as the

alias (Microsoft SQL Query uses the table name as the alias by default). This causes a syntax error in

Informix.

The workaround is to use some other alias in the query statement; for example, use resource1.