Out of space using GetString? 
Author Message
 Out of space using GetString?

Hi,

Here's the problem...I am extracting data from an SQLBase database
(EZCAP)...for the purpose of putting it into SQLServer 7.0....

Tried using DTS, but get too many errors...

Anyway, the getstring mthod works well up to about 1 Million records...then
I get a message that I'm out of space, which I have plenty of...

I started having issues at about 600,000...increased the paging file size...

Then noticed that it took about 2 - 21/2 minutes tio close the
connection...I believe it is merely closing out the cache....

Tried putting a cachesize property on the recordset.  Was never able to find
out if it worked...Could hold the F8 key down all day, but as soon as I let
it go....I get a type mismatch error, which I thinks means the local cache
isn't getting refilled...

Using the rdo getclipstring method is more stable, but quite a bit
slower(hard to imagine - as it has always seemed quicker)...

Below is the code.  Thanks in advance for any help and guidance..

I have tried opening and closing the text file with no effect.

Bud Dean

****************Start Code*****************

Sub testGetString()

'set the variables

Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fs As FileSystemObject, str As String
Dim A As TextStream

Dim strCnn As String
Dim l As Long
Dim rcount As Long
Dim varOutput As Variant
Dim datTime1 As Date
Dim sConFname As String
Dim sSQL As String

sConFname = "E:\VBSQLTextFiles\testClaimMasters.txt"

strCnn = "Provider=SQLBASEOLEDB;" & _
        "Initial Catalog=MON2RPT;Data Source=mon2rpt;User
Id=SYSADM;Password=SYSADM; "

'get connected

Set cnn1 = New ADODB.Connection
   cnn1.Mode = adModeRead
   cnn1.CursorLocation = adUseServer
   'cnn1.Open strCnn, , , adAsyncConnect
   cnn1.Open strCnn
   While (cnn1.State = adStateConnecting)
      Debug.Print "Opening first connection...."
   Wend

   Set rst = New ADODB.Recordset

   'rst.CacheSize = 100

'Open the recordset

   rst.Open "Select * " & _
      "FROM Claim_Masters ", cnn1, adOpenForwardOnly, adLockReadOnly

'Set the fso stuf

 Set fs = CreateObject("Scripting.FileSystemObject")

 Set A = fs.OpenTextFile(sConFname, ForAppending, True)

   varOutput = ""

'start looping through te recordset

 Do While Not rst.EOF

      varOutput = rst.GetString(, 50, , Chr(10), "Null")

  rcount = rcount + 50

  If rcount Mod 10000 = 0 Then

   Debug.Print rcount & "  " & Format(Now() - datTime1, "hh:nn:ss")
  End If

  'added doevents to see if it helped
  DoEvents

 A.WriteLine CStr(varOutput)
 'tried resetting the variable
 varOutput = ""
 Loop

A.Close
rst.Close
cnn1.Close
Set rst.ActiveConnection = Nothing
Set cnn1 = Nothing

End Sub

***********************End Code********************



Fri, 04 Apr 2003 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Space Space Space Space Space Space Space

2. Space Space Space Space Space Space Space

3. Space Space Space Space Space Space Space

4. Using ADO GetString with MSHFlexgrid - problem with memo field

5. university EMR send outs

6. on the outs

7. Learning the ins and outs of Peek and Poke

8. How to handle print outs in VB?

9. Filesystem Time outs

10. Session Variable Time-outs

11. Time-outs

12. Need help with time outs

 

 
Powered by phpBB® Forum Software