+ Reply to Thread
Results 1 to 8 of 8

ntext invalid for local variables

  1. 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)

  2. Re: ntext invalid for local variables [FIXED]

    Please Disregard. I was running the procedure wrong.

    Jim in Arizona wrote:
    > 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)


  3. Re: 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


    "Jim in Arizona" 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)




  4. Re: ntext invalid for local variables

    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.

  5. Re: 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


    "Jim in Arizona" 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.




  6. Re: ntext invalid for local variables

    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.

  7. Re: 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


    "Jim in Arizona" 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.




  8. Re: ntext invalid for local variables

    Looks like your clock is a little bit "off". (10/22/2007)

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

    Most good judgment comes from experience.
    Most experience comes from bad judgment.
    - Anonymous


    "liangsuzhen" wrote in message
    news:eAVpctp7GHA.4996@TK2MSFTNGP03.phx.gbl...
    >
    > "Arnie Rowland" дʼ
    > news:eM%23qnqE2GHA.4176@TK2MSFTNGP06.phx.gbl...
    >> 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
    >>
    >>
    >> "Jim in Arizona" 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)

    >>
    >>

    >
    >




+ Reply to Thread