Connecting to an MS SQL Instance using NodeJS (Fixing ConnectionError: Port for SQLServer not found in ServerName & Failed to connect to localhost:undefined in 15000ms)





4.00/5 (1 vote)
Connecting to MS SQL Server using NodeJS
After few debates and discussions on new technologies in the market and how to adapt to them, during the weekend I thought of exploring NodeJS and it applications. Since I spent most of my time designing data-centric applications at office, as the first step I thought of connecting to MS SQL Server using NodeJS. As a newbie to NodeJS, I went through the official documentation and managed to achieve it. However, during the course, I faced many difficulties and by referring to many of the articles, I was managed to resolve all these hurdles. So I thought of including these problems which I faced and how to overcome them. So it would be a great help to anyone who’s exploring or trying to achieve this more easily.
For this, I will be using SQL Server 2014 on an Instance (.\SQL2K14).
- First you need to download and install NodeJS. (https://nodejs.org/en/)
- Install MSSQL package for Node, using the following syntax: (Use windows command prompt)
npm install mssql
- Create a file named ‘connecttosql.js’ and include the following code:
//We require mssql package for this sample var sqlcon = require('mssql'); function GetSQLData(queryCallback){ //A callback function is taken as an argument. Once the operation is completed we will be calling this //SQL Configuration var config = { user:'###' //SQL User Id. Please provide a valid user ,password:'######' //SQL Password. Please provide a valid password ,server:'localhost\\SQL2K14' /* Since my SQL is an instance I am using 'localhost\\Instance'. If you have SQL installed on the default instance, it should be server:'localhost' */ ,database: 'master' //You can use any database here } var connection = new sqlcon.Connection(config,function(err){ //In case of an error print the error to the console. You can have your customer error handling if (err) console.log(err); //Query Database var dbQuery = new sqlcon.Request(connection); //Purposely we are delaying the results dbQuery.query("WAITFOR DELAY '00:00:05';SELECT * FROM INFORMATION_SCHEMA.TABLES",function(err,resultset){ //In case of an error print the error to the console. You can have your customer error handling if (err) console.log(err); //Passing the resultset to the callback function queryCallback(resultset); }) }); } function callback (resultset){ console.dir('Results returned and printed from the call back function'); console.dir(resultset); //Exit the application console.dir('Exiting the Application'); process.exit(0); } //Calling the function console.dir('Calling GetSQLData'); GetSQLData(callback); /* Once we call this function even there's a delay to return the results you will see the next line printing 'Waiting for callback function to get invoked...' */ console.dir('Waiting for callback function to get invoked...');
I have provided the relevant information as comments. Before running the program please make sure the following configurations on the SQL server is already done:
- Enable TCP/IP Protocols in SQL Server Configuration Manager for both server and client
Or else when running it will result an error shown below:
{ [ConnectionError: Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\[YourPCName]\pipe\M SSQL$SQL2K14\sql\query;;] name: 'ConnectionError', message: 'Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\\\[YourPCName]\\pipe\\MSSQL $SQL2K14\\sql\\query;;', code: 'EINSTLOOKUP' } { [ConnectionError: Connection is closed.] name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED' }
- In SQL Server Configuration Manager under SQL Server Services make sure that ‘SQL Server Browser’ service is running.
Or else when running the script it will result an error shown below:
{ [ConnectionError: Failed to connect to localhost:undefined in 15000ms] name: 'ConnectionError', message: 'Failed to connect to localhost:undefined in 15000ms', code: 'ETIMEOUT' } { [ConnectionError: Connection is closed.] name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED' }
if the aforementioned issues are already addressed execute the above file using the following syntax in a Windows Command Window:
node connecttosql.js
You should get a similar result which is shown below:
'Calling GetSQLData' 'Waiting for callback function to get invoked...' 'Results returned and printed from the call back function' [ { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'spt_fallback_db', TABLE_TYPE: 'BASE TABLE' }, { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'spt_fallback_dev', TABLE_TYPE: 'BASE TABLE' }, { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'spt_fallback_usg', TABLE_TYPE: 'BASE TABLE' }, { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'spt_values', TABLE_TYPE: 'VIEW' }, { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'spt_monitor', TABLE_TYPE: 'BASE TABLE' }, { TABLE_CATALOG: 'master', TABLE_SCHEMA: 'dbo', TABLE_NAME: 'MSreplication_options', TABLE_TYPE: 'BASE TABLE' } ] 'Exiting the Application'
I hope this will help anyone who’s using node to connect to SQL and facing the aforementioned issues.