SP Error - String or binary data would be truncated

Question

SP Error - String or binary data would be truncated

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
November 28, 2008 at 2:43 am

#383480

Ok cant seem to get around this,
the error is:
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 33
String or binary data would be truncated.
The statement has been terminated.
This part of the SP is failing:
INSERT INTO dbo.tblClients
(Client_Short_Name,
Client_Long_Name,
CountryCode,
ClientID)
SELECT dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID
FROM dbo.vwImportAllNewClientIDs
The following are the table details:
dbo.tblClients
Client_short_Name (nvarchar(30),not null)
Client_long_name (nvarchar(100), null)
CountryCode (varchar(5), null)
ClientID (PK,nvarchar(10), notnull)
VwImportAll
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)
View uses information from the following table:
dbo.tblBarg - Populated by my SSIS package from a flat file
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)
ANY HELP IS MUCH APPRECIATED!!!!

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

pri.amin (11/28/2008)
Ok cant seem to get around this,
the error is:
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 33
String or binary data would be truncated.
The statement has been terminated.
dbo.tblClients
Client_short_Name (nvarchar(30),not null)
Client_long_name (nvarchar(100), null)
CountryCode (varchar(5), null)
ClientID (PK,nvarchar(10), notnull)
VwImportAll
Client (varchar(255),null)
ClientID (varchar(20),null)
CountryCode (varchar(50),null)

One or more rows from VwImportAll have a value of Client which exceeds the length of the Client_short_Name column.

View them by using this:

SELECT TOP 10 Client FROM VwImportAll ORDER BY LEN(Client) DESC

I guess you have at least two options here: increase the length of the Client_short_Name column, or insert a substring of the client column.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 2

problem is length of datatype (char / varchar), just increase the length of the datatype and then try it.

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 3

That was extremely helpful…. THANK YOU VERY MUCH!!!! Chris Morris

Made me a happy man, I know thats sad but this has been hurting my head for the last 2 days!!

So when I run

SELECT Client

FROM dbo.vwImportAll

ORDER BY LEN(Client) DESC

It does bring me back names which are larger than exspected! >30

Not I dont think I will be able to change the column length!

What did you mean by 'insert a substring of the client column'?

Could I not just change the 'Insert SQL' to do:

When the dbo.vwImportAllNewClientIDs.Client > 30 inserting into Client_Short_name ....

therefore when we have a big client name over 30 characters it insertis it into Client_Short_name like 'I HAVE A REALLY BIG COMANY NAM.....'

Hope that clears!!

Rin Sitah SSCrazy Points: 2180 More actions · Answer 4

I think what he is refering to is using a substring command to only return the 1st 30 characters of vwImportAllNewClientIDs.Client...

--

INSERT INTO dbo.tblClients

(Client_Short_Name,

Client_Long_Name,

CountryCode,

ClientID)

SELECT substring(dbo.vwImportAllNewClientIDs.Client,1,30),dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID

FROM dbo.vwImportAllNewClientIDs

--

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi

You could truncate using LEFT() as follows:

[font="Courier New"]DROP TABLE #tblClients

CREATE TABLE #tblClients (

Client_short_Name NVARCHAR(30) NOT NULL,

Client_long_name NVARCHAR(100) NULL,

CountryCode VARCHAR(5) NULL,

ClientID NVARCHAR(10) NOT NULL)

DECLARE @Client VARCHAR(255)

SET @Client = 'This is a 30-char client name.Here are another thirty chars.'

INSERT INTO #tblClients

(Client_Short_Name,

Client_Long_Name,

CountryCode,

ClientID)

SELECT

LEFT(@Client, 30) AS Client,

LEFT(@Client, 100) AS Client,

'UK' AS CountryCode,

RIGHT(NEWID(),10) AS ClientID

UNION ALL SELECT

CASE WHEN LEN(@Client) > 30 THEN LEFT(@Client, 27) + '...' ELSE @Client END AS Client,

CASE WHEN LEN(@Client) > 100 THEN LEFT(@Client, 97) + '...' ELSE @Client END AS Client,

'UK' AS CountryCode,

RIGHT(NEWID(),10) AS ClientID

SELECT * FROM #tblClients

[/font]

In the lower example, an indication is given that the original string was truncated by replacing the last 3 characters of the string with an ellipsis [...].

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 6

That looks good but the SP uses a view to incert the data into tblclients:

INSERT INTO dbo.tblClients

(Client_Short_Name,

Client_Long_Name,

CountryCode,

ClientID)

SELECT dbo.vwImportAllNewClientIDs.Client,

dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID

FROM dbo.vwImportAllNewClientIDs

Where fore can I replace the above code with that in my SP?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Like this:

[font="Courier New"]INSERT INTO dbo.tblClients

(Client_Short_Name,

Client_Long_Name,

CountryCode,

ClientID)

SELECT

CASE WHEN LEN(c.Client) > 30 THEN LEFT(c.Client, 27) + '...' ELSE c.Client END AS Client,

CASE WHEN LEN(c.Client) > 100 THEN LEFT(c.Client, 97) + '...' ELSE c.Client END AS Client,

c.CountryCode,

c.ClientID

FROM dbo.vwImportAllNewClientIDs c

[/font]

Note that I've added a table alias (c). In this case it's not necessary, just cosmetic - makes the code easier to read.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 8

Chris thats fantastic, I cant thank you enough!

I willm try this and keep toy posted:

Drop tblclients

Create NEW tblclients

Edit SP with new INSERT

RUN SP

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 9

Sorry, what did you mean by the bit of code:

DECLARE @Client VARCHAR(255)

SET @Client = 'This is a 30-char client name.Here are another thirty chars.'

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 10

Ok getting the following error when running the update:

Msg 8152, Level 16, State 4, Line 1

String or binary data would be truncated.

The statement has been terminated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

pri.amin (11/28/2008)
Sorry, what did you mean by the bit of code:
DECLARE @Client VARCHAR(255)
SET @Client = 'This is a 30-char client name.Here are another thirty chars.'

This is just sample data, in the absence of real tables to run the code against.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

pri.amin (11/28/2008)
Ok getting the following error when running the update:
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.

Looks like you may have to check the maximum data length in ClientID and CountryCode in VwImportAll. Both of these columns can also have more characters than can fit into their respective target columns.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden