Failed to retrieve data from the database.DB Vendor Code 156

[Former Member]

Hi,

We recently migrated our scheduled Crystal Reports from BO XI R2 platform to BO XI 3.1, and are having issues with select reports that pull from the same SQL database. The tables and fields exist in the database - and I ran the SQL in a different tool and the syntax is correct. When I try to run an on-demand or schedule one of these reports in 3.1 Infoview I receive the following error:

Failed to retrieve data from the database. Details: [Database Vendor Code: 156 ]

Running:

CR 2008 SP2

BO XI 3.1 SP2 (FP 2.7)

Please advise! Thanks!

[Former Member]

One other key item to note. I opened the report in CR 2008 from the Enterprise folder - and the report refreshes without issue. It's when I try to run on-demand or schedule in Infoview is when I receive the error.

[former_member11696]

Hi LuAnn,

Database vendor error codes are passed from the client to CR/BOE directly. You did not say what DB client you are using so look up that error code on their web site or help file and it should indicate what the problem is.

Typically low level error codes suggest the client is not accessible under the account the server is running under.

Make sure the client \bin folder ( or what ever they use ) is in the System PATH statement on the BOE Server.

Thank you

Don

[Former Member]

Hi Don,

With respect to how the DB client "talks to" the BOE Server is beyond the scope of my knowledge. I will discuss with our BO Admin. However, before I do that I have another question.

1. You suggested I look up the error code on the database clients web site or help - I have not researched error codes. Is there such a site for SQL Server databases?

I would like to provide the BO Admin with as much detail as possible. Thanks!

[former_member11696]

Hi LuAnn,

Its still not clear what DB you are using but if it is MS SQL Server type "SQL Server Error Code 156 :msdn" into the search window and it should filter just for Microsoft MSDN site.

First one I get back indicates it's a security level error, the user, likely the Local System account doesn't have access to the database. Typically the work around is to run the BOE Servers under a User Account that has full Network and DB Admin rights.

Thank you

Don

[Former Member]

I relayed your response to one of the BO Admin's - and here is what he responded with:

not the case eiter I ran the test report with your id and my id would not have access to the db

and your id which connects is not running the bo system I think these are issues when you are using network ids when accessing a db. we are using MS SQL specific ids

[former_member11696]

Hi LuAnn,

Not really clear what he said but I believe this is correct. The BOE servers are typically running under the local System Account. So when it passed the report job from the BOE Server it is the Local System account that the database is getting. We only do single hop for security. Which means if you are logged into InfoView under your local domain account and try to run a report BOE will not, unless specified in the custom database connection options in the CMC, pass your credentials to the DB but will pass the local system account credentials which is likely why these reports are failing.

As A TEST only Ask your Administrator to set up a user account in MS SQL Server that has rights to run all reports. Or when you create the report check on the option to use Trusted Authentication. Then when the reports are published to BOE your log on credentials should get passed to the DB server through BOE.

You'll have to more testing to find a Use account that you can run BOE Servers under and still maintain DB security and report level security that works for you. I also suggest you post your security questions to the BOE Administrator forum to get help configuring all of this and what others are doing.

Thank you

Don

[Former Member]

I forwarded your message on to the BO Admin - haven't received a response yet. Other items to call out are:

I have approximately 100 reports that pull from the same MS SQL Server DB - and they currently update/deilver in Infoview (3.1) without issue.

The report I was testing yesterday (that pulls from the same database) - failed in Infoview (3.1). I started the process of elimination and removed all of the formula fields embedded in the Crystal Report - just leaving the database fields. The report updated/delivered via Infoview without issue. However, it fails when the formula fields are there.

Also, I have another Crystal Report that I had written a command - and that report fails as well. All of these reports ran without issue on the BOE XI R2 platform - not sure what changed from XI R2 to XI 3.1 to cause this issue. It's very frustrating.

Not sure if any of the above will shed some light on the issue but thought I would provide more details.

[former_member11696]

Hi LuAnn,

Actually that is very helpful info. Can you drill into each formula and see what and which one is causing the problem and then Verify it, browse each field reference to confirm the field exists and has not changed.

If it fails in CR Designer it will fail in BOE also. BOE assumes the reports work before publishing.

Likely what has happened is either the field references have changed after the update, could be a difference in DB patches or could be a change in how CR handles the various field types.

Need more info on what formula is causing the problem and what it's trying to do. If you have formula that are not used in the report I suggest removing them first.

Thank you

Don