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.

Article
02/16/2015

I came across an interesting case which took a lot of efforts and understanding SQL server stored procedure code in replication space to nail down. Therefore, I would like to share the same. Hopefully it will help some of you.

=> Created a publication using “initialize with backup” and got the below error when the subscriber was created.

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. (Source: MSSQLServer,
Error number: 273)

=> It seems like we get the above error whenever the table in the publication has a timestamp column in the primary key. Example below,

CREATE TABLE [dbo].[Trick](
[Sno] [int] NOT NULL,[Sname] [varchar](50)
NULL, [timet] [timestamp] NOT NULL,CONSTRAINT [PK_Trick] PRIMARY KEY CLUSTERED ([Sno]ASC,[timet] ASC))ON [PRIMARY]

=> Create the publisher and subscriber databases,

Create database Test_Pub;
Create database Test_Sub;

=> Create below tables in both database. I am not following the actual process for “initialize with backup”, instead creating the required objects on both side and will not select the option to apply snapshot later, as I will not have data changes.

          CREATE TABLE [dbo].[Trick1](
                     [Sno] [int] NOT NULL,
                     [Sname] [varchar](50) NULL,
                     [timet] [timestamp] NOT NULL) ON[PRIMARY]

ALTER TABLE dbo.Trick1 ADD CONSTRAINT PK_Trick1 PRIMARY
KEY CLUSTERED ( Sno) ON [PRIMARY]
GO

=> Create a transactional read-only publication and a subscriber, make sure you dont enable snapshot.

=> We will get below error if you check the replication monitor.

          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.
          (Source: MSSQLServer, Error number: 273)

=> Verbose logging in distribution agent. -Output C:\ReplOutput.txt -OutputVerboseLevel 3

=> Error occurs while creating below stored procedure.

           2015-01-21 23:57:35.696 OLE DB
           Subscriber '<SQL instance>': if object_id(N'dbo.MSreplication_objects')
           is not null

delete from dbo.MSreplication_objects
           where object_name = N'sp_MSins_dboTrick1'
           2015-01-21 23:57:35.696 OLE DB Subscriber '<SQL instance Name>': create
           procedure [sp_MSins_dboTrick1] @c1 int, @c2 varchar(50),
           @c3 binary(8)
           as
           begin
           insert into [dbo].[Trick1]([Sno],[Sname], [timet]) values
(@c1, @c2, @c3)
           end
           2015-01-21 23:57:35.711 Agent

message code 273. 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.

2015-01-21 23:57:35.711 ErrorId = 25, SourceTypeId = 0
ErrorCode = '273'

ErrorText = '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.'

=> Not sure why we are seeing insert for @c3. We had "Convert TIMESTAMP to BINARY" set to false.

=> Recreate the publisher and check the article properties while selecting the articles and ensure "Convert TIMESTAMP to BINARY" is set to false.

=> Check the article properties again. For article Trick "Convert TIMESTAMP to BINARY" is set to true and for article trick1 "Convert TIMESTAMP to BINARY" is set to false.

=> If (Convert TIMESTAMP to BINARY -> False)
{

      Source and destination column will be timestamp
      @schema_Options -> 8 will be set for "Replicate timestamp columns.
If not set, timestamp columns are replicated as binary."
(Refer https://msdn.microsoft.com/en-us/library/ms173857.aspx)
    }

=> The insert stored procedure will have a “default” for the timestamp column, Example stored procedure below,

create procedure [dbo].[sp_MSins_dboTrick] @c1 int
as
begin
insert into [dbo].[Trick1]( [sno], [time]) values (@c1, default )
end

=> If (Convert TIMESTAMP to BINARY -> True)

{

       Source column will be timestamp and destination
column should be changed to binary.
       @schema_Options -> 0 will be set for "Replicate timestamp
columns. If not set, timestamp columns are replicated as binary."
       (Refer https://msdn.microsoft.com/en-us/library/ms173857.aspx)

}

=> The insert stored procedure will have an explicit variable inserting the value for the timestamp column, Example stored procedure below,

create procedure [dbo].[sp_MSins_dboTrick] @c1 int, @c2 binary(8) as
begin
insert into [dbo].[Trick]( [Sno], [Timet]) values (@c1, @c2)
end

=> It seems like the value for "Convert TIMESTAMP to BINARY" changes to TRUE automatically for articles that have timestamps in primary key column.

=> Ran a profiler trace on subscriber and able to see that we got the above error while creating the stored procedure.

=> Created a new database and tried creating the table that has timestamp column in primary key and tried creating the stored procedure. It gave me the same error. In our case I see that the table is already present in the subscriber, as we have initialized with backup.

=> Dropped the subscriber, started the profiler.

[$] The insert stored procedure sp_MSins_dbo… and the table gets
created at the same time.
[$] ALTER TABLE [dbo].[Trick] ADD CONSTRAINT [PK_Trick] PRIMARY
KEY CLUSTERED ([Sno],[timet]) gets created later.

=> This is the reason why we dont see this issue while using snapshot.

=> The issue with "Initialize with backup" is because when the insert stored procedure gets created the table with primary key is already present.

=> Workaround:

[$] Change the timestamp column as part of the primary key to BINARY data type on the subscriber database. Downside to this is that you need to change all the columns that has timestamp in primary key to BINARY on your subscriber database and this operation should be done every time you restore the backup while using “initialize with backup” option.

[$] Use snapshot instead of “Initialize with backup” option.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

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.

Additional resources