why i am getting binary or string data would be truncated

Question

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'

Possible duplicate of [SQL Server String or binary data would be truncated](http://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) — Evaldas Buinauskas, Oct 07 '15 at 09:17

score 1 · Answer 1 · answered Oct 07 '15 at 09:14

1

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)

answered Oct 07 '15 at 09:14

japzdivino

1,668
2
13
24

score 0 · Answer 2 · answered Oct 07 '15 at 09:16

0

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.

answered Oct 07 '15 at 09:16

Indra Prakash Tiwari

817
1
5
15

score 0 · Answer 3 · answered Oct 07 '15 at 16:29

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

why i am getting binary or string data would be truncated

3 Answers3