0

My typescript azure function, interacts with my SQL Server database. I can use TVPs to insert data into the database, except for when the receiving table type contains an IDENTITY column. My table has 3 columns, the first being an IDENTITY column.

When I pass a TVP with 3 columns I get the following error:

INSERT into an identity column not allowed on table variables

When I omit the identity column from the TVP and only send the remaining 2 columns, then it throws the following error:

Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires 3 column(s).

This is the definition of user defined table type on SQL Server:

[PropertyOrder] [INT] IDENTITY(1,1) NOT NULL,
[Name] [NVARCHAR](200) NOT NULL,
[Value] [NVARCHAR](MAX) NULL

The TVP that is created in TypeScript:

const tvp = new Table()
tvp.columns.add('PropertyOrder', Int)
tvp.columns.add('Name', NVarChar(200))
tvp.columns.add('Value', NVarChar(MAX))

tvp.rows.add(1, 'TestName', 'TestValue')

Software versions

  • NodeJS: 8.10
  • node-mssql: 5.1.0
  • SQL Server: Azure SQL database
tuathail
  • 48
  • 5
  • 1
    If you're inserting values into your column `Order` (which is a bad name for a column by the way `ORDER` is a [Reserved Keyword](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017)) why are you declaring it with the `IDENTITY` property? Either don't use the `IDENTITY` property and then you can insert values for it, or do use the property and don't. The point of the `IDENTITY` property is that SQL Server handles the value assigned to the column, not the user. – Thom A May 28 '19 at 09:06
  • how did you pass 2 columns instead of all columns of TVP? – Pugal May 28 '19 at 12:23
  • @Larnu @Pugal Thanks for the replies. I updated the column name in the sample code (i'd just truncated it when writing the original description). That's what I'm trying to do with the `IDENTITY` column, use it but not insert a value for it, but I get the later error above when I do this from the Azure function. I'll try update the post to clarify. – tuathail May 29 '19 at 10:43
  • From what I gather from the node-mssql docs (I've never used it myself) you need to actually tell it the column is an identity. Try `tvp.columns.add('PropertyOrder', sql.Int, {identity: true})`. – Jeroen Mostert May 29 '19 at 10:46
  • Hi @JeroenMostert I checked the source code [https://github.com/tediousjs/node-mssql/blob/master/lib/table.js] and it looks like the only options that are allowed are nullable and primary `column.nullable = options.nullable column.primary = options.primary` – tuathail May 29 '19 at 15:49

0 Answers0