[thelist] 8k Row limits must Go!

[Anthony Baratta]

At 12:24 PM 7/11/2002, Rob Smith wrote:

>my 21k words still doesn't show up...

You might look into this: (Stolen from the MS SQL On-Line Books)
Also - make sure all your data is going in.

"When this method is used in Transact-SQL scripts, stored procedures, and
triggers, it works only for relatively short values. If the length of the
data is longer than the length specified in SET TEXTSIZE, you must use
increase TEXTSIZE or use another method. The current TEXTSIZE setting is
reported by the @@TEXTSIZE function and is changed with the SET TEXTSIZE
statement:

SET TEXTSIZE 64512

The default setting for TEXTSIZE is 4096 (4 KB). This statement resets
TEXTSIZE to its default value:

SET TEXTSIZE 0

The full amount of data is returned if the length is less than TEXTSIZE.

The DB-Library API also supports a dbtextsize parameter that controls the
length of ntext, text, and image data that can be selected. The Microsoft
OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically
set @@TEXTSIZE to its maximum of 2 GB."

OR this

"Managing Long Data Types

Long data types include ntext, text, and image data types. ntext, text, and
image data can be so large that they cannot be retrieved in one operation
or fit into memory. If the long data can fit into memory, the Value
property of the Field object can be used to retrieve all the data in one
operation. If the long data is too large to fit into memory, the data must
be retrieved or written in chunks. You can manipulate long data in chunks
through the Field object or through the Parameter object.

The Field object allows you to write and read long data through the
Recordset object. The AppendChunk method of the Field object allows you to
append data at the end of the current data when the query has already been
executed. The GetChunk method allows you to read the data in chunks.

With the Parameter object, there is no GetChunk method, and there is no
Recordset object when you are dealing with long data at run time. With the
Parameter object, long data is bound at run time and executed with the
Command object.

There are some restrictions for long data when using MSDASQL. If no server
cursor is used, all long columns must be to the right of all nonlong
columns. If there are multiple long columns, the long columns must be
accessed in order (from left to right).

This example shows how to use ADO with SQLOLEDB to read and write image
data. The critical routines are the while loops that copy the long data
(image) to a variable and write the variable to a record in chunks (using
the GetChunk and AppendChunk methods).
Before setting up the destination table in this example, make sure to run
the sp_dboption stored procedure:

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True'

The destination table is a copy of the pub_info table in the pubs database.
Create the table by running:

USE pubs
SELECT * INTO pub_info_x
    FROM pub_info
GO

The pub_info_x table is the destination table in which the long data will
be inserted.
The ADO code is:

Public Sub AppendChunkX()

    Dim cn As ADODB.Connection
    Dim rstPubInfo As ADODB.Recordset
    Dim strCn As String
    Dim strPubID As String
    Dim strPRInfo As String
    Dim lngOffset As Long
    Dim lngLogoSize As Long
    Dim varLogo As Variant
    Dim varChunk As Variant

    Const conChunkSize = 100

    ' Open a connection.
    Set cn = New ADODB.Connection
    strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"

    cn.Provider = "sqloledb"
    cn.Open strCn

    'Open the pub_info_x table.
    Set rstPubInfo = New ADODB.Recordset
    rstPubInfo.CursorType = adOpenDynamic
    rstPubInfo.LockType = adLockOptimistic
    rstPubInfo.Open "pub_info_x", cn, , , adCmdTable

    'Prompt for a logo to copy.
    strMsg = "Available logos are : " & vbCr & vbCr

    Do While Not rstPubInfo.EOF
       strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
         Left(rstPubInfo!pr_info,
          InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
       rstPubInfo.MoveNext
    Loop

    strMsg = strMsg & "Enter the ID of a logo to copy:"
    strPubID = InputBox(strMsg)

    ' Copy the logo to a variable in chunks.
    rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
    lngLogoSize = rstPubInfo!logo.ActualSize
    Do While lngOffset < lngLogoSize
       varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
       varLogo = varLogo & varChunk
       lngOffset = lngOffset + conChunkSize
    Loop

    ' Get data from the user.
    strPubID = Trim(InputBox("Enter a new pub ID:"))
    strPRInfo = Trim(InputBox("Enter descriptive text:"))

    ' Add a new record, copying the logo in chunks.
    rstPubInfo.AddNew
    rstPubInfo!pub_id = strPubID
    rstPubInfo!pr_info = strPRInfo
    lngOffset = 0   ' Reset offset.

    Do While lngOffset < lngLogoSize
       varChunk = LeftB(RightB(varLogo, lngLogoSize - _
         lngOffset),conChunkSize)
       rstPubInfo!logo.AppendChunk varChunk
       lngOffset = lngOffset + conChunkSize
    Loop

    rstPubInfo.Update

    ' Show the newly added data.
    MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
      "Description: " & rstPubInfo!pr_info & vbCr & _
      "Logo size: " & rstPubInfo!logo.ActualSize

    rstPubInfo.Close
    cn.Close

End Sub

See Also

ADO and Long Data Types (C++)
ADO and Long Data Types (Visual Basic)
ADO and Long Data Types (Web)"

--
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."