field added to replicated tables?

Hi all,
i found a column "msrepl_synctran_ts" have been added to one of my replicated tables but not found in other replicated tables. I
guess this field may be added by the DB server, i would like to ask under what situation or configuration this field will be added
and the field is timestamp type (the replication is trasactional replication). thanks
Hi Cweb,
Thanks for your post.
From your descriptions, I understood that you would like to know why there
will be a column named "msrepl_synctran_ts" in one of your replication
tables. Have I understood you? Correct me if I was wrong.
Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
tables if you publish the table and enable it for 'Immediate updating
subscriptions'. If it was allowed, a Subscriber can modify subscription
data and have those changes replicated to the Publisher.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing ,
it is me again, the strange thing is that i have not enable the "Immediate updating subscriptions" option but the column have
been added. Can i remove it? or need to remove the subscription to truncate the coulumn and push subscription again? thanks
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:%23doEXJTrEHA.1344@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your post.
> From your descriptions, I understood that you would like to know why there
> will be a column named "msrepl_synctran_ts" in one of your replication
> tables. Have I understood you? Correct me if I was wrong.
> Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
> tables if you publish the table and enable it for 'Immediate updating
> subscriptions'. If it was allowed, a Subscriber can modify subscription
> data and have those changes replicated to the Publisher.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Nice to see you again and thanks for your prompt updates!
Based on my scope, if you do not want your tables to be replicated, you
could definitely delete this column, otherwise, it is not recommend to do
this. msrepl_synctan_ts is the timestamp column that replication uses to
determine which is the latest version of a given row accross the servers
participating in the replication. My suggest is let SQL Server handle the
column. If you have big concerns with this additional column, please send
me your replication script and I would love to see whether there is any
workaround for us to omit this.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
here is the script of table and publication
CREATE TABLE [dbo].[MisTransactionReply] (
[TransID] [int] IDENTITY (1, 1) NOT NULL ,
[RequestTransID] [int] NOT NULL ,
[CompanyID] [varchar] (15) NOT NULL ,
[City] [varchar] (3) NULL ,
[UserID] [varchar] (15) NOT NULL ,
[SenderPIMA] [varchar] (35) NULL ,
[RecipientPIMA] [varchar] (35) NULL ,
[CAR] [varchar] (35) NOT NULL ,
[MsgType] [varchar] (6) NOT NULL ,
[MsgID] [int] NOT NULL ,
[AWBPrefix] [varchar] (3) NULL ,
[AWBSuffix] [varchar] (8) NULL ,
[Origin] [varchar] (3) NULL ,
[Destination] [varchar] (3) NULL ,
[Remark] [varchar] (100) NULL ,
[CreateDT] [datetime] NOT NULL ,
[MsgDateTime] [varchar] (10) NULL ,
[Unread] [bit] NOT NULL ,
[Deleted] [bit] NOT NULL ,
[msrepl_synctran_ts] [timestamp] NOT NULL --original schema not have this column
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MisTransactionReply] ADD
CONSTRAINT [DF_MisTransactionReply_RequestTransID] DEFAULT (0) FOR [RequestTransID],
CONSTRAINT [DF_MisTransactionReply_CreateDT] DEFAULT (dateadd(hour,(-8),getdate())) FOR [CreateDT],
CONSTRAINT [DF_MisTransactionDetail_Unread] DEFAULT (1) FOR [Unread],
CONSTRAINT [DF_MisTransactionReply_Deleted] DEFAULT (0) FOR [Deleted],
CONSTRAINT [PK_MisTransactionReply] PRIMARY KEY NONCLUSTERED
(
[TransID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply] ON [dbo].[MisTransactionReply]([CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_1] ON [dbo].[MisTransactionReply]([MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_10] ON [dbo].[MisTransactionReply]([SenderPIMA], [CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_2] ON [dbo].[MisTransactionReply]([MsgID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_3] ON [dbo].[MisTransactionReply]([Origin]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_4] ON [dbo].[MisTransactionReply]([Destination]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_5] ON [dbo].[MisTransactionReply]([AWBPrefix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_6] ON [dbo].[MisTransactionReply]([AWBSuffix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_7] ON [dbo].[MisTransactionReply]([CreateDT]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_8] ON [dbo].[MisTransactionReply]([SenderPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_9] ON [dbo].[MisTransactionReply]([RecipientPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_11] ON [dbo].[MisTransactionReply]([SenderPIMA], [CreateDT], [MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_12] ON [dbo].[MisTransactionReply]([RequestTransID]) ON [PRIMARY]
GO
--Publication.sql--
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'ACE_GLSP2', @.optname = N'publish', @.value = N'true'
GO
use [ACE_GLSP2]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.restricted = N'false', @.sync_method = N'native',
@.repl_freq = N'continuous', @.description = N'Transactional publication of ACE_GLSP2 database from Publisher DB02.TRAXON.NET.',
@.status = N'active', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.retention =
0
exec sp_addpublication_snapshot @.publication = N'ACE_GLSP2_UserData_Trans_Reply',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1,
@.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'TXNEDS\SQLAdmin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.source_owner = N'dbo',
@.source_object = N'MisTransactionReply', @.destination_table = N'MisTransactionReply', @.type = N'logbased', @.creation_script = null,
@.description = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000000000063, @.status = 16, @.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_MisTransactionReply', @.del_cmd = N'CALL sp_MSdel_MisTransactionReply', @.upd_cmd = N'MCALL
sp_MSupd_MisTransactionReply', @.filter = null, @.sync_object = null, @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article filter
EXEC sp_articlefilter @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.filter_name =
N'dbo.FLTR_MisTransactionReply_1__36', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article synchronization object
EXEC sp_articleview @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.view_name =
N'SYNC_ACE_GLSP2_UserData_Trans_Reply_MisTransacti onReply', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'all', @.subscriber = N'192.168.0.1',
@.destination_db = N'ACE_GLSP2', @.sync_type = N'automatic', @.update_mode = N'read only'
GO
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:tK1eXo2rEHA.2500@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Nice to see you again and thanks for your prompt updates!
> Based on my scope, if you do not want your tables to be replicated, you
> could definitely delete this column, otherwise, it is not recommend to do
> this. msrepl_synctan_ts is the timestamp column that replication uses to
> determine which is the latest version of a given row accross the servers
> participating in the replication. My suggest is let SQL Server handle the
> column. If you have big concerns with this additional column, please send
> me your replication script and I would love to see whether there is any
> workaround for us to omit this.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Thanks for your detailed script providing such detailed information.
I setup a transactional replication based on the scripts you provided and
it seems not adding that additional column "msrepl_synctran_ts" on Table
[MisTransactionReply]. You could pick up a test machine and have a try on
this.
Based on my scope, Published table will cause upgrade to fail so that
column msrepl_synctran_ts gets added to SQL Server tables, if you publish
the table and enable it for 'Immediate updating subscriptions'. This column
will not be removed even after unpublishing the table OR uninstalling
replication. During an SMS upgrade we add values to the table and fail
because of this new column that is created.
You could run the following script to update all the sysobjects.replinfo
column to 0
/***********************************/
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
sp_configure 'allow updates', 0
go
reconfigure with override
go
/***********************************/
Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
from the table.
If the you have multiple tables that need the timestamp column removed, the
following script will drop the timmestamp column for every table that has
one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
removal.
/*************************************/
DECLARE repl_remove CURSOR
FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
= 'u'
and a.id = b.id
and b.name = 'msrepl_synctran_ts'
OPEN repl_remove
DECLARE @.TableName sysname
FETCH NEXT FROM repl_remove into @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
FETCH NEXT FROM repl_remove into @.TableName
END
CLOSE repl_remove
DEALLOCATE repl_remove
GO
/***********************************************/
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||thx
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Mingqing,
i got this error when trying to delete the column, should i stop the publishing first and delete the column and re-publish
again?
'MisTransactionReply' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter the table 'MisTransactionReply' because it is being
published for replication.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi CWeb,
Thanks for your prompt updates!
Yes, you will have to stop the replication if you want to delete this
column.
Note that make sure you have a full backup of the database if it has
critical data for you.
Tell me the result of your test and let me know whether you have any
questions or concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!