3

Scenario: A large stored procedure is run thousands of time a day, 6 days a weeks every year. For this one specific situation I'm getting an error

Msg 8152, Level 16, State 14, Line 114
String or binary data would be truncated

This stored procedure is about 800 lines of code, with lots of parameters, lots of variables and lots of tables. 

Go
@exampleParam varchar(30) 
@exampleParam datetime
DECLARE @declaredvara VARCHAR(50);
DECLARE @declaredvarb VARCHAR(50);  -- where the line error is according to the printout
DECLARE @declaredvarc VARCHAR(50);
DECLARE @declaredvard VARCHAR(50);  -- where line error is adjusted for comments in front of GO
BEGIN TRANS
-- some calculations (700+ lines)
COMMIT TRANS
--error catch
ROLLBACK TRANS

Problem: I'm want to know if its possible to get an accurate Line error read, so I can at least narrow down which tables I have to check. There are tons of tables involved in this stored procedure.

*** Both declared variables(before and after adjustment of GO) it points to are dead weight, they're nvr used.

Bonus info: I ran the stored procedure with params populated and params = '' except for the datetimes. In both cases I got the same error. However, I know the stored procedure works because it's being used thousands of times a day w/out this error.

EXEC SP '','','','','2014-11-6'
EXEC SP 'XX_XX_XX','',1,'','2014-11-6'
--both return the same error of Msg 8152, Level 16, State 14
--XX is a specific serialnum.  
--However all other serialnum have no problem

EDIT: The DB is running SQL Server 2005 EDIT2: I'm using SQL Server 2008 to edit. - So debug isn't an option

MADnoobie
  • 74
  • 1
  • 2
  • 9
  • Have you compared `@declaredvarb` as a varchar(50) to its sql server table equivalent? Somewhere you are putting in either 1.) more than 50 chars into the variable, 2.) the column schema is more narrow than your data (the string is greater than your column) or 3. putting in Unicode data into a varchar field. – crthompson Nov 06 '14 at 23:16
  • The line number will only tell you the number of lines from the beginning of the batch, so if you have a `GO` (or something else if you have redefined your batch separator), it is the number of lines from there. – DavidG Nov 06 '14 at 23:21
  • 1
    double click the error message in SSMS and it should take around about where the error line is. – M.Ali Nov 06 '14 at 23:27
  • @paqogomez The variables the line error points to aren't even used. Its just declared, upon commenting them, the same error occurs. – MADnoobie Nov 07 '14 at 14:58
  • @DavidG I adjusted the count for the GO just before the AlterProcedure. In either case, they point to a declared variable that aren't in use. – MADnoobie Nov 07 '14 at 14:59
  • @M.Ali The error occurs when I exec the SP, the error message simply directs itself to the EXEC command. – MADnoobie Nov 07 '14 at 14:59
  • You can do is `SET ansi_warnings OFF`. Check out this post http://blog.sqlauthority.com/2015/02/14/sql-server-msg-8152-level-16-state-14-string-or-binary-data-would-be-truncated/ – Jaider Mar 22 '16 at 16:23
  • You can find the error using the SQL Server Profiler. http://stackoverflow.com/questions/13380972/ef-exception-string-or-binary-data-would-be-truncated-the-statement-has-been-t – Jaider Mar 22 '16 at 16:28

5 Answers5

7

Used that it will fix Msg 8152, Level 16, State 14, Line 114 String or binary data would be truncated.

SET ansi_warnings OFF
GO
Pang
  • 9,564
  • 146
  • 81
  • 122
Anees Rana
  • 71
  • 1
  • 2
  • While your answer here is probably not best practice, in some circumstances it can work out OK. & in my case it was just perfect because I wasn't too concerned about what I might lose in the truncation, but it was a right PITA to figure out which of the 20 or so fields was not lining up correctly... THX! – kiltannen May 29 '19 at 04:55
4

This error indicates that you are going to store some thing bigger that it's storage. you may need to check if you are going to store some string with more that 50 characters in @declaredvara or @declaredvarb or @declaredvarc or @declaredvard or any other variables or table columns. for example you may define @test as varchar(2) and then try to insert 'vahid' into it. in this case similar exception will be thrown. There is 2 way(as I know) that you can find the error ocurance line:

  1. If you need to find the exact line of error occurrence you may need to debug you SP using sql server debug tools(Read more about debugging in Sql Server here)
  2. You can also use TRY...CATCH blocks to check which block of code in you SP causes the error.
Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
  • Unfortunately the DB is SQL Server 2005 but the editing software I have is 2008 so debug isn't an option. – MADnoobie Nov 07 '14 at 14:56
  • For more details- please refer https://www.tsql.info/error/msg-8152-level-16-string-or-binary-data-would-be-truncated.php#:~:text=On%20Transact%20SQL%20language%20the,than%20the%20length%20of%20column. – Avnish Patel May 22 '23 at 12:50
4
BEGIN CATCH    
 DECLARE @body VARCHAR(4000)    
 SET @body='<b>Exception in spname   </b><br/>'    
 SET @body=@body + ( SELECT        
 '<b>Error No :</b> '+ CONVERT(VARCHAR(100),ERROR_NUMBER()) +'<br/>'    
  +'<b>Error Severity :</b> '+CONVERT(VARCHAR(1000),ERROR_SEVERITY()) +'<br/>'    
  +'<b>Error State :</b> '+CONVERT(VARCHAR(1000),ERROR_STATE()) + '<br/>'    
  +'<b>Error Procedure :</b> '+CONVERT(VARCHAR(1000),ERROR_PROCEDURE())+'<br/>'    
  +'<b>Error Line :</b> '+CONVERT(VARCHAR(100),ERROR_LINE())+'<br/>'    
  +'<b>Error Message :</b> '+CONVERT(VARCHAR(2000),ERROR_MESSAGE()));    

END CATCH
ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
Sunny Sunny
  • 51
  • 1
  • I don't use TRY CATCH that often (bad habit? I'm still a newbie I guess) however woudldn't you need a PRINT cmd to see the error? – MADnoobie Nov 07 '14 at 15:11
0

It turns out one of the variables in the area was declared but never given a value.

That variable was later used as a param in executing another stored procedure from within. Which resulted in a null param being passed into a param that couldn't contain it.

Thanks everyone for the responses.

MADnoobie
  • 74
  • 1
  • 2
  • 9
-1

if your inserting in varchar(3) and selecting from varchar(10), you are getting the error. You have to design them same as varchar(3) or varchar(10).

AEC Production
  • 21
  • 6