November 28, 2008 at 2:43 am
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!!!!
November 28, 2008 at 3:12 am
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
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
November 28, 2008 at 3:30 am
problem is length of datatype (char / varchar), just increase the length of the datatype and then try it.
November 28, 2008 at 3:51 am
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!!
November 28, 2008 at 4:11 am
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
--
November 28, 2008 at 4:15 am
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
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
November 28, 2008 at 4:33 am
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?
November 28, 2008 at 4:44 am
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
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
November 28, 2008 at 4:48 am
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
November 28, 2008 at 5:10 am
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.'
November 28, 2008 at 5:24 am
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.
November 28, 2008 at 5:30 am
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.
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
November 28, 2008 at 5:35 am
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.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply