Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String or binary data would be truncated.The statement has been terminated. Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 6:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
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
Post #965909
Posted Monday, August 9, 2010 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 3:52 AM
Points: 55, Visits: 354
i guess try it with //(double slash )in case staement
Thnx Anil
Post #965918
Posted Monday, August 9, 2010 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,369, Visits: 9,922
Ryan

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

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

John

Post #965921
Posted Monday, August 9, 2010 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
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
Post #965941
Posted Monday, August 9, 2010 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,369, Visits: 9,922
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
Post #965944
Posted Monday, August 9, 2010 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
Oh sorry John. It is limied to the first 30 characters. I will give it a go.

Thanks
Post #966030
Posted Tuesday, August 10, 2010 2:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:23 AM
Points: 218, Visits: 442
Just to say Thanks.

This worked.
Post #966425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse