need help with error -2147217871 ODBC time out

[DougP]

This is connected to a SQL server 2000 db.
I have a vb6 program that gets this -2147217871 ODBC error occasionally. I am “resuming” back to the line that caused it.

But if the code has an insert statement I think its inserting as many records again.
Say it inserts 40,000 records then gets the error it resumes to inserting, I think it tries to insert those same 40,000 plus the rest it didn’t get. So I end up with way to many which are duplicates.

How do I get around trapping this error so it works correctly?

strSQL=” INSERT into [BambooAnalysis].[dbo].[tblAnalysis01] (facid, encounterid, pat...

    Set cmdCommand = New adodb.Command
    With cmdCommand
        .ActiveConnection = gcnDataRepository
        .CommandTimeout = 0
        .CommandText = strSQL
        .CommandType = adCmdText
        .Execute       ‘<<<<<<<<< Resume comes back here, this caused the error
    End With

    Select Case Err.Number
        Case -2147217871
            Resume
…

PS its attempting to insert 140,000 records



DougP, MCP, A+

 

[Nagrom]

Is each insert a separate statement? That shouldn't be happening if they are all being inserted by a single statement because it is one transaction and none of the new records should be committed in the database if the transaction did not complete. You can ensure that happens by putting the insert into a transaction and rolling back the transaction if you get the error, but with a single statement you shouldn't have to do that.

 

[WZUP]

try ...ConnectionTimeout = 0

 

[DougP]

No this is one insert statement.
Nagrom, if I understand what you are saying. if the error occurs it is not committing records so I don’t end up with duplicates?
Do you know how would I code the rollback?

we have ConnectionTimeout =0 already
maybe there is another reason for the error


DougP, MCP, A+

 

[gmmastros]

You have 1 insert statement that is inserting 140,000 records? Depending on how you do this, it could be very slow. There are other methods that you can use to load data in to SQL Server.

Are you inserting records in to a table from other tables within the same database? Are you loading data that is gathered externally (access database, excel file, ASCII file, etc...)? Are you loading data from another linked server?

My point is... 140,000 records really isn't that much. Depending on the method used, you can probably get it to take less than 30 seconds.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[macleod1021]

I'm not seeing where you're closign your ADODB connection. From looking at your code, I'm assuming that you're looping through something to change the parameters of the insert. You should be setting your new connection to nothing each pass. Theres a limited number of ADO connections that you can have open before your computer freaks. I don't remember the number, but it's typically a high number. I'm almost positive it's < 140k. Try that and let me know if it works.

 

[DougP]

gmmastros, the data is being inserted from one other SQL server database and also from other tables in this same database.
here is the whole loop. First we open the recordset, which contains some 10,000 records then loop through each record and create 25 more records from each record with the wnd_time_windows column equal to a multiple of 7

   strSQL = "Select a.facid,a.encounterid,a.patientid,a.woundid,b.transfer_12weekflag,
b.fup_2weekflag,b.firstassessment,b.lastassessment,a.duration,
a.wnd_baseline_area from [" & _
        gstrBambooAnalysisDBName & "].[dbo].[tblAnalysis02] a inner join [" & gstrBambooAnalysisDBName & "].[dbo].[tblAnalysis00] b on " & _
        "b.facid = a.facid and b.encounterid = a.encounterid and b.patientid = a.patientid " & _
        "inner join [" & gstrBambooAnalysisDBName & "].[dbo].[tbletiology] e on e.facid = a.facid and e.encounterid = a.encounterid and e.woundid = a.woundid " & _
        "where a.wnd_baseline_area is not null and e.etiologyid in (1,2,3,4,5,6,7,8,9,10,11) " & _
        "order by a.facid,a.encounterid,a.patientid,a.woundid "
        
    Set rsLoadTable = New adodb.Recordset
    rsLoadTable.CursorLocation = adUseClient
        
    rsLoadTable.Open strSQL, gcnBambooAnalysis, adOpenStatic, adLockOptimistic
    
    '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    ' get total records to display
    '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    frmBambooStats.lblTotalRecords.Caption = rsLoadTable.RecordCount
    
     Do While Not rsLoadTable.EOF
     
          'Create a record for every wnd_time_window
          
          If rsLoadTable.Fields("transfer_12weekflag") Then
                weekflag12 = 1
          Else
                weekflag12 = 0
          End If
          
          If rsLoadTable.Fields("fup_2weekflag") Then
                weekflag2 = 1
          Else
                weekflag2 = 0
          End If
                
          For X = 0 To 24
            strSQL = "Insert into [" & gstrBambooAnalysisDBName & "].[dbo].[tblAnalysis07] " & _
                 "(facid, encounterid, patientid, woundid, duration, area, transfer_12weekflag, fup_2weekflag, " & _
                 "FirstAssessment, LastAssessment, wnd_time_windows) Values (" & _
                 rsLoadTable.Fields("facid") & ", " & rsLoadTable.Fields("encounterid") & ", " & _
                 rsLoadTable.Fields("patientid") & ", " & rsLoadTable.Fields("woundid") & ", " & _
                 rsLoadTable.Fields("duration") & ", " & rsLoadTable.Fields("wnd_baseline_area") & ", " & _
                 weekflag12 & ", " & weekflag2 & ", '" & _
                 rsLoadTable.Fields("firstassessment") & "', '" & rsLoadTable.Fields("lastassessment") & "', " & (X * 7) & ")"
                 
                Set cmdCommand = New adodb.Command
                With cmdCommand
                    .ActiveConnection = gcnBambooAnalysis
                    .CommandTimeout = 0
                    .CommandText = strSQL
                    .CommandType = adCmdText
                    .Execute
                End With
    
                Set cmdCommand = Nothing
        Next
       
       lngRecordCount = lngRecordCount + 1
       If (rsLoadTable.AbsolutePosition Mod 100) = 0 Then
            frmBambooStats.lblRecordCount.Caption = lngRecordCount
       End If

       DoEvents
       rsLoadTable.MoveNext
    Loop

    rsLoadTable.Close

DougP, MCP, A+

 

[gmmastros]

You will gain tremendous speed improvements by moving this in to a sql server stored procedure. I would expect the entire process to take less than a second. (hint: you'll be able to remove the pseudo-progress bar where you set the form caption).

The tricky part here is to duplicate the '25 records per record' part. Using a little set based logic, we should be able to duplicate it.

Hopefully you have a production database (i.e. NOT LIVE) so that you can play around with queries without affect the real data.

First, let's create a select statement that returns the records you want. After getting the select to return the correct records, then we will convert it in to an insert command. To create 25 records for each record in the original query, we can create a dummy table and do a left outer join.

All of this code is meant for the query analyzer. After we get the stored procedure correct, then you can use VB to call the stored procedure.

Select a.facid,
       a.encounterid,
       a.patientid,
       a.woundid,
       b.transfer_12weekflag,
       b.fup_2weekflag,
       b.firstassessment,
       b.lastassessment,
       a.duration,
       a.wnd_baseline_area ,
       Numbers.wnd_time_windows * 7 As wnd_time_windows
from   [[!]gstrBambooAnalysisDBName[/!]].[dbo].[tblAnalysis02] a 
       inner join [[!]gstrBambooAnalysisDBName[/!]].[dbo].[tblAnalysis00] b 
          on  b.facid = a.facid 
          and b.encounterid = a.encounterid 
          and b.patientid = a.patientid 
       inner join [[!]gstrBambooAnalysisDBName[/!]].[dbo].[tbletiology] e 
          on  e.facid = a.facid 
          and e.encounterid = a.encounterid 
          and e.woundid = a.woundid 
       Cross Join 
          (Select 0 As wnd_time_windows Union All
           Select 1 Union All
           Select 2 Union All
           Select 3 Union All
           Select 4 Union All
           Select 5 Union All
           Select 6 Union All
           Select 7 Union All
           Select 8 Union All
           Select 9 Union All
           Select 10 Union All
           Select 11 Union All
           Select 12 Union All
           Select 13 Union All
           Select 14 Union All
           Select 15 Union All
           Select 16 Union All
           Select 17 Union All
           Select 18 Union All
           Select 19 Union All
           Select 20 Union All
           Select 21 Union All
           Select 22 Union All
           Select 23 Union All
           Select 24 Union All
         ) As Numbers
where  a.wnd_baseline_area is not null 
       and e.etiologyid in (1,2,3,4,5,6,7,8,9,10,11) 
order by a.facid,
       a.encounterid,
       a.patientid,
       a.woundid

Change the server names (in red) and run this query in Query Analyzer. Don't put the connection string to the linked server, just the server name. Does it produce the correct results?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DougP]

thank you so much!!!
I need to try it out.

DougP, MCP, A+