CODEWITHSUNDEEP
sqlsql-server
HarshSharma
HarshSharma

Reputation: 660

why i am getting binary or string data would be truncated

I have below query in which i am inserting into the temporary table and when i try to run the query sql is throwing binary or string data would be truncated. But actually my data is in the range of defined nvarchar(400) for column PhoneModel.

CREATE TABLE #orders
(
    quoteHeaderId INT,
    PhoneModel nvarchar(400)
)

INSERT INTO #orders(quoteHeaderId, PhoneModel)
SELECT qh.QuoteHeaderId, 
   (Select ph.ModelName + ','  
    FROM t_handsetQuote h2
       INNER JOIN t_PhoneAudit t1 on t1.PhoneAuditId = h2.QuotePhoneAuditId
       INNER JOIN t_phoneModel ph on t1.phoneModelid = ph.phoneModelid                                                  
    where qh.QuoteHeaderId = h2.QuoteHeaderId
    FOR XML PATH('')
   ) AS PhoneModel
FROM t_QuoteHeader qh
INNER JOIN t_handsetQuote h on qh.QuoteHeaderId = h.QuoteHeaderId
INNER JOIN t_CustomerAdditionalInformation ch on qh.CustomerId = ch.CustomerId 
and keyName ='SendUpdatesByEmail' AND ch.value = 'yes'

Upvotes: 0

Views: 70

Answers (3)

James Z
James Z

Reputation: 12317

Easiest thing to see what's causing the problem is to use select ... into:

SELECT qh.QuoteHeaderId, 
   (Select ph.ModelName + ','  
    FROM t_handsetQuote h2
       INNER JOIN t_PhoneAudit t1 on t1.PhoneAuditId = h2.QuotePhoneAuditId
       INNER JOIN t_phoneModel ph on t1.phoneModelid = ph.phoneModelid                                                  
    where qh.QuoteHeaderId = h2.QuoteHeaderId
    FOR XML PATH('')
   ) AS PhoneModel
into #orders
FROM t_QuoteHeader qh
INNER JOIN t_handsetQuote h on qh.QuoteHeaderId = h.QuoteHeaderId
INNER JOIN t_CustomerAdditionalInformation ch on qh.CustomerId = ch.CustomerId 
and keyName ='SendUpdatesByEmail' AND ch.value = 'yes'

and then check the created table by ordering len(PhoneModel) desc

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

run your select query without insert statement, and check the len of ph.ModelName + ','. it could be differ record by record.

Run your query and provide feedback.

Upvotes: 0

japzdivino
japzdivino

Reputation: 1746

I think the nvarchar(400) cannot handle the length of string you inserted to it.

How about changing your PhoneModel nvarchar(400) into PhoneModel varchar(MAX)

Upvotes: 1

Related Questions