String or binary data would be truncated.The statement has been terminated.

Question

Post reply

String or binary data would be truncated.The statement has been terminated.

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
August 9, 2010 at 6:47 am

#236460

Hi there - Me again I'm afraid.
I am running the following code which I am in the process of putting together -
INSERT INTO Customers
(
RecordStatusID,
CreatedDate,
CreatedTime,
CreatedByID,
Residential,
ShortName,
ShortCode,
SiteTypeID,
[Name],
Address,
City,
ProvinceCountyID,
StateCountyID
)
SELECT 0 AS RecordStatusID
,GETDATE() AS CreatedDate
,{ fn NOW() } AS CreatedTime
,2 AS CreatedByID
,0 AS Residential
,CASE WHEN PROP.postcode IS NULL
THEN PROP.address
ELSE PROP.postcode + N'/' + PROP.address
END AS ShortName
,'' AS ShortCode
,0 AS SiteTypeID
,'' AS Name
,PROP.address
,PROP.city
,0 AS ProvinceCountyID --Need to clear this with Solarvista
,0 AS StateCountyID
FROM DataWarehouse.dbo.HEX_PROP_AND_UNIT AS PROP
When I run the code I get the following error -
Server: Msg 8152, Level 16, State 6, Line 1
String or binary data would be truncated.
The statement has been terminated.
I think I know why it is.
The ShortName in the Customers table is limited to 30 characters (Datatype nvarchar) but because of the way I am making the Shortname in my Select statement via the CASE it is exceding the 30 characters.
How can I limit this is just 30 characters in my SELECT STATEMENT?
Thanks
Ryan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

inampudianil Ten Centuries Points: 1274 More actions · Answer 1

i guess try it with //(double slash )in case staement

Thnx Anil

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Ryan

That all depends on your requirements. Here's one way (not tested):

...

LEFT(COALESCE(PROP.postcode + N'/','') + PROP.address ,30)

...

John

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

In my CASE statement I want my results to be limited to 30 characters.

So in the ELSE part of the statement I have

ELSE PROP.postcode + N'/' + PROP.address, it is this that I want limited.

Thanks

Ryan

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Ryan

But how do you want to limit it? First 30 characters? Last 30 characters? Remove all vowels? If you want the first 30 characters, use the code snippet I provided earlier. You'll notice that because it uses COALESCE, you don't need a CASE statement.

John

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

Oh sorry John. It is limied to the first 30 characters. I will give it a go.

Thanks

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 6

TSQL Tryer

SSCrazy Eights

Points: 8869

August 10, 2010 at 2:14 am

#1204543

Just to say Thanks.

This worked.