Cannot insert the value NULL

[kpaekn]

I have the following stored procedure:

CREATE TABLE #temp_table (num INT, txt VARCHAR(10))
INSERT #temp_table (num, txt) VALUES (NULL, 'second')
INSERT #temp_table (num, txt) VALUES (0, 'text')
SELECT num, txt FROM #temp_table

and i have the following JS code:

var tedious = require('tedious'),
    Connection = tedious.Connection,
    Request = tedious.Request,
    config,
    connection;

config = {
  userName: process.env.SQL_AJ_USER,
  password: process.env.SQL_AJ_SECRET,
  server: process.env.SQL_HOST,

  options: {
    port: process.env.SQL_PORT,
    database: 'TestingDatabase',
    encrypt: true,
    trustedServerCertificate: true
  }
};

connection = new Connection(config);
connection.on('connect', function (err) {
  if (err) {
    console.log(err);
    connection.close();
  } else {
    executeRequest(function () {
      connection.close();
    });
  }
});

function executeRequest (callback) {
  var request = new Request('ProcTest', function (err, rowCount, rows) {
    if (err) {
      console.log(err);
      callback();
    } else {
      console.log('rowCount: %d', rowCount);
      console.log(JSON.stringify(rows, null, 2));
      callback();
    }
  });
  connection.callProcedure(request);
}

I get the error:

Cannot insert the value NULL into column 'num', table 'tempdb.dbo.#temp_table; column does not
allow nulls. INSERT fails.

The error is not produced when the field is explicitly set as nullable, however, we have existing code that omits the null on creating temp tables.

When this procedure is executed on SQL Server Management Studio, it runs fine without errors.

[bretcope]

That error is coming from SQL Server. Tedious isn't even doing anything with types in this case. Why it would work elsewhere, I don't know, could have something to do with defaults in other environments.

There is an obvious solution which you've already discovered: just be explicit. In fact, you should really always be explicit via NULL or NOT NULL when defining columns. If you do that, you'll never get bitten by issues like this.

[lee-houghton]

Try the workaround in #230. I think you're running into the same problem I had with temporary table columns being created as non-nullable by default (whereas the ODBC and OLE DB drivers set nullable as the default).