Cognos Sql Server Error

[UcfMike]

I created a new catalog and set of reports. These reports have been published to the web using Cognos Upfront. When I run the reports from Impromptu, they all work fine. When I run the reports from Cognos Upfront, one set runs fine, while the other set returns the reports with no data. I get the following error:

Query Server Error:
SQLSTATE = 42000,ERROR=536

Error Number -258
DMS-E-GENERAL, A general exception has occurred during operation 'asynchronous open'
DMS-E-RDS_OLEDB, The OlE DB provider has returned the following error:
HREULT: DB_E_ERRORSINCOMMAND Microsoft OLE DB Provider for SQL Server
Invalid length parameter passed to the SUBSTRING function

I believe this is probably a problem with the report, though I can't figure out which column. It could be a problem with the catalog. I have calculated items in the catalog. However, if I pull all of those out of the report, I shouldn't get the error.

With all that said, my question is this. If I had a bad calculated field in the catalog, would it cause this problem even if that field wasn't being pulled into the report?

Thanks,

Michael

 

[mfgf]

Hi,

No, I don't think it will cause you a problem (unless one of the items or joins you are using somehow depends on your faulty catalog calculation).

Regards,

MF.

 

[UcfMike]

I think I found the offending column in the catalog, which was being used in the report. We have a calculated field which has this calculation:

reverse(first-word(reverse(Comments)))

I believe the either reverse or first-word isn't supported with Sql Server, or at the very least the function isn't active.

 

[mfgf]

Hi,

Glad you managed to track it down. Thanks for letting us know the cause.

Best regards,

MF.

 

[blom0344]

reverse is SQL server only..

Perhaps you are looking for:

ltrim(reverse(substring(field,1,(charindex(' ',field)))))

?

Ties Blom