Ogeday writes "Hi,I want to get all the data stored in an ntext field in a table but I can only get first 2048 chars using the following sp and vb code. Please help.-----------------------------CREATE PROCEDURE sp_read@length int OUTPUTASDECLARE @ptrval binary(16)SELECT @length = DATALENGTH(body), @ptrval = TEXTPTR(body) FROM mytable WHERE id = 3READTEXT mytable.body @ptrval 0 0-----------------------------Although the @length parameter includes say 13200 the recordset's content has 2048 characters (unicode data, so it's 4096 bytes only)Here's the vb code that calls the sp;----------------------------- Const conChunkSize = 100 Set conn = CreateObject("ADODB.Connection") conn.Open ConnectionString Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "sp_read" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("return", adInteger, adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("length", adInteger, adParamOutput) Set rst = cmd.Execute If Not rst Is Nothing Then If Not rst.State = adStateClosed Then If Not (rst.EOF Or rst.BOF) Then MsgBox (rst!body.ActualSize) MsgBox (rst!body.DefinedSize) lngBodySize = rst!body.ActualSize Do While lngOffset < lngBodySize varChunk = rst!body.GetChunk(conChunkSize) vbody = vbody & varChunk lngOffset = lngOffset + conChunkSize Loop End If rst.Close End If Set rst = Nothing End If MsgBox ("length = " & cmd.Parameters(1).Value) Set cmd = Nothing conn.Close Set conn = Nothing-----------------------------Although length is 13200, rst!body.ActualSize returns 4096Thank you,Ogeday"
Stored Procedure returns text field with max. 2048 chars 
Posted - 2001-11-28 : 09:17:36
