Bulk insert not possible for tables that contain timestamp columns

[peterdouce]

When a table contains a timestamp (rowversion) column (for optimistic concurrency) it's currently impossible to do a bulk insert as it tries to insert a value in a column that can't be assigned. This is a similar issue as with the "generated always / hidden" columns in #99

Expected behaviour:

Timestamp columns shouldn't be included in the generated insert statement or should get DEFAULT as value. See error output.

Actual behaviour:

bulk insert fails with the following error

RequestError: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
    at done (.\node_modules\mssql\lib\msnodesqlv8\request.js:192:21)
    at .\node_modules\msnodesqlv8\lib\table.js:1085:25
    at runNextTicks (node:internal/process/task_queues:61:5)
    at processImmediate (node:internal/timers:437:9) {
  code: 'EREQUEST',
  originalError: [Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.] {
    sqlstate: '23000',
    code: 273,
    severity: 16,
    serverName: 'FSM075652',
    procName: '',
    lineNumber: 1
  },
  number: 273,
  lineNumber: 1,
  state: '23000',
  class: 16,
  serverName: 'FSM075652',
  procName: ''
}

Software versions

• NodeJS: 16.14
• node-sqlserver-v8: 2.4.8 (issue is also in 2.5.0)
• SQL Server: 2016

Possible fix

When I add "|| col.type === 'timestamp'" in table.js it works but there may be a better solution?

readOnly (col) {
    return (col.is_identity || col.is_computed || col.is_hidden || col.generated_always_type || col.type === 'timestamp')
}

[TimelordUK]

Thanks a lot I will try to reproduce and get back with a fix if yours works we can take that.

…

Sent from my iPad

On 6 Jul 2022, at 15:40, peterdouce ***@***.***> wrote:  When a table contains a timestamp (rowversion) column (for optimistic concurrency) it's currently impossible to do a bulk insert as it tries to insert a value in a column that can't be assigned. This is a similar issue as with the "generated always / hidden" columns in #99 Expected behaviour: Timestamp columns shouldn't be included in the generated insert statement or should get DEFAULT as value. See error output. Actual behaviour: bulk insert fails with the following error RequestError: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. at done (.\node_modules\mssql\lib\msnodesqlv8\request.js:192:21) at .\node_modules\msnodesqlv8\lib\table.js:1085:25 at runNextTicks (node:internal/process/task_queues:61:5) at processImmediate (node:internal/timers:437:9) { code: 'EREQUEST', originalError: [Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.] { sqlstate: '23000', code: 273, severity: 16, serverName: 'FSM075652', procName: '', lineNumber: 1 }, number: 273, lineNumber: 1, state: '23000', class: 16, serverName: 'FSM075652', procName: '' } Software versions NodeJS: 16.14 node-sqlserver-v8: 2.4.8 (issue is also in 2.5.0) SQL Server: 2016 Possible fix When I add "|| col.type === 'timestamp'" in table.js it works but there may be a better solution? readOnly (col) { return (col.is_identity || col.is_computed || col.is_hidden || col.generated_always_type || col.type === 'timestamp') } — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.

[TimelordUK]

your fix is fine - it is currently on master but will be a couple weeks before release as am testing MS v18 driver and have some other fixes to look at

[peterdouce]

ok thanks for the update