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:
var sqlcon = require('mssql');
function GetSQLData(queryCallback){
var config = {
user:'###'
,password:'######'
,server:'localhost\\SQL2K14'
,database: 'master'
}
var connection = new sqlcon.Connection(config,function(err){
if (err) console.log(err);
var dbQuery = new sqlcon.Request(connection);
dbQuery.query("WAITFOR DELAY '00:00:05';SELECT * FROM INFORMATION_SCHEMA.TABLES",function(err,resultset){
if (err) console.log(err);
queryCallback(resultset);
})
});
}
function callback (resultset){
console.dir('Results returned and printed from the call back function');
console.dir(resultset);
console.dir('Exiting the Application');
process.exit(0);
}
console.dir('Calling GetSQLData');
GetSQLData(callback);
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.
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports