tediousjs/node-mssql

Microsoft SQL Server client for Node.js

node-mssql's Issues

Support Streaming

Have you any plans to support streaming?

The library looks really promising as an alternative to tedious, but we have some procs which returns hundreds of thousands of records and waiting for the callback will hurt performance.

Multiple errors only showing last. Can we show the preceding too?

When I do

    var request = new sql.Request();
    request.query('select a;select b;', function(err, recordset) {
        console.log(err.originalError)
    });

I would only get the last error

{
  name: 'RequestError',
  message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'b'.',
  code: 'EREQUEST'
}

Would you consider implementing something like this that shows the preceding messages too in a way that we don't break existing code?

{
  name: 'RequestError',
  message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'b'.',
  code: 'EREQUEST',
  precedingErrors: [{
    name: 'RequestError',
    message: 'Msg 207, Level 16, State 1, Line 2. Invalid column name 'a'.',
    code: 'EREQUEST'
  }]
}

question how to determine which of the two modules are used

I am using Ubuntu on my PC. So when I use node-mssql module to set up connections, I can be using either tedious module or node-tds module. From the quick example, it wasn't clear which of the two is being used. How can we tell?

Thanks in advance.

Stored procedure return value: -1 becomes 4294967295

I've found that an SP that returns -1 will result in a returnVal of 4294967295.

POC:

create proc sp_negone as begin
return -1
end

app.get("/poc",function(req,res){
    var sqlcmd = new mssql.Request(dbc);
    sqlcmd.execute("sp_negOne",function(err,rs,rv){
        console.log(rv);
        res.end(rv.toString());
    })
});

4294967295 is logged to console and sent as response.

Thanks

Add node-tds as an optional driver

Validate Transact-SQL Data Types

Hi. Check this validate Transact-SQL Data Types https://github.com/paulomcnally/tsdt.js

It allows me to implement your module?

Any recommendation is welcome.

Regards.

The problem with using data types for stored procedure

the transmission parameters stored in the procedure as described in your reference, (for example "request.input ('input_parameter', sql.Int, 10);" )
when writing code:

var sql = require('mssql');
var request = new sql.Request();

request.input('position', sql.Int, 99);

request.execute('[TestSql].[dbo].[Test]', function(err) {
if (err){
console.error(err);
return next();
}
res.redirect('/');
});

error:

TypeError: Cannot read property 'Int' of undefined
at ContentHandler.postAddUser (D:\Node JS\TestDbSql\routes\post\index.js:98:38)
at callbacks (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:164:37)
at idSprav (D:\Node JS\TestDbSql\middleware\idSprav.js:11:5)
at callbacks (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:164:37)
at param (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:138:11)
at pass (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:145:5)
at Router._dispatch (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:173:5)
at Object.router (D:\Node JS\TestDbSql\node_modules\express\lib\router\index.js:33:10)
at next (D:\Node JS\TestDbSql\node_modules\express\node_modules\connect\lib\proto.js:193:15)
at Object.handle (D:\Node JS\TestDbSql\middleware\index.js:22:9)
POST /add 500 140ms - 892b

Module Hanging on Failed Connections

As a little background I'm writing a simple script to check if a connection to a DB can be established. When the DB connection is successful everything works great, however in the case where one or more of the connections fails the script will hang until idleTimeoutMillis + connectTimeout has passed and then errors with the following:

mssql-test-connection/node_modules/mssql/lib/tedious.js:204
            return c.close();
                     ^
TypeError: Cannot call method 'close' of null
    at Object.cfg_pool.destroy (mssql-test-connection/node_modules/mssql/lib/tedious.js:204:22)
    at Object.me.destroy (mssql-test-connection/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:149:13)
    at removeIdle (mssql-test-connection/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:178:10)
    at Timer.listOnTimeout [as ontimeout] (timers.js:110:15)

It appears that the code is waiting for the failed connection to go idle and then trying to close it at that point causing an error.

Here's the associated code to reproduce the error:

var sql    = require('mssql');

//To Reproduce: this is a connection to a DB that does not exist or is inaccessible
connectionsToTest = [
  {
    name: '...',
    user: '...',
    password: '...',
    server: '...',
    database: '...'
  }
];

connectionsToTest.forEach(function(config) {
  //To prevent a 45sec wait for failure
  config.pool = { idleTimeoutMillis : 500 };
  config.options = { connectTimeout : 5000 };

  var connection = new sql.Connection(config, function(err) {
      var result = 'Attempting to Connect to '+config.name+' DB ';

      if(err) {
        result += '[ '+err+' ]\n';
        process.stdout.write(result);
      } else {
        var request = new sql.Request(connection);

        request.query('select 1 as number', function(err, recordset) {
            if(recordset[0].number == 1) {
              result += '[ Success ]\n';
            } else {
              result += '[ '+err+' ]\n';
            }

            process.stdout.write(result);
        });
      }
  });
});

Doesn't know if the connection failed after connection is made.

From what I've seen, Tedious provides events to let you know when a connection has ended. I'm not 100% certain if this even is fired if the server has gone down or the connection is broken by some other source. If it does though, it would be nice to gain access to such events. From what I've seen, this library doesn't handle failed connections.

I tested the connected property of the connection object, but it seems to only stay true all the time once a connection has been successfully created. I tested it by pulling out the ethernet wire and it still claimed to be connected.

How to disable Pooling for node-mssql

Hey, guys

I'm trying to use node-mssql for testing performance of MSSQL Server using nodejs.

Currently, I just fork 256 child processes which in charge of sending query to MSSQL Server, and I need to create 256 active connections definitely, how can I make it work?

thanks,
Khalil

Cannot connect using instance-name

I have config like below, but get connection error. When i use port instead of instance-name everything works.

var config = {
   user: 'sa',
   password: '...',
   server: '192.168.1.2\\sql2005',
   database: 'db1'
};

Using options (tedious format) works good

var config = {
   user: 'sa',
   password: '...',
   server: '192.168.1.2',
   database: 'db1',
   options: {
       instanceName: 'sql2005'
   }
};

2 simultaneous different database connection don't seem to work

I've created 2 simultaneous sql.connect call to 2 different configuration on a Hapi server, where each call is mapped to a different path.
When testing each call on it's on it seems to work just fine.
but if I try to run both of them at the same time only 1 of them seem to return results, the other one returns an error message: 'Invalid object name...'

if I add a timeout function in the front end when making the call and if one of the calls wait for a bit it works just fine.
can you please advice?
Thanks in advance.

How to use input parameters in a INSERT INTO Query

Hi,

Im a begginer in programming, i can´t pass an input parameter to my sql statement, please can you help me. Also i need to insert the value of the input parameter from another external variable, is it possible?

var sql = require('mssql');
var qstring = {
user: 'sa',
password: '*',
server: '**',
database: '****'
}
var connection = new sql.Connection(qstring, function(err) {
var r = new sql.Request(connection);
r.input('cinco',sql.VarChar,'tres');
r.multiple = true;
r.query("INSERT INTO Test(userid,userod) values (cinco,'ridiculo')",function(err,recordsets) {

connection.close();

});

});

Queries don't support parameters

Here's a simple patch to add that support:

src/tedious.coffee
===================================================================
--- src/tedious.coffee  (revision )
+++ src/tedious.coffee  (revision )
@@ -172,6 +172,16 @@
                        recordset.push row

                    if @verbose then console.log "---------- response -----------"
+
+                   for name, param of @parameters when param.io is 1
+                           if @verbose
+                               if param.value is tds.TYPES.Null
+                                   console.log "    input: @#{param.name}, null"
+                               else
+                                   console.log "    input: @#{param.name}, #{param.type.name.toLowerCase()}, #{param.value}"
+
+                           req.addParameter param.name, getTediousType(param.type), if param.value? then param.value else tds.TYPES.Null
+
                    connection.execSql req

                else

CREATE/ALTER PROCEDURE CALLS

Hello,
I'm pretty much having this issue
tediousjs/tedious#79

can you please provide a way to use tedious' execSqlBatch?

I added a these lines around line 205 in tedious.coffee.

if @batch
    connection.execSqlBatch req
else connection.execSqlBatch req

But i am not sure if these would have any implications, or cause any problems.

Cannot prepare statement when using Decimal type

I'm trying to use Prepared Statements and am running into an issue where Decimal types will not prepare. I'm using mssql version 0.5.5 with the bundled tedious, connecting to SQL Server 2000.

The following is minimal code reproduction:

var sql = require('mssql');

var opts = {
  user     : "username",
  password : "password",
  server   : "10.0.0.1",
  database : "database",
  options  : {
    // SQL Server 2000
    tdsVersion : "7_1"
  }
};

var conn = new sql.Connection(opts, function(err) {
  var query = "select @param as value";
  var data = { 
    param : 155.33,
  };

  var ps = new sql.PreparedStatement(conn);
  ps.input('param', sql.Decimal);
  ps.prepare(query, function(err) {
    if (err) {
      console.log(err);
      return;
    }
    ps.execute(data, function(err, recordset) {
      // ... snip error check ...
      console.log(recordset);
      ps.unprepare(function(err) {
        // ...
      });
    });
  });
});

The error message is as follows:

{ [RequestError: Statement(s) could not be prepared.]
  name: 'RequestError',
  message: 'Statement(s) could not be prepared.',
  code: 'EREQUEST',
  precedingErrors: 
   [ { [RequestError: Line 1: Incorrect syntax near '18'.]
       name: 'RequestError',
       message: 'Line 1: Incorrect syntax near \'18\'.',
       code: 'EREQUEST' },
     { [RequestError: Must declare the variable '@param'.]
       name: 'RequestError',
       message: 'Must declare the variable \'@param\'.',
       code: 'EREQUEST' } ] }

If I use sql.Int, the query is prepared properly. sql.Numeric errors as well, with the same error message as above. Any help would be appreciated.

ReadableTrackingBuffer.prototype.assertEnoughLeftFor Error: required : 4, available : 0

When I get more than 30 rows data from a mssql table, the function : ReadableTrackingBuffer.prototype.assertEnoughLeftFor will throw the error : "required : 4, available : 0" or "Error: required : 76, available : 42" . but if I get less than 20 rows data from the same table, it can return the right result .

the function in the files : \node_modules\mssql\node_modules\tedious\lib\tracking-buffer\readable-tracking-buffer.js

setCharset

How to setCharset?

Combine execute() callback parameters

Currently the signature for the execute() callback is function (err, recordsets, returnValue) {}. This causes a problem with every node library that relies on the standard node callback signature of function (err, result) {}.

I'd like to propose that the standard signature be adopted, and an object literal be returned with the following signature:

{
  recordsets: [...],
  returnValue: ...
}

Connection 'close' event not firing

With tedious.

Currently, the 'close' event is not fired on a connection when said connection is closed by the connection pool manager (ex. on idle timeout)

From briefly poking around, may be a tedious issue; but thought I'd raise it here first

SQL Server Instance

hello! How i can use MS SQL Server instance name?
localhost\i2008 dont work

RangeError: Trying to access beyond buffer length

I am getting this error while getting records from mssql. If there are 1 - 2 records it is working fine but in case of more records it is throwing this error: RangeError: Trying to access beyond buffer length. The function where I am getting error is ReadableTrackingBuffer.prototype.assertEnoughLeftFor. and the function is in file the function in the files : \node_modules\mssql\node_modules\tedious\lib\tracking-buffer\readable-tracking-buffer.js .

The way I am connecting and fetching data is:

var sqlQuery = "SELECT * from [BackOffice.Applications] ";
var request = new sql.Request(connection);
request.query(sqlQuery, function (err, recordset) {
//Some logic here
});

Please help.

Problem on connection and authentication

When run the following code, keep getting error
"Login failed; one or more errorMessage events should have been emitted"

config = {user: p.username, password: p.password, server: p.host, database: p.other};
        console.log("connecting to mssql");
        CBS[id].conn = new mssql.Connection(config, function(err) {
            if (err) {
                sendMsg(id, {error: err}); return;
            }
            sendMsg(id, {result:"connected"} );
        });

packet sniffer shows it failed too. The question is, what mode of authentication the above code uses. Is it Windows Authentication?

Thanks in advance.

SQLDbType.Structured

Is it possible to have a parameter of type SQLDbType.Structured?

http://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx

Prepared Statement,NVarChar and sql.MAX

I'm probably missing something simple but I'm getting a request error (statement not prepared) when I try to prepare a statement that uses sql.MAX with a sql.NVarChar. My SQL table has a field named SValue that is an nvarchar(MAX). I get the error when I execute something like the following:

...
var ps = new sql.PreparedStatement(connection);
ps.input('TID', sql.Int);
ps.input('SValue',sql.NVarChar(sql.MAX));

var statement = 'insert into TTable (TID,SValue) values (@tid,@svalue)'

ps .prepare(statement, function(err, recordsets) {
...

If I replace sql.MAX with a number, it works fine. What am I missing?

Column Meta is empty (Tedious)

When using tedious, result.columns is always undefined, regardless if any actual rows returned or not.

Add support for named instances in msnodesql driver

https://github.com/WindowsAzure/node-sqlserver/wiki

RequestError: Invalid object name 'people'

After a hard time, I successfully connecting to azure database with this configuration
var config = {
"user": "MyService@server",
"password": "password",

"options":{
"database": "database",
"encrypt": true,
},
};
but when i tried to request a query like (select * from people), it shows an error
RequestError: Invalid object name 'people'.although i have a table named people and i can use it with this statement locally.
please help.

throw TypeError('value is out of bounds');

[root@ISAAPI01 ser-web-api]# node api.js
Kill PID:6644

buffer.js:784
    throw TypeError('value is out of bounds');
          ^
TypeError: value is out of bounds
    at TypeError (<anonymous>)
    at checkInt (buffer.js:784:11)
    at Buffer.writeUInt32LE (buffer.js:841:5)
    at WritableTrackingBuffer.writeUInt32LE (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:92:17)
    at WritableTrackingBuffer.writeUInt64LE (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:119:17)
    at Object.TYPE.writeParameterData (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/data-type.js:268:25)
    at new RpcRequestPayload (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/rpcrequest-payload.js:66:22)
    at Connection.callProcedure (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:731:56)
    at /sites/ser-web-api/node_modules/mssql/lib/tedious.js:723:33
    at dispense (/sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:247:16)
    at Object.me.acquire (/sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:316:5)
    at Request._acquire (/sites/ser-web-api/node_modules/mssql/lib/main.js:884:37)
    at Request.TediousRequest.execute (/sites/ser-web-api/node_modules/mssql/lib/tedious.js:586:21)
    at Request.execute (/sites/ser-web-api/node_modules/mssql/lib/main.js:1088:56)
    at /sites/ser-web-api/controllers/lubricantes.js:28:21
    at Array.forEach (native)
    at /sites/ser-web-api/controllers/lubricantes.js:12:14
    at /sites/ser-web-api/helpers/mssql.js:40:17
    at /sites/ser-web-api/node_modules/mssql/lib/main.js:236:51
    at /sites/ser-web-api/node_modules/mssql/lib/tedious.js:330:20
    at /sites/ser-web-api/node_modules/mssql/node_modules/generic-pool/lib/generic-pool.js:278:11
    at Connection.<anonymous> (/sites/ser-web-api/node_modules/mssql/lib/tedious.js:308:24)
    at Connection.g (events.js:180:16)
    at Connection.EventEmitter.emit (events.js:92:17)
    at Connection.processedInitialSql (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:690:17)
    at Connection.STATE.LOGGED_IN_SENDING_INITIAL_SQL.events.message (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:173:23)
    at Connection.dispatchEvent (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:573:59)
    at MessageIO.<anonymous> (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/connection.js:528:22)
    at MessageIO.EventEmitter.emit (events.js:92:17)
    at MessageIO.eventData (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/message-io.js:58:21)
    at Socket.<anonymous> (/sites/ser-web-api/node_modules/mssql/node_modules/tedious/lib/message-io.js:3:59)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)
    at Socket.EventEmitter.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:408:10)
    at emitReadable (_stream_readable.js:404:5)
    at readableAddChunk (_stream_readable.js:165:9)
    at Socket.Readable.push (_stream_readable.js:127:10)
    at TCP.onread (net.js:528:21)
[root@ISAAPI01 ser-web-api]# 

RangeError: Trying to access beyond buffer length

Hello,
I am getting this Notification while connecting to MSSql, I tried to resolve this issue in all aspects but i didn't achieved, can you please help me out to solve this problem.
Please reply me as soon as possible.

Thank you in advance

Remote Server and Windows Authentication

I am logging into a remote server for our SQL DB. Currently the DB uses Windows Authentication for the login. Is this supported. Looking to see if this is supported before submitting a request to change security items in the DB.

Thanks in advance.

Problem with getting last inserted ID

Anybody have issues with retrieving the last inserted ID from db after an insert statement?

Assume a table student contains 3 fields (id, age, name).
Here's my code:

        var query = "INSERT INTO student(age, tagname) values (6, 'Bob') ";
        db = new sql.Request();
        db.query(query, function(err, data) {
        if (!err)
        {
            var str = "SELECT @@IDENTITY AS 'identity'";    
            db = new sql.Request();
            db.query(str, function(suberr, subdata)
            {
                console.log(" this is LAST inserted id : ");
                console.log(subdata);   // -> RETURNED NOTHING
            });
        }

The above code returned nothing as the ID eventhough the student record was captured correctly into the db.

OUTPUT

 this is last LAST inserted id :
[ { identity: null } ]

I am wondering if there's something wrong with my code, or if this is a bug.
Any feedbacks are appreciated.

PS. If I do another query in between the INSERT and SELECT @@IDENTITY, be it a select query, or insert query, then it would give me the ID that I am looking for.

Performance issue

Hello,

I am trying to save 3 documents (head and body in transaction) in sql server 2008 r2. Each document has 4 or 5 rows as body, so in total I need just to insert about 20 rows in database.
The time that it is needed to make this insertion is about 1 minute. I tried to insert just one document with 5 rows in body, and it was needed about 45 seconds. While reading this 3 documents it was needed just 0.8 seconds. My question is: Is this a normal performance or am I doing something wrong?

Best Regards

requests execute using multiple connections?

I'm not too familiar with Coffeescript, but I think there might be a scoping issue with connections and requests.

I wrote a little test case to show what's happening. Below 2 connections are created, but a request is only run on 1 of them. The callback however is called twice however, with the request being run against both connections.

Thanks for the work you've put into this library by the way - I prefer this api much more than the usual tedious api

var server1Config = {
    user: "s1user",
    password: "s1password",
    server: "s1server",
    database: "s1database"
}

var server2Config = {
    user: "s2user",
    password: "s2password",
    server: "s2server",
    database: "s2database"
}

var server1 = require('mssql');
server1.connect(server1Config, function(err) {
    // At this point server1 is connected. 
    // We'll go ahead and also connect up to another server at the same time
    var server2 = require('mssql');
    server2.connect(server2Config, function(err) {
        // now server2 is connected
        // Here we'll create a request with server1, 
        // but it will actually be executed twice using both server1 and server2
        var request = new server1.Request();
        var sql = "SELECT @@Servername AS ServerName";
        request.query(sql, function(err, recordset) {
            console.log(recordset);
        });
    });
});

Closing Connections

Looking at the coffee script, it appears that when a connection is closed and pooling is enabled, the entire pool is cleared. (Not coffee script literate)

What's the recommended use of connections with pooling enabled? Should i

1. connect once and share amongst modules
2. connect, execute query and then close for each db request
3. connect, execute query and not close for each db request.

thanks for a great module

Error Handling

Hello,

I was wondering the most elegant way to handle errors using prepared statements or transactions.. In my code, there are always 3 nested functions (preparedstatement .prepare, .execute, and .unprepare). The code gets really convoluted quickly when handling the possibility of an error at each of these steps. Also, I am using prepared statements for each of my queries so I can bind input variables.

ps.prepare('select @param as value', function(err) {
    // ... error checks

    ps.execute({param: 12345}, function(err, recordset) {
        // ... error checks

        ps.unprepare(function(err) {
            // ... error checks

        });
    });
});

Is there a clean, recommended method of handling errors for a prepared statement query if I want to handle most of these errors the same way?

Failed login seems to leave an open connection

Test code:

sql = require('mssql')

config =
  database: 'MyDb'
  user: 'username'
  password: 'INVALID'

conn = new sql.Connection config

conn.connect (err)->
  if err
    console.error err.message
  else
    console.log 'connected'
  conn.close()

If the password in the above configuration is correct, the I get:

$ coffee test-login.coffee
connected

$

Buf if it's incorrect, I get

$ coffee test-login.coffee
Login failed for user 'username'

and then I have to wait about 30 seconds until the connection times out. I believe I've traced this to the fact that neither the @connecting or @connected flag gets set on the connection, so when the @close method is called on the Connection object, the underlying driver's Connection::close method never gets called.

I tried applying this patch:

diff --git a/src/main.coffee b/src/main.coffee
index 5de6756..05ac216 100644
--- a/src/main.coffee
+++ b/src/main.coffee
@@ -210,7 +210,11 @@ class Connection extends EventEmitter
                                callback? err

                        @driver = null
-
+               else
+                       @driver.Connection::close.call @, (err) =>
+                               callback? err
+
+                       @driver = null
                @

But still no luck. Is this an issue with node-mssql or with the generic-pool module?

ConnectionError?

I have the following code:

    var config = {
         . . . . .
.   };
    var callback = printError;
    var connection = new sql.Connection(config, function(err) {
        if(err) return callback(err);
        var request = new sql.Request(connection);
        request.query('select p.PresentationId from PlaybackAudits p', function(err, recordset) {
            if(err) return callback(err);
            console.dir(recordset);
        });
    });

And I get the error:

{ name: 'ConnectionError',
message: 'Login failed; one or more errorMessage events should have been emitted' }

I tried these same credentials with SQL Server Management Studio and it succeeded but these credentials fail with node-mssql.

Kevin

How can I keep the connection of sql server?

I start a new server connected to the database server, after a long time no connections to db, when reconnect it, the app idled.

Can you add a 'error' or 'timeout' event for connection that we can use it.

Thank you so much.

TypeError: Cannot call method 'acquire' of null

I get this error when i try connect in my database.

Follow the code:

var sql = require('mssql'); 

var config = {
    user: 'xx',
    password: 'xx',
    server: 'localhost',
    database: 'xx'
}

var connection = new sql.Connection(config, function(err) {
    if (err) {
        console.log(err);
    }
    else {
        console.log('connected');
    }
});

and the error:

Thanks.
Will

Connect to sql server 2000 throws an uncaught exception and is terminated

I am on a machine with node-mssql installed but also MSSMS. I can connect to a SQL Server (2000) using MSSMS (Microsoft SQL Server Management Studio), but when I use node-mssql to connect to the same SQL Server I don't get an error, but it doesn't connect either. This is my code:

var sql   = require('mssql');
sql.connect({server: "ip", user: "user", password: "password"}, function(err) {
      console.log('sql.connect error', err);
      if (err) return callback(err);
      var request = new sql.Request();
      request.query(sql, callback);
});

The line console.log is never called and instead the node application throws an uncaught exception and is terminated:

TypeError: Uncaught, unspecified "error" event.
    at TypeError (<anonymous>)
    at Connection.EventEmitter.emit (events.js:74:15)
    at Parser.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:490:15)
    at Parser.EventEmitter.emit (events.js:95:17)
    at Parser.nextToken (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:100:14)
    at Parser.addBuffer (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:66:17)
    at Connection.sendDataToTokenStreamParser (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:675:35)
    at Connection.STATE.SENT_LOGIN7_WITH_STANDARD_LOGIN.events.data (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:146:23)
    at Connection.dispatchEvent (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:573:59)
    at MessageIO.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:523:22)

When I connect with node-mssql using an ip address of another machine without mssql I get a nice ETIMEOUT, "Failed to connect to 10.101.0.11:1433 in 15000ms"

PS: I changed a lot of content in this issue since I originally wrote it 20 minutes ago!

Support for prepared statements?

Hello

Is there any built in support for prepared statements? Do you have any suggestions as to how to implement them?

Thank you for your amazing work!

The server did not return a response for this request. Server returned 0 bytes

I have a node service connect successfully to azure database from local host using tedious and retrieve data correctly ,but when i deploy the service on a cloud service like nodejitsu or any cloud service, the request takes long time and ends with "The server did not return a response for this request. Server returned 0 bytes".Where is the problem?

Wrong? number of recordsets returned by executing request.execute()

First of all I want to say that I really like your module and it is definitely the best MSSQL node module I've tried so far.

Not sure if I'm missing something but I have a problem with request.execute() it seems that the returning recordsets argument has more recordsets than I expect. I created a stored procedure which simply returns me 2 tables: 1 with the returnCode and message and another table contains the result.

For some reason request.execute() returns me 4 tables where some of them are empty. If I execute the same stored procedure using request.query I will get a precise number of recordsets back which is 2.

Here is my example code:

var sql = require('mssql');

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

connection.connect(function(err) {
  if (err) {
    console.log(err);
  }
});

var request1 = new sql.Request(connection);
request1.multiple = true;
request1.verbose = false;
request1.execute('stGetLocations', function(err, recordsets) {
  console.log('====== stored procedure request ======');
  console.log(recordsets.length);
  console.log(recordsets);
});

var request2 = new sql.Request(connection);
request2.multiple = true;
request2.verbose = false;
request2.query('EXEC [dbo].[stGetLocations]', function(err, recordsets) {
  console.log('====== query request ======');
  console.log(recordsets.length);
  console.log(recordsets);
});

And here is what I see in my output

====== stored procedure request ======
4
[ [],
  [ { errorCode: 1,
      errorMessage: 'Locations retrieved successfully' } ],
  [ { locationid: 1, name: 'Ontario' },
    { locationid: 2, name: 'Quebec' } ],
  [] ]
====== query request ======
2
[ [ { errorCode: 1,
      errorMessage: 'Locations retrieved successfully' } ],
  [ { locationid: 1, name: 'Ontario' },
    { locationid: 2, name: 'Quebec' } ] ]

using OSX 10.7.5
mssql 0.3.4
node 0.10.12
sql server 11.0.3000 (SQL Server 2012)

Information regarding best practices, tips or heads up?

Hello,

I'm working on an API that will be pulling information from multiple databases, lots of different mssql databases. But it's possible it would receive concurrent requests for the same db during short periods of time.

Naturally, I still haven't done an all out testing of the product, but I'm already wondering if there are gotchas, best practices or, in general, any kind of information that would help me get the best out of this library.

For instance, I was thinking about probably preparing some sort of connection pool, or a store of connections that have been recently used. Maybe keep those connections open or something.

BTW, I'm also new to this node culture. Maybe my question is a silly. Yet any help would be greatly appreciated :)

Thanks in advance,

Session and local temporary tables

Hi,
I have problem with local temporary tables - it seems that they are not visible in concurent requests.
The error I got is: [RequestError: Invalid object name '#tbltemp'.]

create procedure p_temp as
begin
insert into #tbltemp (dt) values (GetDate());
end;

var sqlConfig: {
        user: 'xxx',
        password: 'xxx',
        server: 'xxx',
        database: 'xxx'
    }

/* POST new events . */
router.post('/loctmp', function(req, res) {
    try {
        var connection = sql.connect(config.sqlConfig, function(err) {
        var transaction = new sql.Transaction(connection);
        transaction.begin(function(err) {
        var request = new sql.Request(transaction);

        //var request = new sql.Request(transaction);
        request.query("create table #tbltemp (dt datetime)", function(err, recordsets, returnValue) {
            console.dir(err);

            //var request = new sql.Request(transaction);
            request.execute('p_temp', function(err, recordsets, returnValue) {
                console.dir(err);

                //var request = new sql.Request(transaction);
                request.query('select * from  #tbltemp', function(err, recordset) 
                    {
                        console.dir(err);
                        console.log(recordset);
                    }); 

            }); //p_temp
        }); //create table

        }); //transaction
    }); //connection
    } catch (e) {
        console.error("Error:", e); 
    }

});

Synchronous methods

For any decent sized webapp, you'll have situations where the query of a dataset requires the result of another dataset. You'll also have situations in which you want to query data in a particular order.

At the moment I'm using chaining/shifting and separate synchronous libraries to replicate this. As evidenced by node.js core modules and a very large number of popular modules, 'Sync' methods are becoming commonplace.

It'd be wonderful for this module to implement sync methods as well.

Reuse connection?

Is the connection reusable like with the oracle package? Is so would an example be possible?

Oracle.connect = function(callback) {
   oracle_driver.connect(connectData, function(err, connection) {
      if(err) {
         console.log(err);
      } else {
         callback(err, connection);
      }
      connection.close();
   });
}

Oracle.query = function(query, callback) {
   Oracle.connect(function(err, connection) {
      connection.execute(query, [], function(err, results) {
         if (err) {
            console.log(err);
            callback(err, results);
         } else {
            callback(err, results);
         }
      });
   });
}

I want to insert huge data into mssql, How can I do it ?

I want to insert 6 millions data into mssql using node-mssql. Should I use transaction or use single sql string?

TypeError('value is out of bounds')

This error is crashing my app, and I'm having a hard time tracking down the parameter and value that's causing it:

buffer.js:784
    throw TypeError('value is out of bounds');
          ^
TypeError: value is out of bounds
    at TypeError (<anonymous>)
    at checkInt (buffer.js:784:11)
    at Buffer.writeUInt32LE (buffer.js:841:5)
    at WritableTrackingBuffer.writeUInt32LE (./node_modules/mssql/node_modules/tedious/lib/tracking-buffer/writable-tracking-buffer.js:92:17)
    at Object.TYPE.writeParameterData (./node_modules/mssql/node_modules/tedious/lib/data-type.js:264:25)
    at new RpcRequestPayload (./node_modules/mssql/node_modules/tedious/lib/rpcrequest-payload.js:66:22)
    at Connection.execSql (./node_modules/mssql/node_modules/tedious/lib/connection.js:712:56)
    at ./node_modules/mssql/lib/tedious.js:563:33
    at Transaction.next (./node_modules/mssql/lib/main.js:738:28)
    at Request._release (./node_modules/mssql/lib/main.js:895:33)
    at Request.userCallback (./node_modules/mssql/lib/tedious.js:462:23)

It's happening in a batch of about 2k inserts inside a transaction. Any pointers to figuring this out would be greatly appreciated.