I am Using node-mssql Link:

while I am inserting bulk data in mssql table it lost connection

while I am inserting data above 4 rows at a time it will throw an error

import * as SQL from "mssql";

const conn = new sql.ConnectionPool({
   user: "XXXXXXXXX",
   password: "XXXXXXXXXXX",
   server: "XXXXXXXXXXX",
   database: "TESTDATA",
   options: {
    instanceName: "XXX"
   },
   pool: {
     max: 10,
     min: 0,
     idleTimeoutMillis: 30000
   }
 });

conn.connect()

var values = [[john,1,4,80],[jenny,null,4,78],[abhi,3,4,null],[ram,4,4,90]]

const table = new sql.Table('CLASS_TABLE');
table.columns.add('NAME', sql.NVarChar(15));
table.columns.add('ROLL', sql.Int);
table.columns.add('CLASS', sql.Int);
table.columns.add('MARKS', sql.Int);

for (let i = 0; i < values.length; i++) {
      let row_data = values[i];
      if (row_data) {
        table.rows.add(row_data[0], row_data[1], row_data[2], row_data[3], row_data[4])
      }
    }

const request = new sql.Request(conn);
request.bulk(table, (err, result) => {
  throw err
});

Error : RequestError: Connection lost - read ECONNRESET

I am Using node-mssql Link: https://www.npmjs./package/mssql

while I am inserting bulk data in mssql table it lost connection

while I am inserting data above 4 rows at a time it will throw an error

import * as SQL from "mssql";

const conn = new sql.ConnectionPool({
   user: "XXXXXXXXX",
   password: "XXXXXXXXXXX",
   server: "XXXXXXXXXXX",
   database: "TESTDATA",
   options: {
    instanceName: "XXX"
   },
   pool: {
     max: 10,
     min: 0,
     idleTimeoutMillis: 30000
   }
 });

conn.connect()

var values = [[john,1,4,80],[jenny,null,4,78],[abhi,3,4,null],[ram,4,4,90]]

const table = new sql.Table('CLASS_TABLE');
table.columns.add('NAME', sql.NVarChar(15));
table.columns.add('ROLL', sql.Int);
table.columns.add('CLASS', sql.Int);
table.columns.add('MARKS', sql.Int);

for (let i = 0; i < values.length; i++) {
      let row_data = values[i];
      if (row_data) {
        table.rows.add(row_data[0], row_data[1], row_data[2], row_data[3], row_data[4])
      }
    }

const request = new sql.Request(conn);
request.bulk(table, (err, result) => {
  throw err
});

Error : RequestError: Connection lost - read ECONNRESET

• javascript
• sql
• node.js
• sql-server
• express

2 Answers 2

In your connection options please mention stream :true to insert multiple records.

const conn = new sql.ConnectionPool({
   user: "XXXXXXXXX",
   password: "XXXXXXXXXXX",
   server: "XXXXXXXXXXX",
   database: "TESTDATA",
   options: {
    instanceName: "XXX"
   },
   stream:true,
   pool: {
     max: 10,
     min: 0,
     idleTimeoutMillis: 30000
   }
 });

I guess there is a parameter which determines the max allowed data at a time

Iam not familiar with mssql,but in MySQL when we want to insert large data at once we will change the parameter max_allowed_packet to a large value

so check if there is a similar parameter and change it