Understanding Error Messages
-
Can anyone shed any light on the following error message:
Server: Msg 8152, Level 16, State 9, Line 5
String or binary data would be truncated.
The statement has been terminated.
Firstly, let me clarify - I understand what the message as a whole is telling me, and have been able to fix the issue. However, tracking down the offending line in my code was not so easy.
Is this message meant to give me a clue as to where the error has occured?
The code is as follows:
CREATE TABLE
[dbo].[#SE_Sales_Tender] (
[transaction_type] VARCHAR (20) NULL
,[store_code] VARCHAR (4) NULL
,[register_code] CHAR (2) NULL
,[sale_code] VARCHAR (12) NULL
,[seq_num] VARCHAR (4) NULL
,[tender_value] VARCHAR (2) NULL
,[currency_code] VARCHAR (3) NULL
,[serial_num] VARCHAR (16) NULL
,[gl_account_code] VARCHAR (14) NULL
,[virtualdrawer_code] VARCHAR (14) NULL
,[tenderline_id] VARCHAR (14) NULL
,[tendertype_id] VARCHAR (14) NULL
,[gltranslation_code] VARCHAR (14) NULL
,[start_time] VARCHAR (14) NULL
,[line_type] VARCHAR (1) NULL
 
ON [PRIMARY]GO
INSERT INTO #SE_Sales_Tender
SELECT
tl.tendertype_id AS [Transaction Type]
,LEFT(tl.source_doc_code, 4) AS [Store Code]
,RIGHT(LEFT(tl.source_doc_code, 6), 2) AS [Till Code]
,tl.source_doc_code AS [Transaction Number]
,1 AS [Sequence]
,CASE
WHEN tl.used_currency_code = tl.local_currency_code THEN CONVERT(VARCHAR(20), CAST(ROUND(tl.tender_value - tl.change_given + tl.rounding_outcome,2) AS DECIMAL(10,2)))
WHEN tl.used_currency_code <> tl.local_currency_code THEN CONVERT(VARCHAR(20), CAST(tl.foreign_tender_value AS DECIMAL(10,2)))
ELSE '0.0'
END
AS [Tender Value]
,tl.used_currency_code AS [Currency]
,tl.reference_number AS [Serial Number]
,NULL AS [GL Code]
,tl.virtualdrawer_code AS [VD Code]
,tl.tenderline_id AS [Tenderline ID]
,tl.tendertype_id AS [Tender Type]
,NULL AS [GL Translate]
,ssh.start_time AS [Start Time]
,'T' AS [Line Type]
FROM tenderline tl (INDEX = ref_source_code)
,#SE_Sales_Header ssh
Thanks
-
Theoretically, Line 5 should refer to the line in your code where the error is. Sometimes that works for me, and sometimes not.
Usually in a situation like this, I just comment out every field except one and start trying to insert. This is a pain and takes a long time if your data set is large, but it works better for me than depending on which line the error actually occurred on.
Also in Query Analyzer, you can double click on the error and it will highlight the line of code that threw the error. Again the phrase "Sometimes that works for me, and sometimes not." applies.
Michelle -
Its all varchar, so why don't you just run the select but put len around everything and see what is larger than the field size. Truncate error means the data is too big obviously. You could also put a substring around everything:
select substring(expression, 1, length of field), ....
Its hard to tell when we don't have the script for the from tables just the to table.
-
Thanks to you both, mimorr and Edward - I had already worked out where the problem was, and what the error meant.
My question was more around the error message - is it supposed to direct me to the problem?
I too thought it meant Line 5, as it said, but line 5 in my code was not the issue.
I will try double clicking on the error message next time - I didn't know you could do that!
-
It does point you to the correct line. However, how Microsoft counts lines and you count lines may be different.
For example, how many lines are each of these:
SELECT col1, col2, col3
FROM mytable
SELECT col1,
col2,
col3
FROM mytable
From what I have seen, both are only two lines. But the bottom example appears to be four lines.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply