SQL Server Performance

Replication between SQL Server 2000 & 2005 with user defined data types.

Discussion in 'SQL Server 2005 Replication' started by DHR, Aug 24, 2007.

  1. DHR New Member

    I have a situation at the moment with a replication job between 2000and 2005. SQL Server 2000 is the publisher with 2005 pulling, the replicationjob fails due to the following error 'Cannot insert an explicit value into atimestamp column. Use INSERT with a column list to exclude the timestampcolumn, or insert a DEFAULT into the timestamp column.'.

    The value it is trying to insert is a user defined data type on the 2000instance, on the 2005 the user defined data types have somewhere along theline been converted to standard SQL Server data types just on one table, these data typesare present in the 2005 instance and on other tables just not the one which iscausing this replication issue.

    My first port of call was to assign the correct user defineddata types to the fields within the troublesome table, I then had to recreatethe snapshot to get the ball rolling again - the replication then kicked in andfailed with the error (again!)
    Reviewing the replication logs and then having a closer look at the troublesometable on the 2005 it is clear that the data types on the 2005 side for thisparticular table have been converted to standard data types. Other tables withthe same user defined data types have been created successfully on the 2005side, is there any specific reason this won't work with just the one table? Others have been replicated successfully and contain all of the correct user defined data types, why not this one table?
    Any pointers would be fantastic :)

  2. satya Moderator

    Welcome to the forum!
    Have a look at this FAQ post in this regard.
  3. DHR New Member

    Hi thanks for your reply, i've reviewed the link you've sent me and the datetime field is the cause of the issue, what i don't understand though is why the same problem hasn't occured on other tables within this replication job where the datatypes are the same?

Share This Page