On production we found bug for truncating data on specific column.
I investigated and saw that the we are taking data from procedure from specific table which
had column varchar 150 to table with column varchar(50). I altered the table I want to insert data to varchar(150) and still getting the same error. Erorr on picture below.
Data table is created from the data I have got from SP: CreateAgingRepTvp(dataFromSp), then result I set up into parameter and should be inserted into table which column I have altered to accept 150 characters. But still I am getting the data is truncated on Data Table, when I have tried to set max length property on specific data table insert not working any more but I don't get any error. On SQL Server when I try to add manually the same data to the table I have altered it works.
private DataTable CreateAgeingReportTvp(List<AgeingReportItem> reportItems, Guid reportId)
{
DataTable ageingTvp = new DataTable();
ageingTvp.Columns.Add("Id", typeof(Guid));
ageingTvp.Columns.Add("ReportId", typeof(Guid));
ageingTvp.Columns.Add("PrincipalId", typeof(string));
ageingTvp.Columns.Add("PrincipalName", typeof(string));
ageingTvp.Columns.Add("Department", typeof(string));
ageingTvp.Columns.Add("BusinessArea", typeof(string));
ageingTvp.Columns.Add("AmtLessThan60", typeof(decimal));
ageingTvp.Columns.Add("Amt61To90", typeof(decimal));
ageingTvp.Columns.Add("Amt91To180", typeof(decimal));
ageingTvp.Columns.Add("Amt181To365", typeof(decimal));
ageingTvp.Columns.Add("AmtGreaterThan365", typeof(decimal));
ageingTvp.Columns.Add("TotalAR", typeof(decimal));
ageingTvp.Columns.Add("AdvanceReceived", typeof(decimal));
ageingTvp.Columns.Add("Provision", typeof(decimal));
ageingTvp.Columns.Add("NetReceivable", typeof(decimal));
ageingTvp.Columns.Add("PrevMonthBalance", typeof(decimal));
ageingTvp.Columns.Add("Remarks", typeof(string));
foreach (var item in reportItems)
{
DataRow row = ageingTvp.NewRow();
row["Id"] = item.Id;
row["ReportId"] = item.ReportId = reportId;
row["PrincipalId"] = item.PrincipalId;
row["PrincipalName"] = item.PrincipalName.Trim();
row["Department"] = item.Department;
row["BusinessArea"] = item.BusinessArea;
row["AmtLessThan60"] = item.AmtLessThan60 ?? 0;
row["Amt61To90"] = item.Amt61To90;
row["Amt91To180"] = item.Amt91To180;
row["Amt181To365"] = item.Amt181To365;
row["AmtGreaterThan365"] = item.AmtGreaterThan365;
row["TotalAR"] = item.TotalAR;
row["AdvanceReceived"] = item.AdvanceReceived ?? 0;
row["Provision"] = item.Provision;
row["NetReceivable"] = item.NetReceivable;
row["PrevMonthBalance"] = item.PrevMonthBalance;
row["Remarks"] = item.Remarks;
}
return ageingTvp;
}
using (TransactionScope tran = new TransactionScope())
{
var ageingReportId = SaveAgeingReport(columnAggregates, arAggregates, filter);
using (DataTable myTvpTable = CreateAgeingReportTvp(reportItems, ageingReportId))
{
SqlParameter parameter = new SqlParameter
{
ParameterName = "@tvpValues",
Value = myTvpTable,
SqlDbType = SqlDbType.Structured,
TypeName = "AgeingReportTvp"
};
sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItem SELECT * FROM @tvpValues;", new List<SqlParameter> { parameter });
}
var jobList = reportItems.SelectMany(x => x.JobList).ToList();
using (DataTable detailsTvpTable = CreateAgeingReportDetailsTvp(jobList))
{
SqlParameter detailsParameter = new SqlParameter
{
ParameterName = "@detailsTvpValues",
Value = detailsTvpTable,
SqlDbType = SqlDbType.Structured,
TypeName = "AgeingReportDetailsTvp"
};
sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItemDetails SELECT * FROM @detailsTvpValues;", new List<SqlParameter> { detailsParameter });
}
tran.Complete();
}
}
