Error Number -239

[viRtuAlinsaniTy]

Need help with an error please

I have two tables - Product Master and Transactions

I need to create a report of transactions (Transaction ID, Description and Product) and I want to filter the report based on a picklist of products from the Product Master table. i.e. display a report of all transactions for Product A or Product B etc.

Firstly I create a picklist report - this is from Product Master, and is a list of product ID and description - nothing else. Save It

Then I create a new report and from the Transaction folder, pull in Transaction Id, Description and Product Id.

The report works fine until I apply a filter of:
Transaction.Product ID in (? product Id?) which is a report-type prompt created from the picklist created above.

I run this and fine - when I exit the application, and re-run it first time - cognos errors with:

Error Number -239
DMS-E-GENERAL, A general exception has occured during operation 'Prepare request with options'
DMS-E-RDS_OLEDB, The OLE DB Provider has returned the following error:
HRESULT: E_FAIL
Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed transaction mode
DMS-E-RBI_TABLE, the table or view transaction was not found in the dictionary.

Some investigation work:

- This only happens on one clients data set which means it might be data-related - other clients data sets don't seem to cause this error
- I have tried this both with a relationship between transaction and product master tables and also without
- It doesn't matter what sort of filter i apply, it always falls over
- There were "orphaned" records in transaction table, but i have got rid of these and the error still appears
- When I create the picklist as a list of distinct products in the transaction table (as opposed to products in the product master), this works ok, but this is not what the client wants (
- Have attached and re-attached the tables/views to/from the catalog in question - nothing

Plus Loads more...

Any help would be greatly appreciated
Thanks

 

[viRtuAlinsaniTy]

I should also add that this only happens in Impromptu Administrator - when you publish the reports to the IWR and browse them through Upfront, they work regardless.
Cheers

 

[griffindm]

Not much on the Cognos KB on this.

This was on the DMS-E-RDS_OLEDB error:

"Testing determined that the collation on the SQL tables was causing the problem. The majority of the tables in the data warehouse had their collation set to Latin, and the table the user was attempting to join to was set to Database Default. This also prevented creating the join in SQL. Changing this on the offending table resolved the problem."

Not much else I can think of. Check the collation on SQL Server and make sure both tables are the same.

Dave Griffin






The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[viRtuAlinsaniTy]

Thanks griffindm - will have a look and getback to you
Cheers

 

[viRtuAlinsaniTy]

sorry if I am being a bit thick, but the tables I am comparing are in the same database - therefore, can they have different collation settings? I was under the impression, with my albeit limited knowledge of SQL, that the collation was set per database not per table.

Anyway, apologies if I am misunderstanding - help on this would be appreciated again
Thanks