SQL Server 2000 error with queries longer than 4000 characters

[christiaanwesterbeek]

When running this code with a query where query.length>4000:

var sql   = require('mssql');
var config = {
  server   : '1.2.3.4',
  user     : 'abc',
  password : 'def',
  options  : {
    tdsVersion: '7_1' //needed for SQL server version 2000
  }
};

sql.connect(config, function(err) {
  if (err)
    return console.error(err);

  var request = new sql.Request();

  var tmp = [];
  tmp.length=3983;

  var query = '/*'+tmp.join('x')+'*/ SELECT 1 as ok';

  console.log('sql length = '+query.length);
  request.query(query, function(err, result) {
    if (err)
      return console.error(err);

    console.log(result)
  });
});

This is what I get:

sql length = 4001
undefined

events.js:72
        throw er; // Unhandled 'error' event
              ^
ConnectionError: Failed to connect to 1.2.3.4:1433 - read ECONNRESET
    at Connection.socketError (...\node_modules\mssql\node_modules\tedious\lib\connection.js:681:26)
    at Socket.<anonymous> (...\node_modules\mssql\node_modules\tedious\lib\connection.js:3:59)
    at Socket.EventEmitter.emit (events.js:95:17)
    at net.js:441:14
    at process._tickCallback (node.js:415:13)

When I make the query 1 character shorter, it's working.

sql length = 4000
[ { ok: 1 } ]

I can provide you with debug info later today.... Will update this post once done.

[patriksimek]

Unfortunately this is a limitation of SQL Server 2000. There is no problem with queries longer than 4000 chars on SQL Server 2005 and above.

[christiaanwesterbeek]

The query (well over 4000 chars) is migrated to Node from another program where the same query was executed through asp and ADODB.Connection. It worked fine there (2000 too). And I'm not talking about a store procedure, but an actual query compiled from a template and merged with parameters. Afterwards being a string of ±6000 chars.

[patriksimek]

I forgot to mention that query uses sp_executesql to execute sql statements. This is where the limitation is.

You should try to execute the statement with batch.

[christiaanwesterbeek]

Ok great. I will try this. Thanks. 2 things. I think trying longer than 4000 length could be caught more elegantly. And you could add this scenario in the readme after where it now says: "Use this only in special cases..."

[patriksimek]

I have updated the docs. Thanks.