Node-mssql: EREQUEST:4012 An invalid tabular data stream (TDS) collation was encountered when SP executed

[deepanigi]

I am trying to send TVP from nodejs to SQL server stored procedure along with other parameters. When the stored procedure is executed the error is triggered.

Expected behaviour:

When i try to execute from the query from SQL server it works fine. I expect the same to happen.

DECLARE @return_value int
DECLARE @priceList LocationPricingTableTyp
DECLARE @primKey uniqueidentifier
     SET @primKey = NEWID();
INSERT INTO @priceList
SELECT @primKey, 55676123, 12.0, 15.0,'young','summary';

EXEC    @return_value = [dbo].[sp_LocationMaster_Create]
        @LocationId = 55676123,
        @Language = 'aen',
        @IsActive = 1,
        @TblLocationPrice = @priceList
SELECT  'Return Value' = @return_value

GO

Actual behaviour:

When i executing from the nodejs

    const LocationPricingTableTyp = new sql.Table()
    LocationPricingTableTyp.columns.add('LocationId', sql.Int)
    LocationPricingTableTyp.columns.add('LowestPrice', sql.Decimal)
    LocationPricingTableTyp.columns.add('HighestPrice', sql.Decimal)
    LocationPricingTableTyp.columns.add('PriceType', sql.VarChar)
    LocationPricingTableTyp.columns.add('Summary', sql.Text)
    // Add rows
    LocationPricingTableTyp.rows.add(777,12.0,15.0,'young','summary')

          SQLrequest.input('LocationId'                      ,sql.Int      ,itemobj.baseId) 
          SQLrequest.input('Keywords'                   ,sql.Text     ,itemobj.Keywords)        
          SQLrequest.input('IsDeleted'                      ,sql.Bit      ,true)        
          SQLrequest.input('IsActive'                           ,sql.Bit      ,true)
          SQLrequest.input('TblLocationPrice'                              ,LocationPricingTableTyp)
          SQLrequest.execute("sp_LocationMaster_Create", (err, recordset) => {
            if (err){

              console.log('Not added due to following error')
              console.log(err)

            }else{ 
            // send records as a response
            console.log('successfully added to Master');

            }
            });

Error:"An invalid tabular data stream (TDS) collation was encountered."

Software versions

• NodeJS: 8.9.4
• mssql: 4.2.2
• SQL Server studio: 17.5

[willmorgan]

Hi, which version of node-mssql are you using?

[deepanigi]

Hi, which version of node-mssql are you using?

I am using "mssql": "^4.2.2",

[willmorgan]

Are you sure your code is correct? You're executing LocationMaster_create and in Node you're executing Location_create. The JS example also has a couple of syntax errors.

A simple search for the error message found a few results; this one looks promising:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0a8defc-8d88-4ea4-95fa-4647625d9c41/error-4012-an-invalid-tabular-data-stream-tds-collation-was-encountered?forum=sqltools

Can you check that your collation is correctly configured and report back your results.

[deepanigi]

Are you sure your code is correct? You're executing LocationMaster_create and in Node you're executing Location_create. The JS example also has a couple of syntax errors.

A simple search for the error message found a few results; this one looks promising:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0a8defc-8d88-4ea4-95fa-4647625d9c41/error-4012-an-invalid-tabular-data-stream-tds-collation-was-encountered?forum=sqltools

Can you check that your collation is correctly configured and report back your results.

Sorry , The name was copied wrong. But both node and SQL executes the same Procedure.

RequestError: An invalid tabular data stream (TDS) collation was encountered.
    at handleError (C:\DataMiner\node_modules\mssql\lib\tedious.js:862:15)
    at emitOne (events.js:116:13)
    at Connection.emit (events.js:211:7)
    at Parser.<anonymous> (C:\DataMiner\node_modules\tedious\lib\connection.js:618:16)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at Parser.<anonymous> (C:\DataMiner\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at addChunk (C:\DataMiner\node_modules\readable-stream\lib\_stream_readable.js:291:12)
  code: 'EREQUEST',
  number: 4012,
  lineNumber: 1,
  state: 1,
  class: 16,
  serverName: 'avndev',
  procName: '',
  originalError:
   { Error: An invalid tabular data stream (TDS) collation was encountered.
    at handleError (C:\DataMiner\node_modules\mssql\lib\tedious.js:860:19)
    at emitOne (events.js:116:13)
    at Connection.emit (events.js:211:7)
    at Parser.<anonymous> (C:\DataMiner\node_modules\tedious\lib\connection.js:618:16)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at Parser.<anonymous> (C:\DataMiner\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at addChunk (C:\DataMiner\node_modules\readable-stream\lib\_stream_readable.js:291:12)
     info:
      { number: 4012,
        state: 1,
        class: 16,
        message: 'An invalid tabular data stream (TDS) collation was encountered.',
        serverName: 'avndev',
        procName: '',
        lineNumber: 1,
        name: 'ERROR',
        event: 'errorMessage' } },
  name: 'RequestError',
  precedingErrors: [] }

This is the error log triggered

[willmorgan]

Thanks but there are still differences and your code doesn't match up.

For example you're inputting IsDeleted in the JS example (with a syntax error in the value) and in the raw SQL example you aren't declaring that at all.

Please thoroughly check and replicate the exact parameters between your SQL and Node, and please check the MSDN article I posted to ensure that your collation and variable values are correct.

[deepanigi]

Thanks but there are still differences and your code doesn't match up.

For example you're inputting IsDeleted in the JS example (with a syntax error in the value) and in the raw SQL example you aren't declaring that at all.

Please thoroughly check and replicate the exact parameters between your SQL and Node, and please check the MSDN article I posted to ensure that your collation and variable values are correct.

Thanks for the response, Yes there is a difference but most of them are NULL value in stored procedure. Regarding the code for IsDeleted that was due to some issue in copying sorry for it

Collation configured for my database is SQL_Latin1_General_CP1_CI_AS . Does it have some limatation with the operations in mssql ?

[dhensby]

@deepanigi the first thing you need to do is provide us with code to replicate the problem.

At the moment the code you're provided is not code that can replicate the problem because it contains material errors that would prevent it from running.

Please provide a working code sample that reproduces the issue.

[deepanigi]

@deepanigi the fist thing you need to do is provide us with code to replicate the problem.

At the moment the code you're provided is not code that can replicate the problem because it contains material errors that would prevent it from running.

Please provide a working code sample that reproduces the issue.
I have mentioned the script and stored procedure below.

Table Type Declared

Nodejs Script File
nodejs.txt
```js
var sql = require("mssql");
var guid = require("guid");
let pool;
const config = {
user: 'hello',
password: 'password',
server: 'database.windows.net',
database: 'DB_DEV',

options: {
    encrypt: true,
},

};

const LocationPricingTableTyp = new sql.Table();
LocationPricingTableTyp.columns.add('Key', sql.UniqueIdentifier);
LocationPricingTableTyp.columns.add('LocationId', sql.Int);
LocationPricingTableTyp.columns.add('LowestPrice', sql.Decimal);
LocationPricingTableTyp.columns.add('HighestPrice', sql.Decimal);
LocationPricingTableTyp.columns.add('PriceType', sql.VarChar);
LocationPricingTableTyp.columns.add('Summary', sql.Text);
// Add rows// Values are in same order as columns.
let key = guid.raw();
LocationPricingTableTyp.rows.add(key, 777, 12.0, 15.0, 'young', 'summary');
DBLocationMaster().then(function (value) {

});

async function DBLocationMaster() {
try {
pool = await sql.connect(config);

    let SQLrequest = await pool.request();

    SQLrequest.input('LocationId', sql.Int, '123');
    SQLrequest.input('Language', sql.VarChar, 'en');
    SQLrequest.input('IsActive', sql.Bit, true);
    SQLrequest.input('TblLocationPrice', LocationPricingTableTyp);
    SQLrequest.execute("sp_LocationMaster_Create", (err, recordset) => {
        if (err) {
            console.log('Location not added due to following error');
            console.log(err);
        }
        else {
            console.log('Location successfully added to Master');
        }
    });

} catch (err) {
    // ... error checks
    console.log(err);
}

}
```

StoredProcedure

SP.txt

[deepanigi]

Hi all, Is this is a bug in mssql or something wrong from my end?

[dhensby]

@deepanigi if you're unable to diagnose where the bug is originating from then you'll have to wait for us to find some time to do so.

This is quite a complicated issue and I'd point out this is also free and open source software provided without warranty, any support provided is at the good will of anyone who provides it and, as such, is subject to their time and availability constraints.

you could narrow down the cause of the bug by first trying to run the SP directly against the SP (as you've done and state it's working as expected) and then by attempting to run the query with tedious directly; if neither of those error then it points to an issue in this package

[willmorgan]

@deepanigi If you can provide a sample table schema and some minimal data, I can use my reproduction repo for testing and take a closer look.

[dhensby]

OK - I've run this locally and I'm getting the same error. I'm really no expert on SQL server / TableTypes or what the heck is really going on here... It's going to take someone with a bit more knowledge to look into this.