Solved
Inserting data into a SQL table while avoiding the 'System.OutOfMemoryException' error.
Hi everyone.
I have this error when trying to insert data into my SQL table 'System.OutOfMemoryException'. This occurs when I use .base to retrieve all my departments.
The following is how I ente...
Hi Marco,
Instead of running the query many times inside a loop, use the loop to curate a single query and run the query once at the end:Dim sqlInsert As New StringBuilder() Dim insertVals As New List(Of String) 'start the query sqlInsert.AppendLine("INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, [View], Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES ") For Each isRow As DataRow In dt.Rows 'add each insert group insertVals.Add($"(1, {Amount}, '{Annotation}', '{SourceRule}', '{DriverRule}', '{Type}', '{StepDesc}', '{TargetRule}', '{AllocRule}', {Generation}, '{Cube}', '{Entity}', '{Consolidation}', '{Scenario}', '{Time}', '{View}', '{Account}', '{Flow}', '{Origin}', '{Ic}', '{UD1}', '{UD2}', '{UD3}', '{UD4}', '{UD5}', '{UD6}', '{UD7}', '{UD8}')") Next 'finish the query sqlInsert.AppendLine(String.Join($",{Environment.NewLine}", insertVals.ToArray())) 'run the query once for all inserts. Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si) BRApi.Database.ExecuteSql(dbConnApp, sqlInsert.ToString(), True) End UsingJust to append to RobbSalzmann answer, you might want to consider using the sql function call, that includes parameters, and if you still get time-outs, use the "useCommandTimeOutLarge" feature
