Itgenspr019: Could not bulk insert into SQL Server: Cannot access destination table

Vragen
, ,
1

Bij het laden van data uit Roller via een bulk insert naar SQL Server krijgen we de volgende error:

itgenspr019: Could not bulk insert 3,000 rows into the table 'PC999_RLLR.dbo.acme_revenues_new'.

Cannot access destination table 'PC999_RLLR.dbo.acme_revenues_new'.

The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.
2020-12-29 18:44:45.941 Error itgenspr019: System.Data.SqlClient.SqlException
System.InvalidOperationException
InvantiveSystemException
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlConnection.ValidateAndReconnect(Action beforeDisconnect, Int32 timeout)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)

De aanroepende code is:

declare
  l_start_date_bk date;
  l_start_date_rev date;
begin
  for r_lcn
  in
  ( select /*+ ods(false) */ *
  from   acme_location_load_status_revenues_new_r@sqlserver 
  )
  loop
     l_start_date_bk := coalesce(r_lcn.createddate_max_bk, r_lcn.startdate);
     l_start_date_rev := coalesce(r_lcn.createddate_max_rev, r_lcn.startdate);
     update_incremental_revenues('Revenues', 'acme_revenues_new@sqlserver', r_lcn.location_alias, l_start_date_bk, l_start_date_rev, r_lcn.user_id); 
 end loop;
end;

De procedure voert uiteindelijk de bulk insert uit via execute immediate:

...
    l_stmt := 'bulk insert into '  || p_target_table_name
    || chr(13) || ' ( user_id, location, acme$modifieddate, bookingpaymentid, bookingreference, receiptnumber, productid, producttype, ticketid, transactiondate, ticketunitcost, tickettransactionvalue, discount, discountcode, transactionfeeamount, taxpayable, taxonfundsreceived, taxpercent, fundsreceived, accountsreceivable, recogniseddiscount, feerevenue, eventtype, eventdate, customerid, netrevenue, transactionlocation, paymenttype, deferredrevenue, deferredrevenueother, deferredrevenuegiftcards, externalpaymentreference'
    || chr(13) || ')'
    || chr(13) || 'select ' || '''' || p_user_id || ''''
    || chr(13) || ',   ' || '''' || p_location_alias || ''''
    || chr(13) || ',   to_char(sysdate, ''YYYYMMDDHH24MISS'')'
    || chr(13) || ',   tbd.bookingpaymentid'
    || chr(13) || ',   tbd.bookingreference'
    || chr(13) || ',   tbd.receiptnumber'
    || chr(13) || ',   tbd.productid'
    || chr(13) || ',   tbd.producttype' 
    || chr(13) || ',   tbd.ticketid'
    || chr(13) || ',   tbd.transactiondate'
    || chr(13) || ',   tbd.ticketunitcost'
    || chr(13) || ',   tbd.tickettransactionvalue'
    || chr(13) || ',   tbd.discount'
    || chr(13) || ',   tbd.discountcode'
    || chr(13) || ',   tbd.transactionfeeamount'
    || chr(13) || ',   tbd.taxpayable'
    || chr(13) || ',   tbd.taxonfundsreceived'
    || chr(13) || ',   tbd.taxpercent'
    || chr(13) || ',   tbd.fundsreceived' 
    || chr(13) || ',   tbd.accountsreceivable'
    || chr(13) || ',   tbd.recogniseddiscount'
    || chr(13) || ',   tbd.feerevenue'
    || chr(13) || ',   tbd.eventtype'
    || chr(13) || ',   tbd.eventdate'
    || chr(13) || ',   tbd.customerid'
    || chr(13) || ',   tbd.netrevenue'
    || chr(13) || ',   tbd.transactionlocation'
    || chr(13) || ',   tbd.paymenttype'
    || chr(13) || ',   tbd.deferredrevenue'
    || chr(13) || ',   tbd.deferredrevenueother'
    || chr(13) || ',   tbd.deferredrevenuegiftcards'
    || chr(13) || ',   tbd.externalpaymentreference'	
    || chr(13) || 'from items@inmemorystorage items join Revenues@'  || p_location_alias || '('''','''',items.bookingreference) tbd'
    ;
    execute immediate l_stmt;   
end;

Hoe kan ik deze error voorkomen?

2

Om te beginnen kun je varieren met de instelling van de SQL Server driverattribuut bulk-insert-page-size-rows van Invantive SQL. Die staat standaard op 3.000. Ik verwacht er echter weinig heil van. Dit soort errors ontstaan omdat Azure SQL Server even “geen zin” heeft om je query of DML uit te voeren. Bijvoorbeeld omdat er een interne herconfiguratie plaatsvindt.

In een heel aantal scenario’s heeft Invantive SQL als voordeel dat het acties automatisch opnieuw probeert een aantal keren. Maar dat kan niet altijd. Bij een bulk insert is niet bekend of er rijen geladen zijn en hoeveel. De transactie is dan niet herstartbaar en Invantive SQL geeft dan voorrang aan de data-integriteit boven gebruiksgemak.

De andere opties om deze error op te lossen zijn:

  • Ticket via Azure.
  • Controleer database logging.
  • Beperken looptijd.
  • Herstartbaar maken transactie.

Ticket via Azure

Je kunt dit soort problemen via jullie Azure support- en consultancykanaal voorleggen, eventueel met de tracebestanden van Invantive voor volledige details.

Database logging

Kijk in de event_log en database log of er bijzonderheden zijn. Soms kun je hieruit inspiratie halen wat er fout gegaan is.

Beperken looptijd

De kans op Azure verbindingsissues is groter als de verbinding langdurig gebruikt wordt. Probeer de werking te versnellen of de transactie op te knippen (partitioneren) in meerdere losse transacties bijvoorbeeld door gegevens in SQL Server per partitie te laden of per dag i.p.v. per week.

Herstartbaar maken

Tenslotte: overweeg het inbouwen van een retry logica. Een uitgangspunt daarvoor is te lezen op Working with transient errors - Azure SQL Database | Microsoft Learn.

Als ik de applicatielogica zo doorgrond, dan zorgt deze code voor incrementeel laden. Omdat de einddatum niet opnieuw ingesteld zal worden als goed is totdat het laden klaar is, kun je eerst het deel van de rijen die reeds geladen zijn maar niet volledig weghalen. Daarna gewoon opnieuw proberen in de volgende job. Eventueel in combinatie met verkleinen te laden datavolume door partitionering of bijvoorbeeld minder dagen.