Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > ntext invalid for local variables
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 17th August 13:38
jim in arizona
External User
 
Posts: 1
Default ntext invalid for local variables


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)
  Reply With Quote


  sponsored links


2 17th August 13:38
jim in arizona
External User
 
Posts: 1
Default ntext invalid for local variables


Please Disregard. I was running the procedure wrong.
  Reply With Quote


  sponsored links


3 17th August 13:38
arnie rowland
External User
 
Posts: 1
Default ntext invalid for local variables


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.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
  Reply With Quote
4 17th August 16:41
jim in arizona
External User
 
Posts: 1
Default ntext invalid for local variables


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.
  Reply With Quote
5 17th August 16:41
arnie rowland
External User
 
Posts: 1
Default ntext invalid for local variables


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.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
  Reply With Quote
6 17th August 20:50
jim in arizona
External User
 
Posts: 1
Default ntext invalid for local variables


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.
  Reply With Quote
7 17th August 20:50
arnie rowland
External User
 
Posts: 1
Default ntext invalid for local variables


I believe that text/ntext is now deprecated...

Varchar(max) allows indexing, searching, using most string manipulation
functions, etc. You should immediately switch over and stop using
text/ntext.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666