Stored Procedure secretly converts to int? 2

[woetech]

Hello. I've been having a mind boggling strange problem with a stored procedure I'm using within an ASP.NET website.

Basically, I'm importing data from an Excel file/template into an existing table in a database (updating data as necessary). The steps are that I use sqlbulkcopy to get the data from the Excel file to a temp table in SQL Server, which is working fine as I can see all data correctly imported into this table. Then I call my stored procedure to parse through the temp table and update the real table based on the information there. This is where the problem is occurring, at least that is what I keep getting for an error via ASP.NET and Visual Studio.

The specific error is: "Conversion failed when converting the varchar value '63.10' to data type int." and happens during the call to the SP via the ASP.NET (VB.NET) code. The value '63.10' above resides in a 'varchar(50)' field in the temp table and is being imported/updated to the real table which has column datatype 'float'. The code in the SP (or at least the important snippets) is as follows:

[dbo].[sp_MBN_validate_import_EIDs2_admin]  @table varchar(100) AS

 BEGIN
	DECLARE @strSQL varchar(2000)

	--CarcassID
	SET @strSQL = 'UPDATE view_User_Animals SET CarcassID = B.[Carcass ID]
							FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID 
							WHERE B.[Carcass ID] IS NOT NULL'
	EXEC(@strSQL)

	--CarcassWeight
	SET @strSQL = 'UPDATE view_User_Animals SET CarcassWeight = B.[Carcass Weight]
							FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID 
							WHERE B.[Carcass Weight] IS NOT NULL'
	EXEC(@strSQL)

...

	--DressingPercentage
	SET @strSQL = 'UPDATE view_User_Animals SET DressingPercentage = B.[Dressing Percentage]
							FROM view_User_Animals A JOIN ' + @table + ' B ON A.EID=B.EID 
							WHERE B.[Dressing Percentage] IS NOT NULL'
	EXEC(@strSQL)

...

 END

It's nothing special and I'm just passing it the temp table name. The part that is causing the issue is where the 'DressingPercentage' is getting updated. As you can see, based on the datatypes in the temp table and the datatypes in the real table, and how the SP pans out, there's no reason at all for it to be trying to convert the values to an integer. Yet, no matter what I try, I continually get that error. What's even more strange is we've used this feature (and this SP) before in the past without error, though this is the first time the 'DressingPercentage' is actually being updated whereas before all other columns were getting updated.

Please, if anyone has any suggestions or sees anything wrong, let me know right away as it's starting to drive me mad. Thanks for your help!

 

[gmmastros]

Looks like you are updating a view. Can you show the view definition?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[woetech]

Oh, sorry about that. There's actually 2 Views you'll probably need:

View_User_Animals

[dbo].[View_User_Animals]
AS
SELECT     U.UserId, UN.LastName + ', ' + UN.FirstName AS Name, UN.FirstName + '_' + UN.LastName AS FullName, 
                      UN.FirstName + ' ' + UN.LastName AS RealName, UN.FirstName AS theFN, UN.LastName AS theLN, F.EntityTypeDesc AS EntityType, 
                      E.EntityName AS Entity, E.EntityID, E.Alliance, G.LotTypeDesc AS LotType, AG.LotAType AS LotATypeID, AG.LotATypeDesc AS LotAType, 
                      C.LotDesc AS Lot, C.LotID, C.Active AS LotActive, C.AuditDone AS LotAuditDone, D.SubLotDesc AS SubLot, D.SubLotID, C.City, C.County, C.State, 
                      A.AnimalID, A.EID, A.Gender, A.GenderDesc, A.Practice, A.PracID, A.CarcassID, A.BrucellosisID, A.RanchTagID, A.OtherID, A.Owner, A.Breed, A.BreedID, 
                      A.Sire, A.SireBreed, A.SireBreedID, A.Dam, A.DamBreed, A.DamBreedID, A.Color, A.ColorID, A.ReplacementTag, A.BirthDate, A.BDMethod, 
                      A.BirthWeight, A.BrandDate, A.WeaningDate, A.WeaningWeight, A.GroupWeaningWeight, A.OtherWeight, A.BodyConditionScore, A.PregTestDesc, 
                      A.PregTest, A.Comments, A.FL_ArrivalWeight, A.FL_ReimplantWeight, A.FL_CheckWeight, A.FL_OutWeight, A.FL_Comments, A.FL_Name, A.FL_Lot, 
                      A.FL_Pen, A.CarcassWeight, A.LiveWeight, A.EstimatedLiveWeight, A.DressingPercentage, A.QualityGradeDesc, A.QualityGrade, A.USDA_YieldGrade, 
                      A.CalculatedYieldGrade, A.PreliminaryYieldGrade, A.MarblingScoreDesc, A.MarblingScore, A.BackfatThickness, A.RibeyeArea, A.KPH_Fat, 
                      A.HarvestDate, A.REA_CWT, A.PackerLotId, A.PackerComments, A.ActiveDesc, A.Active, A.CreateDate, A.EditDate, A.AuditDone, A.TransDate, 
                      A.Unusable, A.UnusableDesc, A.UnusableComment, A.Type, A.TypeDesc, A.TypeDate, A.TypeComment
FROM         dbo.View_Animal AS A INNER JOIN
                      dbo.AnimalLots AS B ON A.AnimalID = B.AnimalID INNER JOIN
                      dbo.Lot AS C ON B.LotID = C.LotID LEFT OUTER JOIN
                      dbo.SubLot AS D ON B.SubLotID = D.SubLotID INNER JOIN
                      dbo.Entity AS E ON C.EntityID = E.EntityID INNER JOIN
                      dbo.EntityType AS F ON E.EntityType = F.EntityType INNER JOIN
                      dbo.LotType AS G ON C.LotType = G.LotType INNER JOIN
                      dbo.LotAType AS AG ON C.LotAType = AG.LotAType INNER JOIN
                      dbo.UserEntity AS UE ON E.EntityID = UE.EntityId INNER JOIN
                      dbo.aspnet_Users AS U ON UE.UserId = U.UserId INNER JOIN
                      dbo.UserName AS UN ON U.UserId = UN.UserId

AND View_Animal

[dbo].[View_Animal]
AS
SELECT     A.AnimalID, A.EID, A.Gender, B.GenderDesc, C.PracDesc AS Practice, A.Practice AS PracID, A.CarcassID, A.BrucellosisID, A.RanchTagID, A.OtherID, 
                      A.Owner, D.BreedDesc AS Breed, A.Breed AS BreedID, A.Sire, E.BreedDesc AS SireBreed, A.SireBreed AS SireBreedID, A.Dam, 
                      F.BreedDesc AS DamBreed, A.DamBreed AS DamBreedID, G.ColorDesc AS Color, A.Color AS ColorID, A.ReplacementTag, A.BirthDate, A.BDMethod, 
                      A.BirthWeight, A.BrandDate, A.WeaningDate, A.WeaningWeight, A.GroupWeaningWeight, A.OtherWeight, A.BodyConditionScore, P.PregTestDesc, 
                      A.PregTest, A.Comments, A.FL_ArrivalWeight, A.FL_ReimplantWeight, A.FL_CheckWeight, A.FL_OutWeight, A.FL_Comments, A.FL_Name, A.FL_Lot, 
                      A.FL_Pen, A.CarcassWeight, A.LiveWeight, A.EstimatedLiveWeight, A.DressingPercentage, Q.QualityGradeDesc, A.QualityGrade, A.USDA_YieldGrade, 
                      A.CalculatedYieldGrade, A.PreliminaryYieldGrade, M.MarblingScoreDesc, A.MarblingScore, A.BackfatThickness, A.RibeyeArea, A.KPH_Fat, 
                      A.HarvestDate, A.REA_CWT, A.PackerLotId, A.PackerComments, dbo.Active.ActiveDesc, A.Active, A.CreateDate, A.EditDate, A.AuditDone, A.TransDate, 
                      A.Unusable, U.UnusableDesc, A.UnusableComment, A.Type, T.TypeDesc, A.TypeDate, A.TypeComment
FROM         dbo.Animal AS A INNER JOIN
                      dbo.Type AS T ON A.Type = T.Type LEFT OUTER JOIN
                      dbo.Gender AS B ON A.Gender = B.Gender LEFT OUTER JOIN
                      dbo.Practice AS C ON A.Practice = C.Practice LEFT OUTER JOIN
                      dbo.Breed AS D ON A.Breed = D.Breed LEFT OUTER JOIN
                      dbo.Breed AS E ON A.SireBreed = E.Breed LEFT OUTER JOIN
                      dbo.Breed AS F ON A.DamBreed = F.Breed LEFT OUTER JOIN
                      dbo.Color AS G ON A.Color = G.Color LEFT OUTER JOIN
                      dbo.PregTest AS P ON A.PregTest = P.PregTest LEFT OUTER JOIN
                      dbo.QualityGrade AS Q ON A.QualityGrade = Q.QualityGrade LEFT OUTER JOIN
                      dbo.MarblingScore AS M ON A.MarblingScore = M.MarblingScore LEFT OUTER JOIN
                      dbo.Active ON dbo.Active.Active = A.Active LEFT OUTER JOIN
                      dbo.Unusable AS U ON A.Unusable = U.Unusable

I know, probably looks like a giant mess, but it's not and it works, lol.

 

[gmmastros]

I don't see anything wrong. Clearly, there must be, but I don't see it.

Last ditch effort.... do you have any triggers on the tables? based on the description of the problem, I would suspect a trigger on the dbo.Animal table could be causing this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[woetech]

I'm sorry to bother you guys, but I think I just found the solution. I didn't even notice this or realize what was happening, but in the SP, right above the DressingPercentage code is some code for another column of data (not shown in this topic). In that code it references the DressingPercentage data and tries to do some math with a couple integer values. As soon as I wrapped a CAST AS FLOAT around the DressingPercentage data, all seemed to work fine. I really don't know how I missed that, but at least it's finally fixed now. Thanks for putting up with this.