node-mssql: Connection Error: Connection is Closed

Hi…

I’m able to access my MSSQL from MSQL Studio. defined my index.js file as below, but it keeps give me this error:

{ [ConnectionError: Connection is closed.] name: ‘ConnectionError’, message: ‘Connection is closed.’, code: ‘ECONNCLOSED’}

what wrong I’m doing, if below is my code, thanks

 var sql = require('mssql'); 
 var config = { user: 'sa', password: 'bk123', server: 'HASN-BK\\SQLEXPRESS', database: 'BK',
stream: true }

  sql.connect(config, function(err) {
 var request = new sql.Request();
 request.stream = true; 
request.query('SELECT * from emp');

request.on('recordset', function(columns) {
    console.log(columns);
    // Emitted once for each recordset in a query
});

request.on('row', function(row) {
    console.log(row);
    // Emitted for each row in a recordset
});

request.on('error', function(err) {
    console.log(err);
    // May be emitted multiple times
});

request.on('done', function(returnValue) {
    // Always emitted as the last one
});

});

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 40

Most upvoted comments

I have the same issue when I run 2 or more queries one right after the other

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

benoror on May 19, 2015

Worked fine for me, better use a global connection rather connecting to the databse each time you have to make a request to avoid the ‘Connection is closed Error’.

guillaumedardenne on May 20, 2015

I’m finding rampant “Connection is Closed” errors when attempting to paralellize multiple requests using the native Promise.all.

For example if I try to run 4 queries in parallel to SELECT from the same table, the first Request in the Promise.all reports the error.

If I try to run 4 INSERTs requests in parallel into the same table (yes I’m familiar with bulk), the first Request in the Promise.all array reports the error, but the last Request succeeds with insertion (as verified by direct SQL calls to the server).

I believe this bug is related to the Tedious Bug.

Shockolate on Dec 29, 2016

I found a solution 😃 Ignore the samples on the page and only use 1 connection:

var config = { user: ‘xxxxx’, password: 'xxxxxxxxx, server: ‘servfername.database.windows.net’, // You can use ‘localhost\instance’ to connect to named instance database: 'db-name, options: { encrypt: true // Use this if you’re on Windows Azure } }

var connection = new sql.Connection(config); connection.connect();

var request = new sql.Request(connection); request.input(‘myEmail’, sql.VarChar, req.params.user); request.input(‘myPass’, sql.VarChar, req.params.pass); var sqlquery = “SELECT * From USERTable WHERE Email=@myEmail AND Password=@myPass”; request.query(sqlquery, function (err, recordset) { if (err) res.json(err); else res.json(recordset); });

GalKlein on May 19, 2015

@dw1284’s solution fixed the “Connection is closed” error for me.

Changed from:

sql.connect(dbConfig).then(function () {
      new sql.Request()
        .execute(storedProcName).then(function (recordset) {

Changed to:

sql.connect(dbConfig).then(function (connection) {
      new sql.Request(connection)
        .execute(storedProcName).then(function (recordset) {

chriscrowley on Jan 23, 2017

I recently started getting connection closed issues as well. I’ve never closed any connection in my application code. Should I? My requests typically look like this:

var connection = new sql.Connection(cred, function(err) {
  var request = new sql.Request(connection);
  request.query(query).then(function(recordset) {
    // Do success stuff here
  }).catch(function(err) {
    // Handle errors here
  });
});

Should I rather do like this?

var connection = new sql.Connection(cred, function(err) {
  var request = new sql.Request(connection);
  request.query(query).then(function(recordset) {
    // Do success stuff here
    connection.close();
  }).catch(function(err) {
    // Handle errors here
    connection.close();
  });
});

AdamGerthel on Sep 15, 2016

Just to add my two cents. I’m getting an error on Azure, though it works locally.

{ name: 'ConnectionError',
  message: 'Failed to connect to ------.database.windows.net:1433 - connect EADDRNOTAVAIL',
  code: 'ESOCKET' }

I am using encrypt:true. It’s kinda perplexing. Let me know if you have any ideas.

christopheranderson on Apr 27, 2015

I always got the error “sql.Connection is not a constructor”, why?

occulter on May 4, 2017

Having the same issue, everything is working locally and when published to Azure I get the same error… Any solution?

GalKlein on May 13, 2015