x

Loading SQL statement into column

I am trying to load a sql statement into a column which is varchar(8000)

The sql is only 200 lines so it can't be reaching the limit of the column but I'm getting an error of "String binary data would be truncated" when I try to insert the sql

Statement Terminated

How do i work around this?

more ▼

asked Aug 24, 2011 at 04:58 PM in Default

avatar image

siera_gld
1k 82 88 93

200 lines or 200 characters?

Aug 24, 2011 at 04:59 PM Tim

200 lines -

Aug 24, 2011 at 05:06 PM siera_gld

can we see the query which you are using ???

Aug 25, 2011 at 05:44 AM Mandar Alawani

Thank you for all your input - I am waiting for end user feedback and will resume work in a day or so

Aug 30, 2011 at 08:54 AM siera_gld
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Are you sure that all 200 lines of code you are inserting are 40 characters or less?

more ▼

answered Aug 24, 2011 at 06:44 PM

avatar image

Tim
40.4k 39 84 166

(comments are locked)
10|1200 characters needed characters left

The error message doesn't lie - You are hitting the limit of the column.

Take a look at:

  • removing line breaks (these are characters too!)

  • changing the column to a varchar(max) (if you are 2005 and above, you didn't tag the question)

  • removing unnecessary text from the query (make comments briefer, use aliases in your code)

That should give you a start

more ▼

answered Aug 25, 2011 at 12:01 AM

avatar image

WilliamD
26.2k 18 34 48

(comments are locked)
10|1200 characters needed characters left

copy the TSQL into a word processing app and check the properties to see how many characters there are or split the code into two and assign each piece to a variable and check the LEN property

 DECLARE @var1 VARCHAR(8000)
 DECLARE @var2 VARCHAR(8000)

 SET @var1 = 'Select top 100 * '
 SET @var2 = 'from sales'

 SELECT  LEN(@var1) AS Len1 ,
     LEN(@var2) AS len2 ,
     LEN(@var1) + LEN(@var2) AS [Total]


 SET @var1 = 'Select top 
 100 * '
 SET @var2 = 'from sales'

 SELECT  LEN(@var1) AS Len1 ,
     LEN(@var2) AS Len2 ,
     LEN(@var1) + LEN(@var2) AS [Total]
more ▼

answered Aug 25, 2011 at 01:04 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left

I can add some observation even though other answers have already covered it. Generally speaking, it is ill advised to create a table which has in_row_data potential size exceeding 8060 bytes. While SQL Server 2008 (and probably even latest pack of 2005) will allow you to do so because SQL Server 2008 has an additional storage area for in_row_data overflows, it is still a bad idea. It is easy enough to opt for varchar(max) instead of varchar(8000) in those scenarios because the former is considered a BLOB and does not have to be stored in row while the latter is always stored in row thus making the records unnecessary wide (allowing only 1 record per page should the varchar(8000) column be filled completely).

Here is the script which is terrible but runs just fine in SQL Server 2008:

 -- never create a horrible table like this because allowing
 -- it to be designed with the possibility of overflowing
 -- in-row_data could be a recipe for disaster.
 -- (note that the record size can be above the 8060 limit)
 create table #test
 (
     RecordID int not null primary key clustered,
     ScriptName char(200) not null default ('a'),
     ScriptText varchar(8000) not null
 );
 go
 
 insert into #test (RecordID, ScriptText)
 select 1, replicate('b', 100);
 go
 
 insert into #test (RecordID, ScriptText)
 select 2, replicate('c', 8000);
 go
 
 select datalength(ScriptText) ScriptSize from #test;
 go

 -- result
 
 ScriptSize
 -----------
 100
 8000

The above happily creates a table and inserts 2 records despite the fact that the second record totalling 8226 bytes is wider than 8060 bytes which a data page can hold. I don't have SQL Server 2000 database handy but I remember that the table like this would be created (with warning) but then the first insert would succeed and the second - fail with

 Msg 8152, Level 16, State 14, Line 1
 String or binary data would be truncated.
 The statement has been terminated.

error. The error can be suppressed and the record - allowed to be inserted by settting the ansi_warnings off, but the results of such a chicanery would be far from desired because some 160 odd characters would be chopped off.

Oleg

more ▼

answered Aug 25, 2011 at 08:46 AM

avatar image

Oleg
17.1k 3 7 28

I do not believe this is the exact thing happening in the code but may explain some "refinements" needed

Aug 30, 2011 at 08:53 AM siera_gld
(comments are locked)
10|1200 characters needed characters left

Don't forget that each line break could be two characters (carriage-return AND linefeed - the joys of having conventions hanging over from real typewriters); leading tabs / spaces also count where they wouldn't in a wordcount. Are the blank lines blank, or loaded with spaces? Have you removed trailing spaces from the lines?

more ▼

answered Aug 30, 2011 at 09:03 AM

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x42
x12
x3

asked: Aug 24, 2011 at 04:58 PM

Seen: 1328 times

Last Updated: Aug 25, 2011 at 03:46 AM

Copyright 2016 Redgate Software. Privacy Policy