Tedious: BulkLoad - Invalid column type from bcp client

[mhingston]

Tedious: 2.2.3
Node 9.3.0
Linux x64
SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)

BulkLoad is failing when trying to insert into a column with type nvarchar(max).

SQL:

CREATE TABLE [dbo].[Test](
    [Body] [nvarchar](max) NOT NULL
)

JS:

const {Connection, TYPES} = require('tedious');
const config = {
    userName: '...',
    password: '...',
    server: '...',
    options: {
        database: '...'
    }
};

const connection = new Connection(config);

connection.on('connect', error => {
    if(error) {
        console.log(error.message);
    }

    const table = 'Test';
    const bulkLoad = connection.newBulkLoad(table, (error, rowCount) => {
        if(error) {
            console.log(error.message); // Invalid column type from bcp client for colid 1.
        }

        console.log(rowCount); // 0
    });

    bulkLoad.addColumn('Body', TYPES.NVarChar, {nullable: false});
    bulkLoad.addRow({Body: '123'});
    connection.execBulkLoad(bulkLoad);
})

Error Message:

Invalid column type from bcp client for colid 1.

[arthurschreiber]

Hey there! 👋🏻

It looks like you’re passing an object { Body: '...' } instead of a an array of values. Try changing it to [ '...' ] instead. 😊

[mhingston]

Thanks @arthurschreiber, I was using this reference which uses an object. Do the docs need updating?

[arthurschreiber]

Oh, maybe I’m wrong? I’m on my mobile right now, I’ll check again later or tomorrow. 😖

[mhingston]

This is strange, but I'm still getting Invalid column type from bcp client for colid 1, have tried using:

bulkLoad.addRow({Body: '123'});
bulkLoad.addRow(['123']);
bulkLoad.addRow('123');

[Suraiya-Hameed]

The Error seems to be due to the max type used 😓 Running your code on non-max column (like Nvarchar (4000)) works just fine. I'll dig more into it.

[Suraiya-Hameed]

bulkLoad.addColumn('MyIntColumn', TYPES.Int, { nullable: false });
options :
length for VarChar, NVarChar, VarBinary

Since it is a max type, length option should be passed,

bulkLoad.addColumn('Body', TYPES.NVarChar, {length: 'max', nullable: false});

We should add that detail into github.io pages.

[arthurschreiber]

bulkLoad.addColumn('Body', TYPES.NVarChar, {length: 'max', nullable: false});

I don't think passing length: 'max' is supported. It might work, but it's not intended to work. 😄 Try passing length: Infinity instead.

Note: Any value over 4000 will cause us to send the data in the format expected by bulk load for nvarchar(max).

[Suraiya-Hameed]

Got it! Thanks for correcting me 😄

[arthurschreiber]

@mhingston Heya! 👋 Just wanted to check whether you got this working and if this issue can be closed?

[mhingston]

@arthurschreiber Yes, all working thanks. Both Infinity and 'max' worked.

[JP1016]

im facing the same issue with nvarchar(max)

table.columns.add('column', sql.NVarChar, {
        length: Infinity
    });, 

tried both infinity and max , both didnt work and had to assign the column as nvarchar(3000)

[JP1016]

Invalid column type from bcp client for colid 1.
Unable to bulk insert data to a table with a column having nvarchar(max) field.
I tried tediousjs/tedious#679 , this fix , but it is also not working , im attaching a sample code to reproduce the same behaviour

Expected behaviour:
Need to bulk insert the data into the db

Actual behaviour:
(node:38764) UnhandledPromiseRejectionWarning: RequestError: Invalid column type from bcp client for colid 1.
    at handleError (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/mssql/lib/tedious.js:426:15)
    at Connection.emit (events.js:193:13)
    at Parser.tokenStreamParser.on.token (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/tedious/lib/connection.js:716:12)
    at Parser.emit (events.js:193:13)
    at Parser.parser.on.token (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at Parser.emit (events.js:193:13)
    at addChunk (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:297:12)
    at readableAddChunk (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:279:11)
    at Parser.Readable.push (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_readable.js:240:10)
    at Parser.Transform.push (/Users/jp/Documents/Coda/wp-backend/backend/node_modules/readable-stream/lib/_stream_transform.js:139:32)

Configuration:

test.js

var sql = require("mssql");

function testTable() {
    let table = new sql.Table('temp1');
    table.create = false;
    table.columns.add('id', sql.NVarChar, {
        length: 'max'
    });
    return table;
}
module.exports = testTable;

demo.js

var sql = require("mssql");
let tempTable = require("./test")();

async function testDb() {
    config = {
//put db config here
    }

    let db = await new sql.ConnectionPool(config).connect();
    const transaction = db.transaction()
    await new Promise(resolve => transaction.begin(resolve));
    const request = new sql.Request(transaction);

    tempTable.rows.add.apply(tempTable.rows, ["123123132"]);
    tempTable.rows.add.apply(tempTable.rows, ["213123123123"]);
    tempTable.rows.add.apply(tempTable.rows, ["23423423"]);
    tempTable.rows.add.apply(tempTable.rows, ["234234234234"]);
    console.log("bulk adding")
    console.log(tempTable)

    let response = await request.bulk(tempTable);
    console.log(response)
    await transaction.commit();
}

testDb();

Software versions
NodeJS: 11.13.0
node-mssql: 5.1.0
SQL Server Enterprise Edition

Engine version: 14.00.3049.1.v1