How I update only tabelstructure without the table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How I update only tabelstructure without the table

Author

Topic

ch.reinke
Starting Member

2 Posts

Posted - 2003-03-12 : 09:21:36

Hi,
I use a Snapshot Replication. I want to update the tablestructure (add a column or delete a relation), but without
update the data in the table.

I use the following Properties:
- Article - propertie: "Delete Data in the existing table that matches the row filter statement"
- Row - Filter: "... WHERE id < 0" (no rows returns with this condition)

I create an Initial Snapshot and apply it to my Subscriber, but the table - structur dont change.

Has the Replication a other way to do this?

Ch. Reinke

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-03-12 : 12:10:37

If you're talking about SQL Server 7 then remove the publication > make the table structure changes > re-replicate

If you're using SQL2K then it may be possible to drop single tables from replication > make structure changes > add single table back into replication ? ala SQL Server 6.5

check some literature - BOL, book etc

===========
Paul

ch.reinke
Starting Member

2 Posts

Posted - 2003-03-13 : 03:41:43

@KnooKie

Its not the problem to declare in the publication, that i want to add or delete a column.

The problem is that the Replication creates the following Script in the Initial Snapshot:

When I using the propertie: "Delete Data in the existing table that matches the row filter statement"

SET QUOTED_IDENTIFIER ON
GO
delete from [TestTable1] where TestTable1.id < 0
GO
SET ANSI_PADDING ON
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTable1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TestTable1] (
[id] [int] NOT NULL ,
[val1] [int] NULL ,
[val2] [int] NULL ,
[val3] [int] NULL ,
[newcol2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
)
END
GO

When I using the propertie: "DROP the existing table an re-create it"

SET QUOTED_IDENTIFIER ON
GO
drop table [TestTable1]
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [TestTable1] (
[id] [int] NOT NULL ,
[val1] [int] NULL ,
[val2] [int] NULL ,
[val3] [int] NULL ,
[newcol2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
)
GO

The first Script dont change my structure and the second sript delete my data.

I need a script like script like this:

ALTER TABLE [TestTable] ADD [newcol2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources