msrepl_synctran_ts?

Hello experts,

I have a table A which has a column named msrepl_synctran_ts because of the replication we have on this table.

Now I have a new table B which is a backup table for table A and it has contained the field msrepl_synctran_ts as well, in fact all fields are indentical. It is one of our testing table and it must contain the field msrepl_synctran_ts.

I need to transfer some data from table A to this backup table B but whenever I do the below:

INSERT INTO B ([FieldA], [msrepl_synctran_ts])
  SELECT [FieldA], [msrepl_synctran_ts] FROM A

I get the following error:
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.

So I do this:
INSERT INTO B ([FieldA])
  SELECT [FieldA] FROM A

But I get this:
Cannot insert the value NULL into column 'msrepl_tran_version', table 'DB1.dbo.B'; column does not allow nulls. INSERT fails.

How do I copy data properly from A to B?

Thank you very much.

Best regards,
Fox

foxvisionAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
Are there any other columns in table B? Any of them set to not allow null?
Is the msrepl_synctran_ts column in tableB important?
If not then allow it to accept null... after checking the other columns and whether they can be null
foxvisionAuthor Commented:
No other columns are set to not allow NULL and we cannot change table B even though we know that field is not important hence our issue.
QPRCommented:
strange, sql should insert the timestamp at the time of the insert
how about

INSERT INTO B ([FieldA], [msrepl_synctran_ts])
  SELECT [FieldA], [NULL] FROM A
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

foxvisionAuthor Commented:
Thanks but I have got this:

Cannot insert the value NULL into column 'msrepl_tran_version', table 'DB1.dbo.B'; column does not allow nulls. INSERT fails.
QPRCommented:
Can you script out the table just to double check the datatype on this column
foxvisionAuthor Commented:
It says:
      [msrepl_synctran_ts] [timestamp] NOT NULL,

Thanks,
Fox
QPRCommented:
Clutching at straws now... When you view the table designer on tableA are there any defaults?
QPRCommented:
Clutching at straws now... When you view the table designer on tableA are there any defaults?
foxvisionAuthor Commented:
No default...
QPRCommented:
Certainly something strange here. Timestamp is a data type that SQL handles itself. In other words you don't insert or update it - SQL provides the value.
ok breaking down the problem... How about a plain insert rather than from the select.
Provide a value for each column but leave out the timestamp column

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
foxvisionAuthor Commented:
I figure out what's wrong.

Remember I showed you above comment about the error:

Cannot insert the value NULL into column 'msrepl_tran_version', table 'DB1.dbo.B'; column does not allow nulls. INSERT fails.

Notice that the field msrepl_synctran_ts is not in the issue now.

It's another column, a replicated one as well. And that has been taken care now.

You are right, we do not need to supply any values to it let SQL handles it.

My fault for not observing the error I have got.

Thanks and point given :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.