Troubleshooting Reports-DB Connectivity at Runtime

[Former Member]

We have an ASP.net application serving some reports to the users. Reports are written in CR 2008 Pro and the .Net version is 3.5SP1. The database is SQL Server 2008. (IIS6 + Windows 2K3)

Within this application we have a few pages that access data using ADO.Net and then we have the CR reports.

Each report, at the initialization time, is dynamically re-configured with the correct database connection parameters.

I have that initialization code below. And I should add that the code behaves perfectly on all other installations we have had.

In this particular installation however all reports produce "Database Logon Failed" shown inside the web version of CR report. The pages which do not use CR and have their own data presentation they all work fine. So I cannot blame the database connectivity. Furthermore I have been able to connect to the database using various other tools such as sqlcmd and using the same crendentials. SQL server logon is used.

Are there any troubleshooting steps that we can take to find out details of error that Crystal Reports runtime encounters?

Can we force the CR runtime to produce detailed log of some sort? Are there simpple utilities provided by SAP that we can run to specifically pinpoint the problem?

thanks a lot

jeff

protected override void OnInit(EventArgs e)

{

// set title

SetTitle(Title);

System.Data.SqlClient.SqlConnectionStringBuilder sb = new System.Data.SqlClient.SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

CrystalDecisions.CrystalReports.Engine.ReportDocument reportDoc = ReportSource.ReportDocument;

if (sb.IntegratedSecurity)

{

reportDoc.DataSourceConnections[0].SetConnection(sb.DataSource, sb.InitialCatalog, true);

}

else

{

reportDoc.DataSourceConnections[0].SetConnection(sb.DataSource, sb.InitialCatalog, sb.UserID, sb.Password);

reportDoc.DataSourceConnections[0].SetLogon(sb.UserID, sb.Password);

reportDoc.DataSourceConnections[0].IntegratedSecurity = false;

}

base.OnInit(e);

}

[former_member183750 · Accepted solution]

Hello Jeff:

Nice to see a well though out problem presentation. Wish we'd get more of these ;).

Anyhow, I do not have any utility as such. But I do have a few ideas so let's see what these do for us.

1) Open the report in the CR designer and enable "Verify on First Refresh" and "Verify Stored procedure on First Refresh" (File | Report Options). See if that helps.

2) This next suggestion may sound quirky, but it may be the simplest and quickest way to figure this out. As a test only, install the CR designer on this machine and see if the report will connect to the database there. You can download an eval of CR 2008 from here;

http://www.sap.com/solutions/sapbusinessobjects/sme/freetrials/index.epx

If the designer has problems connecting, you may get better error message to start with. And if so, certainly better way to troubleshoot and resolve the issue.

3) Check that the correct database client is installed. I do understand that other software, etc. can connect, but often CR is sensitive to this. Best way to determine possible differences is to use the Modules utility from here;

https://smpdl.sap-ag.de/~sapidp/012002523100006252802008E/modules.zip

Ludek

[Former Member · Accepted solution]

Ludek,

we're idebted to you reading about the issues and responding so quickly to them.

I will give those avenues a try.

jeff

[Former Member · Accepted solution]

I have some more information. I used ProcMon to see what fails when the report is being shown.

I found that queries on registry entries for SQLNCLI (2005) fail even though the machine had SQLNCLI10 (2008).

I went back to my report and checked the properties on the stored connection in the report. It show as OLE DB(ADO) and the provider is set to SQLNCLI. Of course this was on my machine which was used to author the report.

We have to be taking into account the variables on the client machine. And one of those is that we didn't think we should be locking them down on the version of SQL Server client they use.

Now if you see in my original post, our application uses ADO.Net and has no problem with any version of the provider. We change database connection params on the fly. Is there anyway for us to change the Provider as well, in the report of course?

Can we completely change the connection in the report? Or can we re-author the report to use ADO.Net period?

thanks

jeff

[former_member183750 · Accepted solution]

Is there anyway for us to change the Provider as well, in the report of course?

Can we completely change the connection in the report? Or can we re-author the report to use ADO.Net period?

- you can do all of the above, but you will have to use the RAS SDK. See [this|https://wiki.sdn.sap.com/wiki/display/BOBJ/ReportApplicationServerDatabaseConnectivity] wiki.

For samples using RAS, see [this|https://wiki.sdn.sap.com/wiki/display/BOBJ/NETRASSDK+Samples] wiki for links to the samples.

Developer help file is here;

http://help.sap.com/businessobject/product_guides/boexir31/en/rassdk_net_dg_12_en.chm

Ludek

[Former Member]

in the light of additional information I'd like to mark this as unanswered. thanks