UCCX 8 - Custom Stored Procedure Not Functioning

[ecornwell]

Hello,

We're trying to modify one of the agent summary reports in UCCX 8.  We want the outbound report to only show numbers that were external calls.  (Longer than 4 digits.)  We copied and modified the existing stored procedure and it seems to run ok until we try to use it in the historical reporter.  HR reports that the stored procedure cannot be found.  We've followed what documentation we could find about adding one but can't get it working.  We've been using ASG Server Studio to access the database.  We opened a TAC case but haven't gotten anywhere.  Here's the detail we gave them.

I have created a stored procedure on the informix DB for UCCX 8. i have

been following the Historical reporting admin guide. the SP runs fine when i use ASG

server studio. when i try to use crystal reports to access the SP i get "Failed to

retrieve data from the database. Details: ADO Error code 0x80004005 Source: Microsoft OLE

DB Provider for ODBC Drivers Description: [Informix][Informix ODBC Driver][Informix]

Routine (sp_agent_call_summar_nointernal) can not be resolved. SQL State:S1000"

if i use a standard procedure Crystal Reports works fine.

i have the uccxHrUserRole assigned to the procedure so i do not understand why i cannot

run the SP.

Any ideas, we really need to get this working.

Thanks!

[ecornwell]

Bump...

[todonghing]

Running in this same problem with a new user-defined function that I wrote.  Please expand on what you mean by both primary and secondary servers.

Thanks,

- Tod

[ecornwell]

We have an HA server pair and the SP was only loaded on one of them.  If you're running HA, make sure you load it to both servers.

[Paulo Pereira]

I believe I found the Service Request in question, and this was the solution provided, for future reference:

Ensure that the new procedure has been uploaded to both primary and secondary nodes.

Just run the following command in the root (In both primary and secondary servers):

Grant execute on sp_agent_call_summar_nointernal to 'uccxHrUserRole'

-Paulo

[rising437]

Hi Paulo,

I'm battlling to achieve a couple of things on UCCX 8.5 and beyond:

1 - How to Execute already existing stored procedure - I have ODBCdriver with the uccxhruser and I ca only execute queries on tables. I cannot see the stored procedures on visual studio database browser. I can see them on SQLDBx tool but I get something like

The routine can not be resolved

2 - How Creating new stored procedures of even adding priveliges to execute existing ones?? I can't find the grant command anywhere!! or even don't know how to upload and create new SPs.

Thanks in advance!!

Best regards,

Rodrigo Boavida

[Aaron Harrison]

Hi

1) There are an impressive number of bugs in early 8.0 versions; on the initial release permissions were incorrect for the existing SPs so the uccxHrUser couldn't run them. You could copy them, so had to copy them and run the copies.. tedious.

If you are on a working version, the other gotcha is that you need to specify them fully-qualified - so sp-test would be  db_cra.dbo.sp_test

Also note that the SPs are randomly split between 'functions' and 'procedures' (there may be a pattern, but I can't work it). Both are similar, but there is a field in the DB that marks a proc as a fucntion or a procedure - the informix Squirrel SQL  plugin doesn't work well with this and just shows the ones marked as 'procedures'. I've had better luck with RazorSQL and have recently played  with the free IBM Data Studio which I think also displayed them all.

2) You can't edit the permissions of existing ones, but you can add new ones and since you (well, uccxHrUser) is the owner you can set permissions on those. Try using RazorSQL or AGS Server Studio (which is better, but $$$) to add new procs.

Aaron

Please ratre helpful posts..