intermittent connection errors to sql server

[bstn82]

We recently just upgraded from C8.4.1 to C10.2 last month and twice we had a period of about 3 hours where users were not able to run reports. The error messages received was:

RSV-SRV-0063 An error occurred while executing the 'asynchRun_Request' command. RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-121'. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Connection failure (SQLSTATE=08S01, SQLERRORCODE=0)

I reached out to the network and dba team and both says they do not see any error logs and or information that may shed some light on this issue. No action was taken by us to fix the issue, it resolved by it self.

Does anyone has any ideas on what is going on here?

Some configuration notes:
Single server install
Windows Server 2008
SQL Server 2012

[MFGF]

From the looks of the IBM technote below, these kinds of errors seem to be caused by network issues between your Cognos instance and your SQL Server instance.

http://www-01.ibm.com/support/docview.wss?uid=swg21370845

I don't think it indicates you have a problem with either Cognos or SQL Server - just the network connectivity between the two.

Regards,

MF.

[bstn82]

Thank you.

I found the same article and reached out to the network and dba teams... they both said that they do not see any network issues or any information related to the connection errors.

Any other thoughts?

-Jeff

[sir_jeroen]

Go to Cognos Administration and set [Cache Size Limit for the Content Manager Cache] to 0% that will most likely fix it..... Keep us informed.

[pdeneche]

Hi,

Did it solved your problem?
I'm having the same problem ou looking for a solution. tried the link f ibm but nothing changed.
thank you!!!!
Pierre

[aaronvoss]

We're also having this issue with bursted reports.  We're running on 10.2.1, and getting the following error for almost all burst reports.

We get the following error message:

Failure   RSV-SRV-0063 An error occurred while executing the 'asynchRun_Request' command. CCL-BIT-0005 A socket reported a communication error. Software caused connection abort

I tried setting the CM Cache to 0% as recommended above, but that doesn't seem to have made a difference.

Any other gueses?

[MFGF]

We're also having this issue with bursted reports.  We're running on 10.2.1, and getting the following error for almost all burst reports.

We get the following error message:

Failure   RSV-SRV-0063 An error occurred while executing the 'asynchRun_Request' command. CCL-BIT-0005 A socket reported a communication error. Software caused connection abort

I tried setting the CM Cache to 0% as recommended above, but that doesn't seem to have made a difference.

Any other gueses?

You should log this with IBM support. They may have magic that helps you to determine what is causing the issues?

Worth a try! :)

MF.

[Grim]

FP1 for 10.2.1 is out. Install that and see if the issue persists.

[ahmerzaidi]

We have the same issue in our environment.  Restarting the Cognos service seems to fix it.

[Grim]

We have the same issue in our environment.  Restarting the Cognos service seems to fix it.

This could be leading to a memory leak issue or something with the SQL sessions getting dropped without an actual close/end session request and response. Having to restart Cognos for any other reason then a config change is just bad mojo. I would look into this further if not at the very least testing the latest FP.

I'm my experience I've also seen this type of behavior with Java being the root cause as well as DB client/driver version (DB2 specifically). For the java we ended up working with IBM Cognos Development to identify the issue and through their recommendation ended up using a newer Java version to resolve the issue (JRE 1.6.30 or newer [we used 1.6.33] vs the 1.6.12 supported env listed version). As for the latter DB2 issue, we found this ourselves by checking the DB version and comparing it to the client and JDBC driver versions. Updating to the same driver versions (we were 1 or 2 DB2 FP versions behind) resolved the issue in this case.

Just somethings to consider...

[pdeneche]

I often had the following error :

RSV-SRV-0063 An error occurred while executing the 'asynchRun_Request' command. RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-121'. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Connection failure (SQLSTATE=08S01, SQLERRORCODE=0)

But we solved it.
Whe had for a long time, a specific SQL Server job which had to kill old and sleeping Cognos 10 job (queries generated by reports). This job worked well but maybe since the installation of FIX PACK 10.1.1 (a am not sure!!! ) it seems to create some problems : when this job kill a spid, the error 121 occured, sometimes 30, 60 minutes after...
Since I have desactived this job, it works fine, but I can't explain why it has begun tu create  this error.
During the following error, restarting the SQL Server service was the only one solution. restarting cognos service just give us any minutes safe, but less than restarting the sql server service (SQL Server 2008 R2).
If you have any explication... Cognos support doesn't.

[ahmerzaidi]

Have you tried refreshing Report Service Connections?

http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/topic/com.ibm.swg.im.cognos.ug_cra.10.1.0.doc/ug_cra_id6430RefreshReportServiceConnections.html#RefreshReportServiceConnections