The frustrating "failed to open connection error"

[Former Member]

I am working on a project to upgrade about 50 Crystal Reports to a new database.

The Reports are selected and generated by a Visual Studio project.Tables reside on SQL server 2005.

So then, the original prject was written in an older version of VS. I have remapped the tables in the reports - some of the field names have changes, etc...

I have not changed any of the VB code - but when it tries to apply connection info to the subreport tables, I get my error. "Failed to Open the Connection" along with error code -2147482892

My ODBC connection works fine, the reports are able to connect as well while editing or refreshing within the report.

I know there are similar threads, but I have not found one specific enough to this, so I would really appreciate it if someone with more experience than me on this can help me out here getting past this error.

This is the code in question (which worked fine in the old version) - The line where the error is produced, where the connection info is applied to CrTable.Location, is in bold:

Public Sub logonReport(ByRef crReportDoc As ReportDocument, _

ByVal strLogon As strLogonValues)

Dim crSubReportDocument As New ReportDocument

Dim crtableLogonInfos As New TableLogOnInfos

Dim crtableLogonInfo As New TableLogOnInfo

Dim crConnectionInfo As New ConnectionInfo

Dim Crtables As Tables

Dim Crtable As Table

Dim crSections As Sections

Dim crSection As Section

Dim crRptObjects As ReportObjects

Dim crRptObject As ReportObject

Dim crSubRptObject As SubreportObject

With crConnectionInfo

.ServerName = strLogon.sServer

.DatabaseName = strLogon.sDatabase

.UserID = strLogon.sUserID

.Password = strLogon.sPassword

End With

Crtables = crReportDoc.Database.Tables

'Loop through all tables in the report and apply the connection

'information for each table.

For Each Crtable In Crtables

crtableLogonInfo = Crtable.LogOnInfo

crtableLogonInfo.ConnectionInfo = crConnectionInfo

Crtable.ApplyLogOnInfo(crtableLogonInfo)

Next

crSections = crReportDoc.ReportDefinition.Sections

For Each crSection In crSections

crRptObjects = crSection.ReportObjects

' Loop through objects to find any subreports

For Each crRptObject In crRptObjects

If crRptObject.Kind = ReportObjectKind.SubreportObject Then

crSubRptObject = CType(crRptObject, SubreportObject)

crSubReportDocument = crSubRptObject.OpenSubreport(crSubRptObject.SubreportName)

' Use logon info to log onto any tables within subreports

Crtables = crSubReportDocument.Database.Tables

For Each Crtable In Crtables

crtableLogonInfo = Crtable.LogOnInfo

crtableLogonInfo.ConnectionInfo = crConnectionInfo

Crtable.ApplyLogOnInfo(crtableLogonInfo)

Crtable.Location = crConnectionInfo.DatabaseName + "." + crConnectionInfo.UserID + "." + Crtable.Name

pProgress.Increment(2)

Next

End If

Next

Next

End Sub

Edited by: RoddyPiper on Apr 15, 2009 12:00 AM

[former_member183750 · Accepted solution]

I don't see what version of CR you are upgrading to and what version of .NET you are using?

Depending on the above, I should be able to suggest troubleshooting steps...

Ludek

[Former Member · Accepted solution]

Hello - I am using CR 2008. I am not sure of the .NET version as I am offsite today - but it is a new install. I think it is 3.5 but have to verity - I am working offsite today. Any troubleshooting steps you can suggest would be great. This has been holding my project up, and I have tried everything I can think of.

[former_member183750 · Accepted solution]

Make sure you have SP 0 for CR 2008:

https://smpdl.sap-ag.de/~sapidp/012002523100007528552008E/cr2008_sp0.zip

If that does not help, the real work starts.

1) First place to start with reports that contain subreports is checking in the CR designer to make sure that both the main and the subreport are using the same database connection. E.g.; make sure that if the main report uses OLE DB, the subreport uses OLE DB. Obvious, but do check that out.

2) I would start with a new report - one table, one field, make sure you can connect this report to the database at runtime.

3) Add a subreport - one table one field. Make sure this connects at runtime

4) If all of the above works, go to the report that is failing, back it up, then save out the subreport.

5) Run the main report without the subreport - does it connect?

5) Run the subreport on it's own as a main report. Does that work?

Ludek

[Former Member · Accepted solution]

OK - here is what I have done so far:

Installed the CR service pack.

Tried to generate a very simple report through VB with no subreports. Fails when trying to export:

Failed to open the connection.

Failed to open the connection.

temp_b52e1630-d81c-41c3-aaf9-931ae9b4b8b9 {3E833466-B4F8-49D4-95E9-73C7CFDBE88C}.rpt

(I am assuming the temp_********* rpt is created when trying to generate the report - I do not see it anywhere).

I then changed the ODBC string to a system dsn just to see if that was the problem. Verified database.

Then ran same test rpt.

Same error message again. This is killing me!

Edited by: RoddyPiper on Apr 15, 2009 7:56 PM

[former_member183750 · Accepted solution]

Interesting. It's killing me too... it really is not that difficult - normally.

Two things to try:

1) Comment out any logon code and let the report prompt for the database logon parameters. Does that work? If yes, then the code is not correct.

2) Download this file:

https://smpdl.sap-ag.de/~sapidp/012002523100006252822008E/net_win_smpl.exe

There a re number of sample apps in the above file. Use vbnet_win_dbengine. Wire in your report and add in your database logon parameters (hard code these) and see if this app will log on.

Ludek

[Former Member · Accepted solution]

Thanks, still having problems - I am taking a look at your sample app.

In the meantime, I rebuilt the project and noticed these errors:

Error 1 Could not determine the dependencies of the COM reference "CRQUERYENGINE". Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)) C-IV Reporting

Error 6 Could not determine the dependencies of the COM reference "VLELib". Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)) C-IV Reporting

I am wondering if this could be the whole problem. There are several different versions of each dll - I've tried using different versions but it doesn't seem to eliminate the error.

What is strange is that I can still run my build.

One other thing - I get the same original error when I try to do a crReport.Export().

Do you thinkmy COM errors may be the problem, and if so, any idea how I clean it up?

As I said previously, the project was written in a prior version of VS and worked fine.

[former_member183750 · Accepted solution]

Error 1 Could not determine the dependencies of the COM reference "CRQUERYENGINE". Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)) C-IV Reporting

this is most certainly bad, bad, bad. I suspect i know what it means, I am not sure it makes sense though, maybe...

Anyhow. My thoughts on htis. The "CRQUERYENGINE" is the crqe.dll. It should be in c:\program files\business objects\businessObjects Enterprise 12 .0\win32_x86 folder. What does not make sense is that presumably this works on your computer in the CR designer, so why would it have problems determining dependencies in an app? Unless it's a web app running under http as opposed to file system (Cassini) while you're developing. To troubleshoot this, I would suggest using Process Monitor. ProcMon can be downloaded from here:

http://technet.microsoft.com/en-ca/sysinternals/bb896645.aspx

Make sure you filter the results for you app process as ProcMon tends to make large files. Once you have a log, go through it and look for any fails / access denieds, etc.

Ludek

[Former Member · Accepted solution]

Thanks for your help so far. I was using process monitor and did notice some "not found" results during registry access.

However, in the meantime I had to move the project to another machine which has more memory, and a fresh install of SQL server. Same error. This new machine has the 2008 1 month free trial version of Crystal on it, but that didn't seem to make any difference.

At any point I try to do a CrReportDoc.export(), I get the failure to connect error. Something strange, though - if I do a CrReportDoc.refresh() it will just hang.

If you can help me understand something - when I am calling the Crystal Report object to do an export, what is actually taking place with the connection - is it using the connection I have established in Visual Basic (the VB can do selects & inserts withing VB to the same SQL server I am trying to connect the Crystal Report to) or is it connecting directly to the database from Crystal Reports itself? Does it need some sort of key set in the registry? Am I supposed to be using a windows authenticated connection or a server authenticated? (i've tried both)

Lastly, should all connection strings be the same (same login & password) I think they are now, but wanted to make sure that isn't a potential issu.

I have a feeling that the issue is something extremely stupid that I should have caught by now.

Anyway, I am going to try process monitor on the new box and see if I am still getting registry errors. Thanks again for your help so far.

[former_member183750 · Accepted solution]

+If you can help me understand something - when I am calling the Crystal Report object to do an export, what is actually taking place with the connection - is it using the connection I have established in Visual Basic (the VB can do selects & inserts withing VB to the same SQL server I am trying to connect the Crystal Report to) or is it connecting directly to the database from Crystal Reports itself? +

- the report will not "piggy back" on an existing connection. You have to cerate a separate connection for the report. There a re a number of threads on this forum with good code for connecting to a database.

Lastly, should all connection strings be the same (same login & password) I think they are now, but wanted to make sure that isn't a potential issu.

- not sure I understand. Same as what? Best way to proceed, will be to download the sample apps from here:

https://smpdl.sap-ag.de/~sapidp/012002523100006252822008E/net_win_smpl.exe

Then use the app vbnet_win_dbengine and use your report in the app. Modify the database logon parameters to match your params. Run the app. If that works, use the export button to export from the viewer. If that works, look at the code in the sample app vbnet_win_export. Add that to the above app. See if that works. If it does, port the code to your app.

Ludek

[Former Member · Accepted solution]

Hello - we figured out the problem a few weeks ago, and as I had suspected, it was something stupid that was staring me in the face. All throughout the project we are using OLE DB objects, but we were using an ODBC connection string in the reports. When we changed the connection string type to reflect OLE DB, the connections magically began to work! I wanted to post this in case I can save anyone else some time and frustration with this problem.

Thanks again for your help.

[former_member183750 · Accepted solution]

Thank you for posting your findings. It is always critical to pass the correct logon parameters. Another thing that often trips up users of Crystal Reports is that the main report is connecting via ODBC and a subreport is connecting via OLEDB or some other connection method. Obviously, if the subreport is not logged on individually, it will not use the main report's connection and thus fail. A good troubleshooting step in these cases is to save out the subreport and run it on it's own.

Ludek