text, ntext, and image data types are invalid for local variables

SQL Server 2000 may give you this error if you try writing a Stored Procedure like:

DECLARE @Stuff1 char
DECLARE @TelText text

SET @Stuff1='C'
SET @TelText=(SELECT TextColumn FROM TestTable WHERE ID=1)

INSERT INTO tmpTable (somestuff, thetextcol) values
  (@Stuff1, @TelText)

As the error message suggests, SQL Server 2000 does not allow declaring variables of the type text, ntext or image.

A simple way of accomplishing the same thing is:

DECLARE @Stuff1 char

SET @Stuff1='C'

INSERT INTO tmpTable (somestuff, thetextcol)
  select @Stuff1, TextColumn FROM TestTable WHERE ID=1

7 Comments

AnonymousDecember 29th, 2005 at 5:43 pm

I love you, man!

LanmiMarch 6th, 2008 at 3:45 pm

Uhhh Thanks !!!!

AnonymousSeptember 22nd, 2008 at 7:55 pm

I’m annoyed that it’s this simple. Thanks though, it saved my life!!!!

VSApril 20th, 2012 at 10:50 pm

Just what I was looking. Very Good explanation. Keep it going.

SlaverMay 8th, 2012 at 5:02 pm

U R Great Master. Thanks a lot. I realy know sense of my life after reading your solution. Amazing job

ahmadMay 27th, 2013 at 1:27 am

Thanks alot. you saved my day!

karthiDecember 7th, 2013 at 1:22 pm

nice

Leave a comment

Your comment