APS-related SQL/db error

[MVisconte]

We've got our APS running on MS SQL. One site recently updated their reports and noticed in their Win Event Log that they were getting db errors every 5-6 seconds.
The errors are APS "related", but I think that they are more along the lines of a DB problem. I haven't seen the error mentioned on the net, so I'm coming here.

Error details from Event log:
Source: Seagate Info
Category: Database
EventID: 2000
Description: String data, right truncation:
SELECT CI_ID, CI_Name, ... etc FROM CI_INFOOBJECTS WHERE 0=1
ID:351; Type: Report; Name:'Our report name'; Path: '\\servername\si\input\si_f09bu.rpt'; Function: CInfoObject::Update()

Data included in the error is in the form of bytes, and the ascii equivalent is "aps32".

I looked at the table and the record referenced (351) in the error is non-existant. The report object mentioned in the error statement (si_...) doesn't exist, nor do any backwards references to it exist. There are different IDs and report objects mentioned, but always the same errors and results. I looked at the column sizes compared to the data for the report paths, etc., and we're not even 1/2 to filling the columns (255 chars).

We asked the site admin to stop and restart the APS, stop and restart MS SQL, clear his reports cache, etc. We also had the admin convert his APS db back to Access and then convert it back to MS SQL.
There are no changes to the errors or their frequency.

Other reports are published and running correctly.

Has anyone come across this or have an idea for a direction for us to head?

Puzzled and frustrated,
Me

 

[synapsevampire]

Does this file exist?

'\\servername\si\input\si_f09bu.rpt'

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MVisconte]

Hello Synapse,

It's been a busy week, and I've been at home for the last 5 days, so I didn't see your reply... didn't check the right email.

Nope. That file (and others from other error messages) don't exist. But, neither are the references present in the CI_Infoobjects. I "hand-checked" CI_InfoObjects for files listed in each of the error messages, and none of the records referenced exist. It's almost as if the SQL server was running cached SQLs, but I can't find any place that they'd be cached. We had the admin stop and restart SI, and when that didn't help, stop and restart MS SQL2k. And then reboot. Which didn't help.

They finally decided to point the APS back to the MS Access mdb, then blow away the MS SQL APS tables, then re-point to MS SQL and re-build the desktop. Sheesh... there's a) got to be a cause, b) got to be a solution, and c) got to be a better way.

And they just recently dropped out Crystal support contract, because we didn't need it often, and we weren't getting much help when we needed it.

If anybody has any ideas at to the cause and any solutions NOT requiring the gymnastics of re-pointing and re-building, I'd like to hear about 'em... I'm sure this will turn up again.

 

[gsomerville]

Hi MVisconte,

I haven't come across this problem before but have a couple
of comments which hopefully aren't too inane.

From the description:

"SELECT CI_ID, CI_Name, ... etc FROM CI_INFOOBJECTS WHERE 0=1"
is a typical query within a report where the user wants to create a custom SQL query without using a crystal query as the source.

"
ID:351; Type: Report; Name:'Our report name'; Path: '\\servername\si\input\si_f09bu.rpt'; Function: CInfoObject::Update()"

To me, these details imply that someone is trying to save the report 'Our report name' to the APS, which has been assigned the path '\\servername\si\input\si_f09bu.rpt' but for some reason the update cannot take place. (Does the servername in \\servername\si\input\ match the APS server?)

As the selected fields are from the info APS,and the query is customised, it may be possible that someone has developed the report using an "Access specific" query, but the ODBC connection has now be changed to point at the new MS-SQL APS database.

In your position I would start by looking for any failed reports which use the APS database as a datasource, particularly ones with recurring schedules.

Good luck!

 

[MVisconte]

I'll ask the site admin to look into this when I get back to the office.

The reports they are running are the same reports we run locally, only pointed towards their servers. The "0=1" select condition throws me, as it should always return a false.

The reports on the APS seem to run OK, it's these non-existant report objects that seem to be causing the problems. All of the published reports have report objects that exist and can be verified, but I'll ask the SA to re-check.

Thank you.

 

[JonRob]

Hi,

I have been experiencing exactly the same thing with an Access copy of the CINFO database. On Seagates website they have an old article, that points to a patch for an old version of Info to put the process ID of the error in the event log. It looks like you already have this patch or a newer version with it in. The article then suggests that this problem is caused by some corruption within the CINFO database. They say that at this point you should contact their support people so they can talk you through cleaning the corruption.

The company I am at currrently don't have a support contract with Seagate so if you have and call them up please let me know the results.

I have posted my error seperately as a new thread.

 

[MVisconte]

We never got to approach Crystal w/ the problem... the company's sponsor dropped Crystal Support -- we weren't getting their money's worth. We "fixed" the problem by re-building the APS database (first, pointing it to the access db and then rebuilding the SQL db). You might want to try... making a copy of the Access MDB, doing compress and repair on the original, and re-trying the Access mdb. In another group where we used the Access DB, we routinely had to repair the DB.