Trying to use field with 8000 characters - Are there limitations

• Ramesh Vekaria

  Ten Centuries

  Points: 1157

  More actions

  January 29, 2006 at 5:38 am

  #170739

  I would be very gratef if someone can advise on the following:

   

  I'm trying to use text with more than 8000 characters if possible.

   

  Below is the store procedure - everything works apart from the newsText field - it adds a NULL value regardless of what is entered.

  ------------------------------------------------------------

   

  CREATE PROCEDURE dbo.uspAddNewsRelease

   

  @newsDate varchar(25),

  @newsTitle varchar(25),

  @newsYear varchar(25),

  @Newsid int,

  @NewsText text -- two parameters used

   

  As

  INSERT INTO  tblNews

    (

   NewsDate,

   NewsTitle,

   NewsYear,

   PageID

     )

   VALUES

   

    (

    @newsDate,

  --             @convertedDate,

   @newsTitle,

       @newsYear,

   'News'

       )

  select @Newsid = @@identity    --system varaiable (@@ reads the last id as an auton number  - - gets last inserted record

  BEGIN TRAN       -- means begin tranasction Need to have this for SQL server 2000

   

  --EXEC sp_dboption 'BLPT', 'select into/bulkcopy', 'true'

   

  DECLARE @ptrval binary(16)       --declare varaible  Must be 16 as we used this value later on in our paramaeter for the upload page

  SELECT @ptrval = TEXTPTR(NewsText)  --- text is wrapped in the varaible

  FROM tblNews

  where NewsID = @Newsid  ----Condition

  --select @ptrval

  WRITETEXT tblNews.NewsText @ptrval @NewsText

   

  --EXEC sp_dboption 'BLPT', 'select into/bulkcopy', 'false'

  COMMIT

   

  GO

  -------------------------------------------------------

• Ramesh Vekaria

  Ten Centuries

  Points: 1157

  More actions

  January 29, 2006 at 5:46 am

  #617647

  Sorry, my colleague has posted this thread earlier.

• Steven Bense

  Valued Member

  Points: 72

  More actions

  January 30, 2006 at 10:28 am

  #617773

  I believe there is an error in the SQL. In fact you are inserting a NULL into the NewsText field because it is not specified here in the field list.

   

  INSERT INTO  tblNews

    (  NewsDate,     NewsTitle,     NewsYear,     PageID )

   VALUES

    (  @newsDate,  @newsTitle,   @newsYear,  'News'    )

   

  Try this.

   

  INSERT INTO  tblNews

    (  NewsDate,    NewsTitle,     NewsYear,   NewsText   )

   VALUES

    (  @newsDate, @newsTitle,  @newsYear,  'News'  )

   

  'News' should end up in the NewsText field.

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  January 30, 2006 at 2:16 pm

  #617831

  I think you are over complicating things.  A stored procedure can take a text field as an argument and insert it directly into the table.

   

  create table tblNews

  (

    NewsID int identity(1,1),

    NewsDate varchar(25),

    NewsTitle varchar(25),

    NewsYear varchar(25),

    PageID varchar(25),

    NewsText text

  )

  go

  drop PROCEDURE dbo.uspAddNewsRelease

  go

  CREATE PROCEDURE dbo.uspAddNewsRelease

   

  @newsDate varchar(25),

  @newsTitle varchar(25),

  @newsYear varchar(25),

  @NewsText text -- two parameters used

   

  As

  declare @newsid int

  INSERT INTO  tblNews

  (

    NewsDate,

    NewsTitle,

    NewsYear,

    PageID,

    NewsText

  )

  VALUES

  (

    @newsDate,

    @newsTitle,

    @newsYear,

    'News',

    @NewsText

  )

  select @Newsid = @@identity    --system varaiable (@@ reads the last id as an auton number  - - gets last inserted record

  GO

  exec uspAddNewsRelease '15 Jan 2006', 'Big News',  '2006', 'some really long text..........'