Not Receiving Result Sets from Stored Procedure After Informational Message

[mdpenguin]

MSSQL doesn't seem to be able to capture output record sets coming from a stored procedure that follows a message from PRINT, some ANSI warnings, or an informational message (i.e. severity <= 10).

Expected behaviour:

Capture all output from SQL Server regardless

Actual behaviour:

Configuration:

I was able to replicate with the following:

SP in SQL Server:

CREATE PROCEDURE [dbo].[NullEliminated] (@P1 TINYINT)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 'First query' 'FirstQuery';

    SELECT 'Second query' 'SecondQuery';

    PRINT 'That was second; now we do third';

    SELECT 'Third query' 'ThirdQuery';

    SELECT 'Fourth query' 'FourthQuery';
END

JS:

const sql = require('mssql/msnodesqlv8');
const config = {
    database: 'YourDBHere',
    server: 'YourServerHere',
    driver: 'msnodesqlv8',
    connectionTimeout: 1500,
    requestTimeout: 1500,
    options: {
        trustedConnection: true
    }
};

const pool = new sql.ConnectionPool(config);

async function testing() {
    await pool.connect();

    const request = new sql.Request(pool);
    request.on('info', info => {
        console.dir(info);
    });

    let result = await request
        .input('P1', 1)
        .execute('NullEliminated');

    console.dir(result.recordsets);
}

testing();

Output:

{
  message: 'That was second; now we do third',
  number: 0,
  state: '01000',
  class: 0,
  lineNumber: 14,
  serverName: 'YourServerHere',
  procName: 'NullEliminated'
}
[
  [ { FirstQuery: 'First query' } ],
  [ { SecondQuery: 'Second query' } ],
  [ { FourthQuery: 'Fourth query' } ]
]

Software versions

• NodeJS: 18.13.0
• node-mssql: 9.1.1
• SQL Server: Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64) Sep 2 2022 11:01:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

[mdpenguin]

I did some further testing and found that the issue is with the driver. I've filed a bug on the node-sqlserver-v8 project (https://github.com/TimelordUK/node-sqlserver-v8/issues/276).