Trying to use field with 8000 characters - Are there limitations

Question

Post reply

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
-------------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Vekaria Ten Centuries Points: 1157 More actions · Answer 1

Ramesh Vekaria

Ten Centuries

Points: 1157

January 29, 2006 at 5:46 am

#617647

Sorry, my colleague has posted this thread earlier.

Steven Bense Valued Member Points: 72 More actions · Answer 2

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 · Answer 3

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..........'