cancel
Showing results for 
Search instead for 
Did you mean: 

Intermittent connection error to SQL Server; unable to open connection or incorrect logon parameters

rick_upperpark
Discoverer
0 Kudos

Hello,

I have an application that has intermittent errors connecting to SQL Server database when running our crystal reports.

I have read the many threads discussing similar errors, especially the "incorrect logon parameters". It seems to me the threads are describing a problem with the engine can't connect to the server all the time. Our issue is that the connection is made successfully most of the time, but fails periodically.

The solution is in Visual Studio 2010 using Crystal version 13.0.2000.0.

The users are connecting to one of a number of Windows 2008R2 servers via terminal services and running the application from the server. The SQL Server is 2012 SP3.

Periodically, the users will get an error when running a report. Either "unable to open connection" or "incorrect logon parameters". This does not happen consistently. Sometimes a user will go days or more without an error. Other times they get the error multiple times in a row. Most of the time they rerun the report and it is fine. This is happening with at least 10 or more different crystal reports.

SQL Native Client 11 is installed on all of the terminal server hosts.

Most of the reports have been saved with Crystal Reports Designer 2008. However, we have recreated one report with the latest version of the Designer and have had the error happen with that report, too.

I am dynamically setting the table location/connection information to which ever SQL Server and database the user is currently working with. We have 3 SQL Server and about 18 databases. My hunch is that the report engine is using the default location in the report instead of the connection information provided at runtime. Some of the reports have a SQL Server in its default location that does not exist on the production LAN. I believe these get the "unable to open connection" error. Other reports have a server and database in the production LAN as its default location but a test user. I'm setting the user/password to the production values at runtime. I think these reports get the "incorrect logon parameters" when the error occurs.

In either case, the reports work 90+% of the time. The error is occurring across all of the production database and from all of the terminal server hosts.

Below is the code I used to setup the report at runtime and call to print, preview or export the report.

I do have capture .GLF log files when the error occurs. From what I can see it looks like it is trying to use the proper credentials. I can supply those if desired.

Any help would be greatly appreciated.

Regards,

Rick

                int RetVal = 1;
                cryRpt = new ReportDocument();
                cryRpt.Load(ReportName);
                if (cryRpt.IsLoaded)
                {
                    cryRpt.RecordSelectionFormula = SelectionString;
                    cryRpt.PrintOptions.PrinterName = PrinterToUse;
                    //Set Table Locations
                    CrystalDecisions.Shared.TableLogOnInfo logonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
                    foreach (CrystalDecisions.CrystalReports.Engine.Table table in cryRpt.Database.Tables)
                    {
                        logonInfo = table.LogOnInfo;
                        logonInfo.ConnectionInfo.ServerName = this.Server;
                        logonInfo.ConnectionInfo.DatabaseName = this.Database;
                        logonInfo.ConnectionInfo.UserID = this.User;
                        logonInfo.ConnectionInfo.Password = this.Password;

                        table.ApplyLogOnInfo(logonInfo);
                    }
                    CrystalDecisions.CrystalReports.Engine.ReportDocument subreport;
                    for (int i = 0; i < cryRpt.Subreports.Count; i++)
                    {
                        subreport = cryRpt.Subreports[i];
                        foreach (CrystalDecisions.CrystalReports.Engine.Table table in subreport.Database.Tables)
                        {

                            logonInfo = table.LogOnInfo;
                            logonInfo.ConnectionInfo.ServerName = this.Server;
                            logonInfo.ConnectionInfo.DatabaseName = this.Database;
                            logonInfo.ConnectionInfo.UserID = this.User;
                            logonInfo.ConnectionInfo.Password = this.Password;

                            table.ApplyLogOnInfo(logonInfo);
                        }
                    }
                    this.crystalReportViewer1.ReportSource = cryRpt;
                    this.crystalReportViewer1.ShowGroupTreeButton = false;
                    this.crystalReportViewer1.ShowParameterPanelButton = false;
                    this.crystalReportViewer1.ShowCloseButton = true;
                    this.crystalReportViewer1.ToolPanelView = ToolPanelViewType.None;
                    this.Text = "Report Preview";

                    System.Diagnostics.Trace.WriteLine("ExportFileName: " + ExportFileName);
                    System.Diagnostics.Trace.WriteLine("PrinterName: " + cryRpt.PrintOptions.PrinterName);
                    System.Diagnostics.Trace.WriteLine("NumCopies: " + NumCopies);
                    System.Diagnostics.Trace.WriteLine("Action: " + this.Action);
                    switch (this.Action)
                    {
                        case "EXPORT":
                            cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFileName);
                            break;
                        case "EXPORTLANDSCAPE":
                            cryRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
                            cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFileName);
                            break;
                        case "PREVIEW":
                            Application.Run(this);
                            break;
                        case "PREVIEWLANDSCAPE":
                            cryRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
                            Application.Run(this);
                            break;
                        case "PRINT":
                            System.Diagnostics.Trace.WriteLine("Sending to printer");
                            for (int i = 1; i <= NumCopies; i++)
                            {
                                cryRpt.PrintToPrinter(1, false, 0, 0);
                            }
                            break;
                        case "PRINTLANDSCAPE":
                            cryRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
                            System.Diagnostics.Trace.WriteLine("Sending to landscape printer");
                            for (int i = 1; i <= NumCopies; i++)
                            {
                                cryRpt.PrintToPrinter(1, false, 0, 0);
                            }
                            break;
                    }
                    cryRpt.Close();
                }
                else
                    RetVal = 0;

                return RetVal;

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Rick,

First question what is the exact version of CR for VS you are using? 13.0.2000.0 is the assembly version, it doesn't change, unless you upgrade to SP 21 which now uses 3500 assemblies.

Programs and Feature will tell you the version you are using.

SP 21 is here:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Read the info also, new updates require "fixing" your project.

If for some reason the connection info does fail it can cause an error, could be connection pooling, no more connections available etc. Try adding RPT.TestConnectivity() and if it fails then fix it, report it, log it....

Don

Answers (2)

Answers (2)

0 Kudos

Connection pooling is Server side, no config in CR available.

Also, I am going to assume you manage either a lot of reports or a lot Customers, CR for VS is not design nor is it capable of processing a lot reports at one time.There are other options to manage higher volumes, Ask Customer Service for more info.

Go here:

https://www.sap.com/products/crystal-reports.html

You'll find links to Customer Services team to purchase a single case.

Don

rick_upperpark
Discoverer
0 Kudos

Hi Don,

Thanks for responding.

We are using SP20 right now. I'm going to hold off moving to SP21 because of the requirement of KB2999226. It doesn't look to me like this is installed on our production servers and the IT manager is on vacation this week. I'll migrate to SP21 after he is back.

It looks like TestConnectivity is a method of the table object. So can it sounds like I can set the connection properties and use TestConnectivity to see if a connection can be made, the retry as needed it there is a problem. If the issue is intermittent then this might work around the problem.

I'll give this a test.

Can you give me more information about how connection pooling might be impacting the issue?

When you say "no more connections available" is this from the Crystal perspective, ODBC Driver, SQL Server?

Also, what is the process for starting a paid support ticket, so we can get more direct help with the problem.

Thanks,

Rick