Yes, you read correctly, 59%. Some of these are undoubtedly programmer error where they are comparing an INTEGER with a VARCHAR value, but of the ones I've surveyed (out of the 5139 execution plans with the problem) the only conversion has been a varchar to varchar comparison, like this ...
ScalarString="CONVERT_IMPLICIT(varchar(250),[CAMMS].[Entity].[t_PersonName].[LastName] as [pn].[LastName]+', '+[CAMMS].[Entity].[t_PersonName].[FirstName] as [pn].[FirstName]+' '+[CAMMS].[Entity].[t_PersonName].[MiddleName] as [pn].[MiddleName],0)"
This part of the stored proc is concatenating name parts. The varchar(250) reference above appears to come from the fact that the Full-Name value is being inserted into a table with a column of type varchar(250)
CREATE TABLE #TmpMatter ( ObjectType varchar(50), ObjectID int, MatterEntityID int, CaseNumber varchar(100) null, Title varchar(50), DefendantName varchar(250) null ...
I checked COLLATION on both my database and on TempDB ... they are the same. Checked COLLATION on all text columns and that is correct. I have 12 columns whose ANSI PADDING is OFF but these CONVERT_IMPLICIT errors are showing up only on columns with it ON, so I'm not seeing any mismatch there.
One post said they fixed this problem by addressing the ANSI PADDING issue, but that can't be it ... unless my temp tables are being created with PADDING = OFF. But TempDB (and all system dbs) have PADDING = ON.
I'm stuck on this one. Anyone have any ideas?