Entering text more than 8000 characters in SQL server

Question

Entering text more than 8000 characters in SQL server

ritesh-296675

Say Hey Kid

Points: 678
More actions
January 25, 2006 at 4:22 am

#91392

Hi , I'm a new developer and I'm looking to write text more than 8000 characters.
Below is the store procedure . It does work but displays a NULL value in the newsText field where the text needs to be more than 8000.
Trying to read the value in the @@identity and then use that to add the text in.
HELP!!!
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 8 posts - 1 through 7 (of 7 total)

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

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 1

Variables of the datatype TEXT are not supported in SQL Server 2000, although the engine seems to accept them as parameter declarations. How are you calling this procedure? My guess is that it is implicitly converting @NewsText to varchar(8000), the largest allowable varchar size.

String constants larger than 8000 characters are possible. Be sure to escape any single quotes ( 'O'Brian' --> 'O''Brian' )

Then you may need to dynamically construct your WRITETEXT code, probably in a client language.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Ignore previous post.

Variables of the datatype TEXT are supported in SQL Server 2000 in the way you use it - as a parameter for SP.

Probably problem is @@identity.

Read BOL about it and try to use SCOPE_IDENTITY instead.

And why not to use just this?

INSERT INTO tblNews

(NewsDate,NewsTitle,NewsYear, PageID, NewsText )

SELECT @newsDate, @newsTitle, @newsYear, 'News', @NewsText

_____________
Code for TallyGenerator

ritesh-296675 Say Hey Kid Points: 678 More actions · Answer 3

Thanks for your help. I'm not going to use this .

But I would be greatful if you could help me with the following:

When runing in analyser i get an error message.

Server: Msg 7133, Level 16, State 2, Procedure uspAddNewsRelease, Line 36

NULL textptr (text, ntext, or image pointer) passed to WriteText function.

HELP! dont know why its adding null values to this field

CREATE PROCEDURE dbo.uspAddNewsRelease

@newsText text

As

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

WRITETEXT tblNews.NewsText @ptrval @newsText

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

COMMIT

return @@identity

GO

Giles McArdell SSC Eights! Points: 875 More actions · Answer 4

It has been a while since I have dealt with Text data but looking at the SQL:

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

FROM tblNews

My initial thoughts would be - 1) does tblNews contain any rows? (if not then it cannot return a pointer to the text as it does not exist). 2) Are there any NULLs in the NewsText Column? I am not too sure what TEXTPTR() would return for a NULL, but from reading BOL I would imagine it would be NULL (I think you have to put something not NULL in the column before it gets a pointer). 3) If tblNews has more than 1 row then what do you want @ptrval to point at? IE you should add a WHERE clause to return just the row you want.

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

ritesh-296675 Say Hey Kid Points: 678 More actions · Answer 5

Thanks - i will look i to this.

Would you know what size is given to adBSTR -

this is used in the parameters of the asp page

cmd.Parameters.Append cmd.CreateParameter ("@newsText", adBSTR, adParamInput, 160000, newsText)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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

FROM tblNews

WHERE ????

_____________
Code for TallyGenerator

ritesh-296675 Say Hey Kid Points: 678 More actions · Answer 7

STRIPPING HTML DATA HELP PLEASE!

What I'm now trying to do:

Ok - i want a user to enter details(text) in a form. This form will have a html editor. This is similar to the form used when adding a new thread.

The text in most cases is copied from word to the html editor form. This creates a lot of crap html and exceeds the amount of text.

IS there anyway once the user has entered the text the html is still used but stripped out so the html is not written to the database.This will solve the problem of the 8000 characters.

Thank You - PLEASE HELP ME!