locked
Error sending reports. Error: Error processing report 'MonthlyRpt'. Error: Too many automatic redirections were attempted. RRS feed

  • Question

  • User-501297529 posted

    I get this error after running a job in SQL Server Agent. I have no idea what it means and how to fix the error. Some of the reports ran but the report in the subject errored out. Does anyone know where to look to fix this error. Is this a SQL Man. Studio error or an error within the job? I looked in the SQL error logs and couldn't find anything.

    I have some code for this. This is part of the Services that I run after the SQL Job. It errors on the MonthlyCardsOrderedRpt. What can I do to fix the code to not cause this error when it get to MonthlyCardsOrderedRpt section of the code? The other reports after that don't print out either so I'm thinking because that is the first report in the code that's the first report it errors out on.

    string ReportName = String.Empty;   //Merely for tracking what report is currently being run so it can be logged with any errors that occur while running it
    
                try
                {
                    //Determine what start & end dates to use for the reports
                    if (Utilities.DateIsBusinessDay(rptRptDate, dtHolidays, UseDynamicHolidays))
                        rptStartDate = ReportingDay.AddDays(-1);
                    else
                        rptStartDate = Utilities.GetFirstPriorBusinessDate(ReportingDay.AddDays(-1), true, dtHolidays, UseDynamicHolidays);
                    rptEndDate = ReportingDay.AddDays(-1);
    
                    if (rptStartDate == rptEndDate)
                        ReportingEmailDays = rptEndDate.ToString("dddd, dd MMMM yyyy");
                    else
                        ReportingEmailDays = rptStartDate.ToString("dddd, dd MMMM yyyy") + " to " + rptEndDate.ToString("dddd, dd MMMM yyyy");
    
                    var re = new ReportExecutionService { UseDefaultCredentials = true, Url = ReportingAPIUrl };
    
                    int iRowCount;  //Used for reports that are *only* sent if there is data
    
                    //Process those reports that are run daily
                    if (ProcessDailyReports)
                    {
                        ReportName = "DailyReloadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", "DailyReloadRpt", "Daily Reload Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "DailyLoadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", "DailyLoadRpt", "Daily Load Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "DailyLoadReloadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", "DailyLoadReloadRpt", "Daily Loads/Reloads", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "DailyReadyLinkRpt";
                        ProcessReport(re, "rptCardLoadReloadReadyLink", "DailyReadyLinkRpt", "Daily Ready Link", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        //Daily Load/Reload Report
                        /* Only sent if there's data for a client, so it: 
                         * - loops through each client
                         * - fires the stored proc for the report directly
                         * - checks if it returns records
                         * - runs the report if there are
                         * */
                        iRowCount = 0;
                        var cmdSubClientLoads = new SqlCommand("dbo.p_rpt_CardLoadReload", conVerfParse)
                        {
                            CommandType = CommandType.StoredProcedure,
                            CommandTimeout = SqlCommandTimeout
                        };
                        foreach (DataRow drSubClient in dtSubClients.Rows)
                        {
                            string SubClientId = drSubClient["SubClientIdentifier"].ToString();
                            iRowCount = GetNumResults_DailyLoadReloadRpt(cmdSubClientLoads, SubClientId, rptStartDate, rptEndDate, iRowCount);
    
                            if (iRowCount > 0)
                            {
                                //Execute/send this report for all sub-clients; only send if there is data.
                                ReportName = "DailyLoadReloadRpt";
                                ProcessReport(re, "rptCardLoadReloadNew", "DailyLoadReloadRpt", "Daily Loads/Reloads", SubClientId, rptStartDate, rptEndDate, ReportingDay);
                            }
                        }
    
                        ReportName = "CheckRequests";
                        ProcessReport(re, "rptCheckRequests", "CheckRequests", "Check Requests", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "DailyPayrollCardRpt";
                        ProcessReport(re, "rptPayroll", "DailyPayrollCardRpt", "Daily Payroll Card Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
    
                    }
    
                    ReportName = ProcessAnnualProgramFeeReport(ReportingDay, re, rptStartDate, rptEndDate);
    
                    //Check for the end (RBC: beginning?) of the month. If it is, run those reports that are done monthly
                    if (ReportingDay.Day == 1)
                    {
                        //Since it's the first of a new month, we need to set the report date range to be for the prior month.
                        rptEndDate = rptRptDate;    //Today - 1 day
                        rptStartDate = DateTime.Parse(rptEndDate.Month + "/01/" + rptEndDate.Year); //First day of last month
    
                        ReportName = "UnfundedPayrollCards";
                        ProcessReport(re, "rptUnfundedPayroll", ReportName, "Unfunded Payroll Cards", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyLoadReloadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", ReportName, "Month End Loads/Reloads Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyReloadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", ReportName, "Month End Reload Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyLoadRpt";
                        ProcessReport(re, "rptCardLoadReloadNew", ReportName, "Month End Load Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyReadyLinkRpt";
                        ProcessReport(re, "rptCardLoadReloadReadyLink", ReportName, "Month End Ready Link Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        //Execute/send these reports for each sub-client; only send if there is data for the card counts
                        var cmdSubClientCardsOrdered = new SqlCommand("dbo.p_rpt_CardCounts", conVerfParse)
                        {
                            CommandType = CommandType.StoredProcedure,
                            CommandTimeout = SqlCommandTimeout
                        };
                        foreach (DataRow drSubClient in dtSubClients.Rows)
                        {
                            var subClientId = drSubClient["SubClientIdentifier"].ToString();
                            iRowCount = GetNumResults_MonthlyReadyLinkRpt(cmdSubClientCardsOrdered, subClientId, rptStartDate, rptEndDate);
    
                            if (iRowCount > 0)
                            {
                                //Execute/send this report for all sub-clients; only send if there is data.
                                ReportName = "MonthlyCardsOrderedRpt";
                                ProcessReport(re, "rptCardsOrdered", ReportName, "Month End Report - Cards Ordered", subClientId, rptStartDate, rptEndDate, ReportingDay);
                                #region New Report - Cards Ordered
                                ReportName = "MonthlyCardsOrderedRevRpt";
                                ProcessReport(re, "rptCardsOrderedRevenue", ReportName, "Month End Report - Cards Ordered Revenue", subClientId, rptStartDate, rptEndDate, ReportingDay);
                                #endregion
                            }
                        }
    
                        ReportName = "MonthlyCardsOrderedRpt";
                        ProcessReport(re, "rptCardsOrdered", ReportName, "Month End Report - Cards Ordered", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyCardsOrderedRevRpt";
                        ProcessReport(re, "rptCardsOrderedRevenue", ReportName, "Month End Report - Cards Ordered Revenue", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        ReportName = "MonthlyPayrollCardRpt";
                        //Run All Clients version
                        ProcessReport(re, "rptPayroll", ReportName, "Month End Payroll Card Report", "-1", rptStartDate, rptEndDate, ReportingDay);
                        //Execute/send this report for each sub-client; only send if there is data.
                        var cmdSubClientPayroll = new SqlCommand("dbo.p_rpt_Payroll", conVerfParse) { CommandType = CommandType.StoredProcedure, CommandTimeout = SqlCommandTimeout };
                        foreach (DataRow drSubClient in dtSubClients.Rows)
                        {
                            var SubClientId = drSubClient["SubClientIdentifier"].ToString();
                            iRowCount = GetNumResults_MonthlyPayrollCardRpt(cmdSubClientPayroll, SubClientId, rptStartDate, rptEndDate);
    
                            if (iRowCount > 0)
                            {
                                ProcessReport(re, "rptPayroll", ReportName, "Month End Payroll Card Report", SubClientId, rptStartDate, rptEndDate, ReportingDay);
                            }
    
                        }
    
                        ReportName = "MonthlyCardLoad";
                        var cmdSubClientCardLoad = new SqlCommand("p_rpt_CardLoad", conVerfParse) { CommandType = CommandType.StoredProcedure, CommandTimeout = SqlCommandTimeout };
                        foreach (DataRow drSubClient in dtSubClients.Rows)
                        {
                            string SubClientId = drSubClient["SubClientIdentifier"].ToString();
                            iRowCount = GetNumResults_MonthlyCardLoadRpt(cmdSubClientCardLoad, SubClientId, rptStartDate, rptEndDate);
    
                            if (iRowCount > 0)
                            {
                                //Execute/send this report for all sub-clients; only send if there is data.
                                ProcessReport(re, "rptCardLoad", ReportName, "Month End Report - Card Load Revenue ", SubClientId, rptStartDate, rptEndDate, ReportingDay);
                            }
    
                        }
                        //Run All Clients version
                        ProcessReport(re, "rptCardLoad", ReportName, "Month End Report - Card Load Revenue ", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        //RBC - This report is disabled in the Configs table as of this writing. Takes LONG time to generate so they run it manually.
                        ReportName = "MonthlyFeeReport";
                        ProcessReport(re, "rptFees", ReportName, "Month End Fee Report", "-1", rptStartDate, rptEndDate, ReportingDay);
    
                        //RBC - New report added 5/2015
                        ReportName = "MonthlyProgramFees";
                        ProcessMonthlyProgramFeesReport(ReportingDay, rptStartDate, rptEndDate, re);
    
                    }  // if (ReportingDay.Day == 1)
    
    
    
                    if (iBadRptGrps > 0)
                    {
                        LogStep("There are " + iBadRptGrps.ToString("#,##0") + " report groups without parents.", true);
    
                        ReportName = "ReportGroupsMissingParents";
                        ProcessReport(re, "rptReportGroupsMissingParents", ReportName, "Report Groups Missing Parents", "-1", rptStartDate, rptEndDate, ReportingDay);
                    }
    
    
                }
                catch (Exception ex)
                {
                    LogStep("Error processing report '" + ReportName + "'.  Error: " + ex.Message, true, false);
                    throw new Exception("Error processing report '" + ReportName + "'.  Error: " + ex.Message);
                }

    Tuesday, September 18, 2018 3:02 PM

Answers

  • User-501297529 posted

    Mikesdotnetting

    You should remove the try catch block and then use the debugger to find out exactly which part of the code is causing the exception.

    I found that this code is what is causing the exception, specifically the line:  using (WebResponse response = request.GetResponse()). 

    This is the error I get on that line

    Error processing report 'MonthlyCardsOrderedRpt'.  Error: System.Net.WebException: Too many automatic redirections were attempted.
       at System.Net.HttpWebRequest.GetResponse()
       at VerfImport.Verfparser.ProcessReport(ReportExecutionService re, String ReportFileName, String ReportName, String ReportTitle, String SubClientId, DateTime rptStartDate, DateTime rptEndDate, DateTime rptRptDate)

    What do I need to correct to not get that exception error.

     WebRequest request = WebRequest.Create(requestUri);
    
                    var reportTimeoutMilliseconds = int.Parse(ConfigurationManager.AppSettings["ReportTimeoutSeconds"]) * 1000;
                    request.Timeout = reportTimeoutMilliseconds;
    //#if DEBUG
    //                request.UseDefaultCredentials = false;
    //                request.Credentials = new System.Net.NetworkCredential("tlode", "faster6", "srobo");
    //#else
                    request.UseDefaultCredentials = true;
    //#endif
    
                    LogStep("Report '" + ReportName + "' for " + SubClientLabel + " - requesting.", false);
                    using (WebResponse response = request.GetResponse())
                    using (Stream readStream = response.GetResponseStream())
                    using (FileStream writeStream = new FileStream(OutputFileName, FileMode.Create))
                    {
                        readStream.ReadTimeout = reportTimeoutMilliseconds;
                        readStream.WriteTimeout = reportTimeoutMilliseconds;
    
                        byte[] readBuffer = new byte[4096];
                        int bytesRead = 0;
                        while ((bytesRead = readStream.Read(readBuffer, 0, readBuffer.Length)) != 0)
                        {
                            writeStream.Write(readBuffer, 0, bytesRead);
                        }
                    }
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2018 8:52 PM

All replies

  • User-821857111 posted

    You should remove the try catch block and then use the debugger to find out exactly which part of the code is causing the exception.

    Wednesday, September 19, 2018 7:06 AM
  • User-501297529 posted

    Mikesdotnetting

    You should remove the try catch block and then use the debugger to find out exactly which part of the code is causing the exception.

    I found that this code is what is causing the exception, specifically the line:  using (WebResponse response = request.GetResponse()). 

    This is the error I get on that line

    Error processing report 'MonthlyCardsOrderedRpt'.  Error: System.Net.WebException: Too many automatic redirections were attempted.
       at System.Net.HttpWebRequest.GetResponse()
       at VerfImport.Verfparser.ProcessReport(ReportExecutionService re, String ReportFileName, String ReportName, String ReportTitle, String SubClientId, DateTime rptStartDate, DateTime rptEndDate, DateTime rptRptDate)

    What do I need to correct to not get that exception error.

     WebRequest request = WebRequest.Create(requestUri);
    
                    var reportTimeoutMilliseconds = int.Parse(ConfigurationManager.AppSettings["ReportTimeoutSeconds"]) * 1000;
                    request.Timeout = reportTimeoutMilliseconds;
    //#if DEBUG
    //                request.UseDefaultCredentials = false;
    //                request.Credentials = new System.Net.NetworkCredential("tlode", "faster6", "srobo");
    //#else
                    request.UseDefaultCredentials = true;
    //#endif
    
                    LogStep("Report '" + ReportName + "' for " + SubClientLabel + " - requesting.", false);
                    using (WebResponse response = request.GetResponse())
                    using (Stream readStream = response.GetResponseStream())
                    using (FileStream writeStream = new FileStream(OutputFileName, FileMode.Create))
                    {
                        readStream.ReadTimeout = reportTimeoutMilliseconds;
                        readStream.WriteTimeout = reportTimeoutMilliseconds;
    
                        byte[] readBuffer = new byte[4096];
                        int bytesRead = 0;
                        while ((bytesRead = readStream.Read(readBuffer, 0, readBuffer.Length)) != 0)
                        {
                            writeStream.Write(readBuffer, 0, bytesRead);
                        }
                    }
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2018 8:52 PM