B
bootzilla
Guest
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);
}
Continue reading...
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);
}
Continue reading...