Error inserting large text into SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error inserting large text into SQL 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-09 : 08:21:35

Jarred writes "Hi,

via an ASP page I'm using a stored procedure to insert email body text into SQL 2000. The field is datatype text field.
when the text goes over a certain length. I get this error.
[Microsoft][ODBC SQL Server Driver]String data, right truncation
The text I'm trying to insert is plain text or html, and wont be huge in size but could be 10K or so.

I searched your forum for the answer but only found one post on the subject of inserting large text, and it didnt really answer the question.

here is the stored proc I'm using. it seems to work ok, until the text reaches 8k.Is this the best way to insert large text?
thanks for your help

CREATE PROCEDURE spAddDevSupportResponse

(
@IncidentID int,
@EmailFrom varchar(500),
@EmailTo varchar(500),
@EmailSubject varchar(500),
@DevSupportEngID int,
@EmailBody text,
@RawData varchar(7000)

)
AS

DECLARE @NewRecordID int

INSERT INTO DEVSUPPORTRESPONSES
(USERID, INCIDENTID, RAWDATA, EmailFrom, EmailTo, EmailSubject, DevSupportEngID, SupportIssue )
VALUES
(@UserID, @IncidentID, @RawData, @EmailFrom, @EmailTo, @EmailSubject, @DevSupportEngID, '\')

EXEC sp_dboption 'IDN', 'select into/bulkcopy', 'true'

DECLARE @ptrval binary(64)
SELECT @ptrval = TEXTPTR(SupportIssue)
FROM DevSupportResponses where recordid = @NewRecordID
WRITETEXT DevSupportResponses.SupportIssue @ptrval @EmailBody

EXEC sp_dboption 'IDN', 'select into/bulkcopy', 'false'
GO"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 12:43:10

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-09 : 12:44:36

Good link tara...

BOL Say that the textpointer needs to be defined as ninary(16) btw...

What does

SELECT @@TEXTSIZE

Return for you?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources