DBeaver :- Export Data – Timestamp / SQL Server – Error – “Cannot insert an explicit value into a timestamp column”

Background

Reviewing a system and wanted to get the data from one of it’s table and copy it into a new system.

Sample Data

SQL Server

DDL

DDL – Create Schema and Table


use [tempdb]
go

if schema_id('column.timestamp') is null
begin

    exec('create schema [column.timestamp] authorization [dbo]');

end
go

if object_id('[column.timestamp].[person]') is null
begin

    create table [column.timestamp].[person]
    (

          [id] int not null identity(1,1)

        , [ts] timestamp not null

    )

end
go

DML

DML – Add Data to Table


use [tempdb]
go

set nocount on
go

set XACT_ABORT on
go

truncate table [column.timestamp].[person]
go

insert into [column.timestamp].[person]
default values

insert into [column.timestamp].[person]
default values

go

Microsoft

SQL Server Management Studio ( SSMS )

SQL Server’s Management Studio capably manages such a task.

But, yesterday received an error.

Opted not to take the bait and troubleshoot further.

DBeaver

Launched Dbeaver and worked towards scripting the data from the table

Outline

Here are the steps :-

  1. Connect to the Database
  2. Transverse the Database Navigator outline and select Instance\Database\Schema\Tables
  3. Select the specific Table
  4. Right click on the selected table
    • From the dropdown menu, please choose to “Export Data
  5. The Data Transfer window opens; here is a quick workthrough
    • Panels
      • Panel – Data Transfer Target Type and Format
        • Choose SQL – Export to SQL Insert Statements
        • Click on the Next button
      • Panel – Settings Tables to Files, SQL
        • Extracting Settings
        • Format Settings
          • Binaries
            • Inline
          • Encoding
            • Native
          • Include generated columns
            • Unchecked
        • Click on the Next button
      • Panel – Data Transfer – Output
        • Copy to Clipboard
          • Checked
            • I only have a few records for testing and so it is OK to place in the clipboard
      • Panel – Data Transfer – Confirm

Images

Image – Database Navigator

database.navigator.01.20200325.0701PM.PNG

Image – Data Transfer Target Type and format

datatransfer.targetType.and.format.01.20200325.0555PM.PNG

Image – Settings ( Table to Files, SQL )

datatransfer.settings.tableToFile.01.20200325.0607PM.PNG

Image – Output

datatransfer.output.01.20200325.0608PM.PNG

Image – Confirm

datatransfer.confirm.01.20200325.0608PM.PNG

Generated SQL

Here is the generated SQL


<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>INSERT INTO tempdb.[column.timestamp].person (ts) VALUES
(0x00000000000007DA)
,(0x00000000000007DB)
;

Apply SQL

The next step is to apply the SQL on to a targeted system.

Error

Error Image

insert.data.02.20200325.0950PM

Error Text


Msg 273, Level 16, State 1, Line 1
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.

Remediation

Skip Generated Columns

One of the workarounds that should should have worked is to skip generated columns.

We tried that and no help this time.

BTW, in DBeaver that ability is offered through the “Settings ( Table to File, SQL) window; specifically “checking off” the “Include generated columns” checkbox.

Settings ( Table to File, SQL )

datatransfer.settings.tableToFile.01.20200325.0607PM.PNG

Versions Tested

Here are the versions we tested against :-

  1. DBeaver
    • 7.1
      • Date Released:-
        • 2020-June-1st
      • Dates Tested
        • 2020-June-11th
    •  7.0.1
      • Date Released:-
        • 2020-March-
    • 6.2.4

Bug Filed

  1. Dbeaver
    • SQL Server – Export Data – Timestamp column should be skipped when “Include generated columns” is unchecked
      Bug # :- 8278
      Link

Dedication

Always in dedication to Serge Rider and the rest of the gang at Dbeaver.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s