"Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:OVMsmYE2GHA.1588@TK2MSFTNGP02.phx.gbl...
>I got this error:
> Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
>
> I wanted to create a stored procedure I could call from my web application
> to insert data into a table. If I can't use ntext as a variable for a
> stored procedure, then what do I use for the variable in the stored proc?
> The datatype of the column in the sql2000 database that the ntext variable
> would be inserting into is ntext.
>
> Here's my stored proc, if it's needed:
>
> CREATE PROCEDURE workrequest
> @empname varchar(30)
> ,@adname varchar(30)
> ,@supervisor varchar(30)
> ,@department varchar(30)
> ,@location varchar(30)
> ,@roomarea varchar(150)
> ,@request ntext
> ,@dtrequest smalldatetime
> ,@ipaddress varchar(15)
> AS
> INSERT INTO TWorkRequest
> (empname
> ,adname
> ,supervisor
> ,location
> ,roomarea
> ,department
> ,request
> ,dtrequest
> ,ipaddress)
> VALUES
> (@empname
> ,@adname
> ,@supervisor
> ,@location
> ,@roomarea
> ,@department
> ,@request
> ,@dtrequest
> ,@ipaddress)

Arnie Rowland wrote:
> You could use either varchar(8000) or nvarchar(4000).
>
> If your text value is longer than that, you could use several
> varchar(8000)/nvarchar(4000) parameters and concatenate them together when
> you do the INSERT.
>

"Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:%23FHUObN2GHA.4300@TK2MSFTNGP03.phx.gbl...
> Arnie Rowland wrote:
>> You could use either varchar(8000) or nvarchar(4000).
>>
>> If your text value is longer than that, you could use several
>> varchar(8000)/nvarchar(4000) parameters and concatenate them together
>> when you do the INSERT.
>>
>
> Yea, I didn't even think of concatenation. Of course, I don't know how to
> do that but I'll look into it on BOL.
>
> For some reason I was thinking that varchar had a limit of 255 characters.
> Now why did I think that?? I'm assuming that 8000 characters is the max?
>
> Thanks for your input.

Arnie Rowland wrote:
> The max sizes for SQL 2000 are varchar(8000) or nvarchar(4000).
>
> With SQL 2005, there is a new varchar(max), which has a 2 GB size limit.
>

"Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:uQs2KL32GHA.5056@TK2MSFTNGP02.phx.gbl...
> Arnie Rowland wrote:
>> The max sizes for SQL 2000 are varchar(8000) or nvarchar(4000).
>>
>> With SQL 2005, there is a new varchar(max), which has a 2 GB size limit.
>>
>
> 2GB Size limit for varchar?? That almost defeats the purpose of the text
> type. I could use varchar in place of text on everything then, so it
> sounds.