March 25, 2004 at 6:04 pm
I'm trying to insert a large piece of text into a text field.. The insert command does not return any error, and creates the new record, but when I view the field in the inserted record, it comes back truncated at about 20251 bytes. (These are entries for an application errror log, and the entries can sometimes be quite a bit larger)
I've tried setting TEXTSIZE to a larger value, but that doesn't seem to help. What am I missing?
March 25, 2004 at 6:14 pm
What are you using to view them and how are you handling you insert specifically?
March 25, 2004 at 6:23 pm
I've tried two ways of inserting:
1. via an odbc connection
2. directly in the query analyzer
And two ways of viewing (both in the query analyser):
1. select * from nmsErrorLog where elpk = 3
2.
DECLARE @val varbinary(16)
SELECT @val = textptr(listing) FROM nmsErrorLog
WHERE elpk =3
READTEXT nmsErrorLog.listing @val 0 20251
(if I try to increase the 20251 value, it errors as being larger than the actual length)
March 25, 2004 at 6:35 pm
Oh.. and in both cases, the insert command (either typed directly into the query analyser or sent via the odbc connection) is just:
Insert into nmsErrorLog (error,listing)
Values
('Test Long Error','Error # 16.....') where the listing value is the long one..
March 25, 2004 at 6:41 pm
You don't have any words in you error statement such as
DON'T
HAVEN'T
HASN'T
or other ' containing word do you?
And what laguage are you using to write the errors to the ODBC connection? I suggest use OLE provider and maybe wrapping in an SP so you can use and ADO Procedure Command so you can use parameters to ensure your string goes thru properly. I think yor insert string might be going outside the buffer length somehow.
March 26, 2004 at 6:47 am
If you can enter the messages outside of SQL Server you can use the tool textcopy.exe which is used to load Pubs DB.
March 26, 2004 at 8:39 am
Are you sure the truncation occurs during the insert and not the subsequent retrieval? If you know the length the error listing should be, run
SELECT Datalength(listing) AS Listing_Length FROM nmsErrorLog WHERE elpk = 3
just to make sure.
** Note that the largest number of characters viewable in query analyzer is 8192 bytes (Tools | Options | Results).
But using the code you originally posted, I would do something like this to view the text in Query analyzer (note that you will get a column header between each block of text that is printed):
DECLARE @val varbinary(16), @textlen int
DECLARE @blocksize int, @bytesRemaining int, @offset int
SET TEXTSIZE 1000000
SET NOCOUNT ON
SELECT @val = textptr(listing)
FROM narratives
WHERE WHERE elpk = 3
SET @textlen = (SELECT Datalength(listing) FROM nmsErrorLog WHERE elpk = 3)
SET @blocksize = 8192
SET @bytesRemaining = @textlen
SET @offset = 0
WHILE @bytesRemaining > 0
BEGIN
IF @bytesRemaining < @blocksize
SET @blocksize = @bytesRemaining
READTEXT nmsErrorLog.listing @val @offset @blocksize
SET @offset = @offset + @blocksize
SET @bytesRemaining = @bytesRemaining - @blocksize
END
Mike
March 26, 2004 at 10:07 am
Thanks guys..
Mike nailed it..
"** Note that the largest number of characters viewable in query analyzer is 8192 bytes (Tools | Options | Results)."
That was the problem (I'm still new to SQL Server and hadn't even thought of this..) And your code for displaying the whole text in the query works great (with minor mods)
Thanks
Ilmar
March 26, 2004 at 10:15 am
Not in this one..
Language for the odbc connection is Visual FoxPro.. we are in the process of porting a large application from VFP as a back end to SQL Server (mostly for security reasons, even though we will probably take a performance hit..). So far we haven't had any problems with using the odbc connection..
Thanks,
Ilmar
March 26, 2004 at 10:22 am
Thanks, I'll take a look at it...
So many fun things to learn about..
Ilmar
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply