Question Re: Failed to open the connection: Error Code 17 in Technology Q&A

Failed to open the connection: Error Code 17

Former Member — Wed, 23 Jul 2008 21:42:20 GMT

Here's an odd one.

Environment:

VB.NET Winform

Visual Studio 2008

.NET framework 2.0

Crystal Reports 2008

ODBC connection

I have three reports, living in the same directory. They are essentially identical, except for the grouping. All three run successfully within Crystal reports.

From a report viewer Winform in VS, two of the three run. As I am dealing with a small data set, the reports render quickly. On the third report, There is a long pause when the report viewer form opens, then I finally get an error:

"Failed to open the connection. Details: [Database Vendor Code: 17] Failed to open the connection. CallReportByExtension 58F5C135-7BA3-4215-898A-DADA6C7DAD9C.rpt Details:[Database Vendor Code: 17]

I was initially getting similar errors on all three reports, before changing the datasource location to my ODBC connection. I have double-checked the location, verified the database, etc.

What am I doing wrong? I can provide the VB code if requested, but since the other two reports work properly, I doubt it's code. I do know that I am passing the report name and path correctly to the report viewer form.

As I am about to kick my monitor through the window and become a street denizen, I would greatly appreciate any help that would be offered.

Thanks,

Mike Beckner

Re: Failed to open the connection: Error Code 17

ted_ueda — Wed, 23 Jul 2008 21:50:12 GMT

What type of database? SQL Server?

I'd first recommend looking up error code 17 for you RDBMs system, to get at the root cause. The error message is stating it's getting back error code 17 from the database driver.

Sincerely,

Ted Ueda

Re: Failed to open the connection: Error Code 17

Former Member — Wed, 23 Jul 2008 21:55:02 GMT

Oops, sorry...

YES, SQL Server 2000. I couldn't find any direct reference to a code 17.

However, the same ODBC connection works on the other two reports.

Re: Failed to open the connection: Error Code 17

former_member208657 — Thu, 24 Jul 2008 05:03:00 GMT

Mike,

You mentioned that you are working with "a small data set". I'd like to rule out the scenario where you are actually passing a .NET DataSet to your report at runtime. If you are passing a DataSet then you are not doing the same thing in code as you are through the designer.

If you are simply using a Crystal Report designed with the ODBC database driver then your situation is puzzling. I think it is worth seeing your code to see what could be going wrong. Please break your code down to its simplest form before post so I don't have to go through too much.

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 13:35:07 GMT

Thanks for the reply. This code works...two of the three reports work.

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Public Class frmReportViewer

Dim m_datStartDate As Date

Dim m_datEndDate As Date

Dim m_strReportPath As String

Dim m_strReportName As String

Dim m_strStaticPath As String = "c:\Program Files\CallReportViewer\Reports\"

Dim crConnectionInfo As New ConnectionInfo

*****Omitting property declarations--I assign the report name and path via these properties. *****

Private Sub frmReportViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

LoadReport()

End Sub

Public Sub LoadReport()

Dim crReportDocument As New ReportDocument

Dim crTableLogonInfo As New TableLogOnInfo

Dim crTables As Tables

Dim crTable As Table

Try

With crConnectionInfo

.ServerName = "CallWhereData"

.DatabaseName = "CallWhereData"

.UserID = "MyID"

.Password = "MyPassword"

End With

Me.Text = m_strReportName

crReportDocument.Load(m_strStaticPath & m_strReportPath)

crReportDocument.SetParameterValue("1StartDate", m_datStartDate)

crReportDocument.SetParameterValue("2EndDate", m_datEndDate)

crTables = crReportDocument.Database.Tables

For Each crTable In crTables

crTableLogonInfo = crTable.LogOnInfo

crTableLogonInfo.ConnectionInfo = crConnectionInfo

crTable.ApplyLogOnInfo(crTableLogonInfo)

CrystalReportViewer1.Dock = DockStyle.Fill

CrystalReportViewer1.ReportSource = crReportDocument

Catch ex As Exception

MessageBox.Show(Err.Description)

End Try

End Sub

End Class

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 13:49:20 GMT

Now it's a matter of figuring out the difference between working reports and non working report.

1) Does the report have subreports?

2) Is the report using the same connection properties (E.G.; ODBC / OLE DB, other)? Check and compare the "Set datasource location" for working and none working reports.

3) Does the non working report contain unlinked tables?

4) Is the non working report using stored procedures as opposed to tables for the working report?

5) Does it help if you enable "Verify on first print"? (File | Report Options)

6) More unlikely, but possible; are any database fields off the design pane of the report when you look at the report in the design view?

7) And of course the assumption is that the report works in the CR designer...

Ludek

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 13:57:05 GMT

Thanks for the reply, Ludek.

I'll paste your questions below, and reply in-line.

1) Does the report have subreports? No

2) Is the report using the same connection properties (E.G.; ODBC / OLE DB, other)? Check and compare the "Set datasource location" for working and none working reports. The datasource location is the same.

3) Does the non working report contain unlinked tables? No

4) Is the non working report using stored procedures as opposed to tables for the working report? No. I am using a command.

5) Does it help if you enable "Verify on first print"? (File | Report Options) Will try and advise. I think the answer will be no (see below)

6) More unlikely, but possible; are any database fields off the design pane of the report when you look at the report in the design view? No

7) And of course the assumption is that the report works in the CR designer... Yes

This is a pretty straightforward report, actually. I connect to the database and do a technically simple select...a couple of joins, but no big deal. The query I use is identical to the others. The only difference I can see between the two is the report grouping. But it's not even getting that far. The non-working report is not connecting to the database. I verified this in SQL Profiler.

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 13:59:09 GMT

Do the other reports use command table?

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 14:03:29 GMT

Yes. They are identical except for the grouping.

I dont' know if it matters or not, but I originally connected to the database via OLE DB (ADO). I changed to ODBC (Set Datasource location....) after I designed the reports.

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 14:13:56 GMT

Was the report that does not work created in an earlier version of Crystal Reports than the reports that work? If so, save them as the present version that you are running.

I'm obviously running out of ideas :). But there has got to be a difference between those reports...

Ludek

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 14:20:10 GMT

Everything was created in CR 2008. I agree that there MUST be something different, but I have gone through every setting, every field, every menu item I can find, and just don't see it. Unfortunately I'm the only CR guy here, so I don't have a second set of eyes.

I can't help but think it might be some obscure glitch in the CR object model.

At this point, I'm going to just rename the file, make a copy of one of the working reports, change the grouping, and move on. As much as I'd like to figure out what happened so I can avoid the problem in the future, I need to move on.

I'll leave the question open in case anybody else has had this happen.

Thanks for taking the time to help, Ludek.

Mike

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 14:27:36 GMT

I do feel... empty / unsatisfied also. But as you said, if you can "recreate" the report from an existing report, it may be the best way to proceed. It will be interesting to see if you get the same issue once you have competed the grouping modification... (please don't let it be so).

Keeping my fingers crossed,

Ludek

Re: Failed to open the connection: Error Code 17

ted_ueda — Thu, 24 Jul 2008 14:35:22 GMT

Hello Mike,

I might be late in replying, but here's how I see the issue:

SQL Server Error Code 17 means "SQL Server does not exist or access denied.". I can get this exception by (1) using a Table to which my database logon identity does not have access, or (2) change the ODBC setting to point to a bogus machine name.

So this issue doesn't appear to be a code issue, but something in the report that's triggering the problem.

I'd recommend, if you do revisit the issue, to turn on ODBC tracing in the ODBC manager, to see if you can get any indication of what's going on. I'm thinking this would give more info, since it's connecting to the driver, just not to the RDBMS.

Sincerely,

Ted Ueda

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 14:58:02 GMT

Ted,

I enabled tracing, and ran the report...very interesting, nothing was created. No file. (and I did get the connection error). To make sure i had it set right, I ran it with one of the working reports, and it did create a file.

So it's hanging up before it even opens ODBC.

Re: Failed to open the connection: Error Code 17

ted_ueda — Thu, 24 Jul 2008 15:01:22 GMT

Then you have a very interesting issue...

I'm pretty much out of ideas - I might just do the report rewrite you've indicated earlier.

Sincerely,

Ted Ueda

Re: Failed to open the connection: Error Code 17

Former Member — Thu, 24 Jul 2008 15:04:52 GMT

Yep...I've already done that, and it worked like a charm.

In the mean time, I'll go back to it and see if I can figure out what happened. I cross-posted on a couple of other boards. If I get it figured out, I'll post the solution here.

Thanks for your help, Ted.

Re: Failed to open the connection: Error Code 17

former_member203619 — Thu, 24 Jul 2008 15:37:57 GMT

It's possible that you have a partially corrupt report.

The standard troubleshooting routine that I do in a situation like this is as follows:

1. Make a backup

2. Remove everything from the report so that all there is is a completely blank report - test it - if it works, then it was something on the report. If not, then it's either in a group (If any are left), or in the command itself

3. If it works, then restore from backup, and this time only remove half of the report - test it - if it fails, then remove even more and test again.

4. Basically, keep removing objects from the report until it works - then I would know that the last object removed was the problem.

5. If it fails until I remove the last database object from the report - then I know that it is something to do with the database connection itself.

It's a bit tedious - but it usually works to narrow down the exact problem.

Shawn

Re: Failed to open the connection: Error Code 17

former_member11696 — Fri, 25 Jul 2008 14:57:16 GMT

My turn,

Error code 17 indicates the report is trying to connect to a data source that no longer exists. Which is why it also takes a minute for the ODBC or OLE DB connection to time out. Somewhere in the old report there is a reference to the old connection info.

As you have discovered the only way to clean this up is to re-build the report. At some point a step was missed when setting location to ODBC and left some old DB connection info behind in the report.

Unforetunately there is no way to view the connection info in the RPT file after version 8.5. In 8.5 using notepad and opening the RPT file you can see the connection info. Likely when this report was upgraded to the current version some reference was left behind. Possibly manually modified the SQL statement when it was allowed. To confirm if you select Show SQL Query, if a RESET button is in the window then it indicates at some time you had manually modified the SQL and this may be causing it to try to to connect to the old source.

I any event we'll consider this thread complete and closed.

Final work around: Rebuild the report.