Table type conflict with TVP error in store procedure after upgrading versions

[MattHoffmanSW]

I recently upgraded our project from version 2.0.11 to version 4.5.0, and have experienced several errors running a large stored procedure with several table types and TVPs in play. We are using ODBC Driver 17 on Windows 10. The errors tend to read like this:

Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data for the table-valued parameter 6 doesn't conform to the table type of the parameter. SQL Server error is: 8169, state: 2

An example of this error is when we provide the following data:

[{
  uuid: 'A2FF83F2-963F-4553-8837-D785E20707E3',
  inspectionUuid: 'ECA07CC2-DF99-4868-999A-F14A2CC1A949',
  identifierChecklistUuid: 'D274B030-A019-4C00-8396-01E53388E5FE',
  inquiryId: '58BF1BE5-3FB7-4562-A239-27AFE621317C',
  inspectionFindingId: '420B660C-B959-4B2B-90D9-55ACB657D94D',
  parentFindingId: null,
  deadline: '',
  requirementsValue: '["58BF1BE5-3FB7-4562-A239-27AFE621317C"]',
  classificationValue: '["2CA61A42-6F25-48E3-84EF-424692E3E1CC"]',
  title: 'Specific Rights',
  text: '<p>Violation</p><p></p><p></p>',
  correctionRequired: 1,
  findingAddedBy: 'Matt Hoffman',
  findingAddedOn: '04/17/2025',
  displayOrder: '0'
}]

to the following stored procedure segment:

USE [SansWrite X v10.0.6 Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[saveInspection]
	@options keyValuePairs READONLY,
	@accountSettings accountSettings READONLY,
	@inspectionAttachmentsData inspectionAttachmentsData READONLY,
	@inspectionFindingCorrectionsData inspectionFindingCorrectionsData READONLY,
	@inspectionFindingHistoryData inspectionFindingHistoryData READONLY,
	@inspectionFindingsData inspectionFindingsData READONLY,
...
...
...
AS
BEGIN
	SET XACT_ABORT ON
BEGIN TRANSACTION
SAVE TRANSACTION saveInspectionTransaction
BEGIN TRY
	SET NOCOUNT ON;
...
...
...
	insert into @existingInspectionFindingsDataUuids select uuid from inspectionFindingsData where inspectionUuid in ( select uuid from @inspectionUuids)
	insert into @newUuids select uuid from @inspectionFindingsData where uuid not in (select uuid from @existingInspectionFindingsDataUuids)	
	update inspectionFindingsData
	set uuid = s.uuid
		,inspectionUuid = s.inspectionUuid
		,identifierChecklistUuid = s.identifierChecklistUuid
		,inquiryId = s.inquiryId
		,inspectionFindingId = s.inspectionFindingId
		,parentFindingId = s.parentFindingId
		,deadline = s.deadline
		,requirementsValue = s.requirementsValue
		,classificationValue = s.classificationValue
		,title = s.title
		,text = s.text
		,correctionRequired = s.correctionRequired
		,findingAddedBy = s.findingAddedBy
		,findingAddedOn = s.findingAddedOn
		,displayOrder = s.displayOrder
		,lastUpdated = CURRENT_TIMESTAMP
	from @inspectionFindingsData s
	inner join inspectionFindingsData t
	on s.uuid = t.uuid

	insert into inspectionFindingsData select *, CURRENT_TIMESTAMP from @inspectionFindingsData where uuid in (select uuid from @newUuids)	
	delete from @newUuids
...
...
...
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	THROW
	IF @@TRANCOUNT > 0
	BEGIN
		ROLLBACK TRANSACTION saveInspectionTransaction	
	END
END CATCH
END

using the following user table type:

USE [SansWrite X v10.0.6 Dev]
GO
CREATE TYPE [dbo].[inspectionFindingsData] AS TABLE(
	[uuid] [uniqueidentifier] NOT NULL,
	[inspectionUuid] [uniqueidentifier] NOT NULL,
	[identifierChecklistUuid] [uniqueidentifier] NOT NULL,
	[inquiryId] [uniqueidentifier] NOT NULL,
	[inspectionFindingId] [uniqueidentifier] NULL,
	[parentFindingId] [uniqueidentifier] NULL,
	[deadline] [varchar](50) NULL,
	[requirementsValue] [varchar](max) NULL,
	[classificationValue] [varchar](max) NULL,
	[title] [varchar](max) NULL,
	[text] [varchar](max) NULL,
	[correctionRequired] [varchar](5) NULL,
	[findingAddedBy] [varchar](100) NULL,
	[findingAddedOn] [varchar](50) NULL,
	[displayOrder] [int] NULL
)
GO

This specific example does not work with an array of length 1, but works once another item is added to the array. Unfortunately, a different table type conflict comes up for a different TVP in that case.
The data is converted to a TVP and added to the procedure using the following code:

const tableTypeForTvp = {...tableType};
tableTypeForTvp.addRowsFromObjects(data);
const tvp = sql.TvpFromTable(tableType);
tvps.push(tvp);

const pm = connection.procedureMgr();
pm.get(storedProcedureName, proc => {
    proc.call(tvps, (err, results, output) => {

Despite the error seeming to originate from the ODBC Driver, the only modifications between a working version of this process and the version where we are seeing this error is a change from Node 14 to Node 22, and a change from msnodesqlv8 2.0.11 to 4.5.0. The data, table types, stored procedure, and driver have not been touched, outside of nullifying a single piece of data that seems to violate a length limit in TVPs.

Has there been a significant change to how TVPs and stored procedures are managed between these versions? If this behavior is intended, guidance on how to debug and resolve these errors would be appreciated.

[TimelordUK]

I would have to check when always on encryption was added.  This makes binding parameters much harder and needs to be precise. This was a very significant change. Sent from my iPhoneOn 24 Apr 2025, at 22:32, MattHoffmanSW ***@***.***> wrote: MattHoffmanSW created an issue (TimelordUK/node-sqlserver-v8#362) I recently upgraded our project from version 2.0.11 to version 4.5.0, and have experienced several errors running a large stored procedure with several table types and TVPs in play. We are using ODBC Driver 17 on Windows 10. The errors tend to read like this: Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data for the table-valued parameter 6 doesn't conform to the table type of the parameter. SQL Server error is: 8169, state: 2 An example of this error is when we provide the following data: [{ uuid: 'A2FF83F2-963F-4553-8837-D785E20707E3', inspectionUuid: 'ECA07CC2-DF99-4868-999A-F14A2CC1A949', identifierChecklistUuid: 'D274B030-A019-4C00-8396-01E53388E5FE', inquiryId: '58BF1BE5-3FB7-4562-A239-27AFE621317C', inspectionFindingId: '420B660C-B959-4B2B-90D9-55ACB657D94D', parentFindingId: null, deadline: '', requirementsValue: '["58BF1BE5-3FB7-4562-A239-27AFE621317C"]', classificationValue: '["2CA61A42-6F25-48E3-84EF-424692E3E1CC"]', title: 'Specific Rights', text: '<p>Violation</p><p></p><p></p>', correctionRequired: 1, findingAddedBy: 'Matt Hoffman', findingAddedOn: '04/17/2025', displayOrder: '0' }] to the following stored procedure segment: USE [SansWrite X v10.0.6 Dev] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[saveInspection] @options keyValuePairs READONLY, @accountsettings accountSettings READONLY, @inspectionAttachmentsData inspectionAttachmentsData READONLY, @inspectionFindingCorrectionsData inspectionFindingCorrectionsData READONLY, @inspectionFindingHistoryData inspectionFindingHistoryData READONLY, @inspectionFindingsData inspectionFindingsData READONLY, ... ... ... AS BEGIN SET XACT_ABORT ON BEGIN TRANSACTION SAVE TRANSACTION saveInspectionTransaction BEGIN TRY SET NOCOUNT ON; ... ... ... insert into @existingInspectionFindingsDataUuids select uuid from inspectionFindingsData where inspectionUuid in ( select uuid from @inspectionUuids) insert into @newUuids select uuid from @inspectionFindingsData where uuid not in (select uuid from @existingInspectionFindingsDataUuids) update inspectionFindingsData set uuid = s.uuid ,inspectionUuid = s.inspectionUuid ,identifierChecklistUuid = s.identifierChecklistUuid ,inquiryId = s.inquiryId ,inspectionFindingId = s.inspectionFindingId ,parentFindingId = s.parentFindingId ,deadline = s.deadline ,requirementsValue = s.requirementsValue ,classificationValue = s.classificationValue ,title = s.title ,text = s.text ,correctionRequired = s.correctionRequired ,findingAddedBy = s.findingAddedBy ,findingAddedOn = s.findingAddedOn ,displayOrder = s.displayOrder ,lastUpdated = CURRENT_TIMESTAMP from @inspectionFindingsData s inner join inspectionFindingsData t on s.uuid = t.uuid insert into inspectionFindingsData select *, CURRENT_TIMESTAMP from @inspectionFindingsData where uuid in (select uuid from @newUuids) delete from @newUuids ... ... ... COMMIT TRANSACTION END TRY BEGIN CATCH THROW IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION saveInspectionTransaction END END CATCH END using the following user table type: USE [SansWrite X v10.0.6 Dev] GO CREATE TYPE [dbo].[inspectionFindingsData] AS TABLE( [uuid] [uniqueidentifier] NOT NULL, [inspectionUuid] [uniqueidentifier] NOT NULL, [identifierChecklistUuid] [uniqueidentifier] NOT NULL, [inquiryId] [uniqueidentifier] NOT NULL, [inspectionFindingId] [uniqueidentifier] NULL, [parentFindingId] [uniqueidentifier] NULL, [deadline] [varchar](50) NULL, [requirementsValue] [varchar](max) NULL, [classificationValue] [varchar](max) NULL, [title] [varchar](max) NULL, [text] [varchar](max) NULL, [correctionRequired] [varchar](5) NULL, [findingAddedBy] [varchar](100) NULL, [findingAddedOn] [varchar](50) NULL, [displayOrder] [int] NULL ) GO This specific example does not work with an array of length 1, but works once another item is added to the array. Unfortunately, a different table type conflict comes up for a different TVP in that case. The data is converted to a TVP and added to the procedure using the following code: const tableTypeForTvp = {...tableType}; tableTypeForTvp.addRowsFromObjects(data); const tvp = sql.TvpFromTable(tableType); tvps.push(tvp); const pm = connection.procedureMgr(); pm.get(storedProcedureName, proc => { proc.call(tvps, (err, results, output) => { Despite the error seeming to originate from the ODBC Driver, the only modifications between a working version of this process and the version where we are seeing this error is a change from Node 14 to Node 22, and a change from msnodesqlv8 2.0.11 to 4.5.0. The data, table types, stored procedure, and driver have not been touched, outside of nullifying a single piece of data that seems to violate a length limit in TVPs. Has there been a significant change to how TVPs and stored procedures are managed between these versions? If this behavior is intended, guidance on how to debug and resolve these errors would be appreciated. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[TimelordUK]

I have added a test on the latest version, we had an issue binding arrays of unique identifiers

https://github.com/TimelordUK/node-sqlserver-v8/blob/master/test/tvp.test.js

this attempts to set up what you have above, im afraid if this still does not work you will have to change the test to exactly show the problem as else it takes too much time.

  it('test inspectionFindingsData TVP issue - single vs multiple rows', async function handler () {
    const promises = env.theConnection.promises
    const tableName = 'inspectionFindingsData'

    // Clean up any previous test artifacts first (this is the missing piece!)
    await promises.query(`IF OBJECT_ID('testInspectionFindings', 'P') IS NOT NULL DROP PROCEDURE testInspectionFindings`).catch(() => {})
    await promises.query(`IF TYPE_ID(N'dbo.inspectionFindingsDataType') IS NOT NULL DROP TYPE dbo.inspectionFindingsDataType`).catch(() => {})

    // Test data - single row that was failing
    const singleRowData = [{
      uuid: 'A2FF83F2-963F-4553-8837-D785E20707E3',
      inspectionUuid: 'ECA07CC2-DF99-4868-999A-F14A2CC1A949',
      identifierChecklistUuid: 'D274B030-A019-4C00-8396-01E53388E5FE',
      inquiryId: '58BF1BE5-3FB7-4562-A239-27AFE621317C',
      inspectionFindingId: '420B660C-B959-4B2B-90D9-55ACB657D94D',
      parentFindingId: null,
      deadline: '',
      requirementsValue: '["58BF1BE5-3FB7-4562-A239-27AFE621317C"]',
      classificationValue: '["2CA61A42-6F25-48E3-84EF-424692E3E1CC"]',
      title: 'Specific Rights',
      text: '<p>Violation</p><p></p><p></p>',
      correctionRequired: '1',
      findingAddedBy: 'Matt Hoffman',
      findingAddedOn: '04/17/2025',
      displayOrder: 0
    }]

    // Use table builder to create the TVP
    const mgr = env.theConnection.tableMgr()
    const builder = mgr.makeBuilder(tableName)

    // Define the table structure using builder
    builder.addColumn('uuid').asUniqueIdentifier().notNull()
    builder.addColumn('inspectionUuid').asUniqueIdentifier().notNull()
    builder.addColumn('identifierChecklistUuid').asUniqueIdentifier().notNull()
    builder.addColumn('inquiryId').asUniqueIdentifier().notNull()
    builder.addColumn('inspectionFindingId').asUniqueIdentifier().null()
    builder.addColumn('parentFindingId').asUniqueIdentifier().null()
    builder.addColumn('deadline').asVarChar(50).null()
    builder.addColumn('requirementsValue').asVarCharMax().null()
    builder.addColumn('classificationValue').asVarCharMax().null()
    builder.addColumn('title').asVarCharMax().null()
    builder.addColumn('text').asVarCharMax().null()
    builder.addColumn('correctionRequired').asVarChar(5).null()
    builder.addColumn('findingAddedBy').asVarChar(100).null()
    builder.addColumn('findingAddedOn').asVarChar(50).null()
    builder.addColumn('displayOrder').asInt().null()

    // Setup table and TVP using builder pattern (like BuilderChecker.checkTvp)
    const table = builder.toTable()
    await builder.drop()
    await builder.create()

    // Create the TVP type and procedure using builder-generated SQL
    const procName = builder.insertTvpProcedureName
    const dropType = builder.dropTypeSql
    const userTypeSql = builder.userTypeTableSql
    const tvpProcSql = builder.insertProcedureTvpSql

    const prochelper = env.procTest({
      name: procName,
      sql: tvpProcSql
    })

    await prochelper.drop()
    await promises.query(dropType)
    await promises.query(userTypeSql)
    await promises.query(tvpProcSql)

    // Test with single row - this was failing in the original issue
    const tvpTableSingle = await promises.getUserTypeTable(builder.typeName)
    tvpTableSingle.addRowsFromObjects(singleRowData)
    const tvpSingle = env.sql.TvpFromTable(tvpTableSingle)

    try {
      await promises.callProc(procName, [tvpSingle])
      console.log('Single row TVP test passed')

      // Verify the data was inserted correctly
      const singleResult = await promises.query(`SELECT * FROM ${tableName}`)
      expect(singleResult.first.length).to.equal(1)
      expect(singleResult.first[0].uuid.toLowerCase()).to.equal(singleRowData[0].uuid.toLowerCase())
    } catch (error) {
      console.error('Single row TVP test failed:', error.message)
      throw error
    }

    // Clear table for next test
    await promises.query(`DELETE FROM ${tableName}`)

    // Test with multiple rows - this was working in the original issue
    const multiRowData = [...singleRowData, {
      uuid: 'B3FF83F2-963F-4553-8837-D785E20707E4',
      inspectionUuid: 'ECA07CC2-DF99-4868-999A-F14A2CC1A949',
      identifierChecklistUuid: 'D274B030-A019-4C00-8396-01E53388E5FE',
      inquiryId: '58BF1BE5-3FB7-4562-A239-27AFE621317C',
      inspectionFindingId: '420B660C-B959-4B2B-90D9-55ACB657D94D',
      parentFindingId: null,
      deadline: '',
      requirementsValue: '["58BF1BE5-3FB7-4562-A239-27AFE621317C"]',
      classificationValue: '["2CA61A42-6F25-48E3-84EF-424692E3E1CC"]',
      title: 'Another Finding',
      text: '<p>Another Violation</p>',
      correctionRequired: '1',
      findingAddedBy: 'Jane Doe',
      findingAddedOn: '04/18/2025',
      displayOrder: 1
    }]

    const tvpTableMulti = await promises.getUserTypeTable(builder.typeName)
    tvpTableMulti.addRowsFromObjects(multiRowData)
    const tvpMulti = env.sql.TvpFromTable(tvpTableMulti)

    try {
      await promises.callProc(procName, [tvpMulti])
      console.log('Multiple row TVP test passed')

      // Verify the data was inserted correctly
      const multiResult = await promises.query(`SELECT * FROM ${tableName} ORDER BY displayOrder`)
      expect(multiResult.first.length).to.equal(2)
      expect(multiResult.first[0].uuid.toLowerCase()).to.equal(multiRowData[0].uuid.toLowerCase())
      expect(multiResult.first[1].uuid.toLowerCase()).to.equal(multiRowData[1].uuid.toLowerCase())
    } catch (error) {
      console.error('Multiple row TVP test failed:', error.message)
      throw error
    }

    // Clean up
    await prochelper.drop()
    await promises.query(dropType)
    await builder.drop()
  })
})