x
login about faq Site discussion (meta-askssc)

Exclude a Column in the Trascation Replication

Hi SSC, How to exclude a column in a table with Trasaction replication, and replicate only the remaining columns. My table schema is

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IP_PERSON](
    [PE_LOGN] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PE_IDEN] [smallint] NULL CONSTRAINT [DF_IP_PERSON_PE_IDEN]  DEFAULT ((0)),
    [PE_EMID] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_NAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    **[PE_LOPW] [varbinary](15) NULL,**
    [PE_DEPT] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_DESG] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_JOIN] [datetime] NULL,
    [PE_QUIT] [datetime] NULL,
    [PE_CRDT] [datetime] NULL,
    [PE_MODT] [datetime] NULL,
    [PE_CRBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_MOBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_PSWD] [varbinary](15) NULL,
    [PE_PTYP] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IP_PERSON_PE_PTYP]  DEFAULT ('0'),
    [PE_MAIL] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_MCTC] [int] NULL CONSTRAINT [DF_IP_PERSON_PE_MCTC]  DEFAULT ((0)),
    [CU_IDEN] [smallint] NULL CONSTRAINT [DF_IP_PERSON_CU_IDEN]  DEFAULT ((0)),
    [PE_LOCN] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_ARCH] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IP_PERSON_PE_ARCH]  DEFAULT ('0'),
    [PE_PROX] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PE_LMGR] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [MSmerge_df_rowguid_042CC70CF90D43C69698564EC357D66D]  DEFAULT (newsequentialid()),
 CONSTRAINT [PK_IP_PERSON] PRIMARY KEY CLUSTERED 
(
    [PE_LOGN] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF.

I had bold the column whose data need not to be replicated. Kindly guide me in getting this done.

more ▼

asked Dec 09 '11 at 02:56 AM in Default

Mohanraj gravatar image

Mohanraj
1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can remove columns from the replicated article, so that only a subset of columns is replicated.

You can do this using SSMS, by navigating to the publication, then to the table inside the publication, then remove the tick from the columns you don't want replicating.

more ▼

answered Dec 09 '11 at 03:54 AM

WilliamD gravatar image

WilliamD
25.3k 16 18 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x237

asked: Dec 09 '11 at 02:56 AM

Seen: 465 times

Last Updated: Dec 09 '11 at 04:37 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.