Loading SQL statement into column

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

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

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?

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]

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