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

siera_gld gravatar image

siera_gld
1k 78 84 85

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

Tim gravatar image

Tim
36.4k 38 41 139

(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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

Oleg gravatar image

Oleg
15.9k 2 4 24

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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x46
x11
x1

asked: Aug 24, 2011 at 04:58 PM

Seen: 1143 times

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