x

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, 2011 at 02:56 AM in Default

Mohanraj gravatar image

Mohanraj
1 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, 2011 at 03:54 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x305

asked: Dec 09, 2011 at 02:56 AM

Seen: 900 times

Last Updated: Dec 09, 2011 at 04:37 AM