USE TEST1
GO
CREATE TABLE [dbo].T_Replication
(
ID [int] NOT NULL identity(1,1) CONSTRAINT [PK_TT_Replication] PRIMARY KEY CLUSTERED,
[Name] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[Submitdate] [datetime] NULL,
[EmailID] [varchar](100) NULL,
)
GO
Then once we set up the publication and subscription for the above table, we may alter the column name and data type:
exec sp_repladdcolumn @source_object = '[T_Replication]'
, @column = 'Email' -- New Column Name
, @typetext = 'varchar(75) NOT NULL' -- This is the new column type
, @publication_to_add = 'Test'
GO
UPDATE THE ADDED COLUMN WITH THE EXISTING COLUMN:
update [T_Replication] set Email = EmailID
GO
FINALLY DROP THE UNWANTED COLUMN:
exec sp_repldropcolumn @source_object = '[T_Replication]'
, @column = 'EmailID'
GO

TO ADD COLUMN AND DROP COLUMN WITHOUT DISTURBING THE EXISTING REPLICATION SET UP, ie, WITHOUT REINITIALIZING THE NEW SNAPSHOT:
ADD THE COLUMN AS YOU LIKE: (TESTED IN MS SQL Server 2000 and 2005)
GO
Also you can add constraint: Few scripts below may be useful while
Alter table dbo.Tablename drop constraint constraintname
GO
ALTER TABLE dbo.Tablename ADD DEFAULT ('UK') FOR [columnname]
GO
Exec sp_repldropcolumn @source_object = 'Tablename' , @column = 'columnname'
GO
Exec sp_repladdcolumn @source_object = 't_tablename'
, @column = 'columnname'
, @typetext ='varchar (10) not null default ''UK'' '
, @publication_to_add = 'All'
No comments:
Post a Comment