First let me thank @stepson and @Kristen for your help.
After further investigation I have found the problem is not necessarily with the SSN. I have stepped through the query, commenting out columns and uncommenting. That is how I found SSN, however, when I just use SSN it works. I find that one of two other columns will cause SSN to throw the error.
Here is the original:
IF OBJECT_ID (N'tempdb..#Curr', N'U') IS NOT NULL
DROP TABLE #Curr;
CREATE TABLE #Curr (
BatchGuid uniqueidentifier,
App varchar(1),
RefNum varchar(10),
MemoText varchar(40),
NewAddr varchar(80),
NewPhone varchar(10),
[NewNamex] varchar(50),
SecName varchar(26),
POEPhone varchar(10),
POEName varchar(15),
POEAddr varchar(40),
CeaseDesistDate date,
DisputeDate date,
CCCInfo varchar(20),
ConsolidatorInfo varchar(20),
NewSSN varchar(9),
AKAName varchar(15),
PhoneAddrUpdateDate date
)
insert into #Curr (
--BatchGuid,
App,RefNum,
NewAddr,
NewPhone,
[NewNamex],
SecName,
NewSSN)
select --(select BatchGuid from #TmpParms),
'P',
A.MatterNum,
left(ISNULL(C.Addr1 + ' ', '') + ISNULL(C.Addr2 + ' ', '') + ISNULL(C.City + ' ', '') + ISNULL(C.State + ',', '') + ISNULL(C.Zip, ''), 80) AS NewAddr,
C.Phone1 AS NewPhone,
left(ISNULL(C.FirstName + ' ', '') + ISNULL(C.LastName, ''),26) AS [NewNamex],
left(ISNULL(C2.FirstName + ' ', '') + ISNULL(C2.LastName, ''),26) AS SecName,
left(C.SSN,9)
--C.SSN
from MyTable1 A
inner join MyTable2 C on A.MatterNum = C.MatterNum and C.ContactType = 'DECEASED'
left join MyTable3 C2 on A.MatterNum = C2.MatterNum and C2.ContactType = 'COSIGNOR'
It turns out that NewNamex or NewAddr will cause the problem when not using left(C.SSN,9)