How to duplicate table structure in Transactional replication?

Dear all:
After I create a new publication and its subscription by Transactional
replication in SQL server 2000,I want to add a column or alter a column
length in publication table and hope that the column can be duplicated to
subscription table.However,I find that Transactional replication in SQL
server 2000 can only replicate data and can not replicate table structure.
The replication type I use is "Transactional replication".
My problem is:
How to duplicate table structure in Transactional replication of SQL server
2000?
On 8 Jun, 04:45, gzwangyang <gzwangy...@.discussions.microsoft.com>
wrote:
> Dear all:
> After I create a new publication and its subscription by Transactional
> replication in SQL server 2000,I want to add a column or alter a column
> length in publication table and hope that the column can be duplicated to
> subscription table.However,I find that Transactional replication in SQL
> server 2000 can only replicate data and can not replicate table structure.
> The replication type I use is "Transactional replication".
> My problem is:
> How to duplicate table structure in Transactional replication of SQL server
> 2000?
What you're trying to do isn't strictly possible. You can't alter a
table that is published for replication.
You would need to drop replication, change the table and then re-
enable the replication with snapshot enabled. Provided your articles
snapshot attributes are correct it will create the table at the
subscriber.
Thanks
James
|||This is not correct. You can use sp_repladdcolumn and sp_repldropcolumn.
Altering a column is not straightforward but is achievable
(http://www.replicationanswers.com/AddColumn.asp).
Cheers,
Paul Ibison

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
MichaelYou first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:
> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > Hi,
> > I have a table with a column:
> > rv smallint default 1 not null
> >
> > I want to drop it:
> > alter table myTable
> > drop column rv
> >
> > But I get an error:
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> > column.
> >
> > The 'object' mentioned above is the default constraint.
> > How do I just drop the column (without having to know waht constraints it
> > has)?
> > Thanks
> > Michael
> >
>
>|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
> > You first have to drop the default constraint
> >
> > alter table myTable
> > drop constraint DF__ccAstPublica__rv__27C3E46E
> >
> > Then drop the cilumn
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > Hi,
> > > I have a table with a column:
> > > rv smallint default 1 not null
> > >
> > > I want to drop it:
> > > alter table myTable
> > > drop column rv
> > >
> > > But I get an error:
> > > Server: Msg 5074, Level 16, State 1, Line 1
> > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
'rv'.
> > > Server: Msg 4922, Level 16, State 1, Line 1
> > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
this
> > > column.
> > >
> > > The 'object' mentioned above is the default constraint.
> > > How do I just drop the column (without having to know waht constraints
it
> > > has)?
> > > Thanks
> > > Michael
> > >
> >
> >
> >|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
>> > 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
>> > this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints
>> > it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:
> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> > > You first have to drop the default constraint
> > >
> > > alter table myTable
> > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > >
> > > Then drop the cilumn
> > >
> > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > Hi,
> > > > I have a table with a column:
> > > > rv smallint default 1 not null
> > > >
> > > > I want to drop it:
> > > > alter table myTable
> > > > drop column rv
> > > >
> > > > But I get an error:
> > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> 'rv'.
> > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> this
> > > > column.
> > > >
> > > > The 'object' mentioned above is the default constraint.
> > > > How do I just drop the column (without having to know waht constraints
> it
> > > > has)?
> > > > Thanks
> > > > Michael
> > > >
> > >
> > >
> > >
>
>|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:
> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> >> > 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> >> > this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints
> >> > it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
> > Sure
> >
> > What you can do is query the sysconstraints table in your db
> > and join it to syscolumns
> >
> >
> > select s2.name, object_name(s1.constid)
> > from sysconstraints s1
> > inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id =s2.id
> > and s1.colid = s2.colid and s1.status = 133141)
> >
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > > Armand,
> > > Thanks. Yes, I know I have to drop the constraint but...
> > > This is in a script and I don't know the auto generated name of the
> > > constraint, so I can't readily drop it.
> > > Is there a way to find out the constraint name for the DEFAULT for
that
> > > column?
> > > That way, I could fid the name then frop the constraint
programmatically.
> > > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > > Thanks
> > > Michael
> > >
> > > "Armando Prato" wrote:
> > >
> > > > You first have to drop the default constraint
> > > >
> > > > alter table myTable
> > > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > > >
> > > > Then drop the cilumn
> > > >
> > > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > > Hi,
> > > > > I have a table with a column:
> > > > > rv smallint default 1 not null
> > > > >
> > > > > I want to drop it:
> > > > > alter table myTable
> > > > > drop column rv
> > > > >
> > > > > But I get an error:
> > > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> > 'rv'.
> > > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > > ALTER TABLE DROP COLUMN rv failed because one or more objects
access
> > this
> > > > > column.
> > > > >
> > > > > The 'object' mentioned above is the default constraint.
> > > > > How do I just drop the column (without having to know waht
constraints
> > it
> > > > > has)?
> > > > > Thanks
> > > > > Michael
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Tibor
Thanks for the helpful advice :).
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||Yes, DEFAULTs are handles like constraints in SQL Server. Where in ANSI SQL, they are just column
attributes. Just one of those things to get used to... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:0B2F4A00-65E5-4DF1-B2C6-86CA9C21C4D8@.microsoft.com...
> Tibor
> Thanks for the helpful advice :).
> Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
> not DEFAULTs. I think this particular aspect of SQL Server's design is not
> particularly helpful. To my eye, it's much clearer to define:
> myColumn smallint default 1
> than
> myColumn smallint,
> constraint MyTab_DF_MyColumn default 1 for MyColumn
> Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
> allow me to drop a column that has an impicit not null, but not one with an
> implicit DEFAULT.
> Ho hum...
> Bet this isn't 'fixed' in 2005...
> Regards
> Michael
> "Tibor Karaszi" wrote:
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> This is why you should name your constraint in the first place... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>> > Armand,
>> > Thanks. Yes, I know I have to drop the constraint but...
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> > Is there a way to find out the constraint name for the DEFAULT for that
>> > column?
>> > That way, I could fid the name then frop the constraint programmatically.
>> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
>> > Thanks
>> > Michael
>> >
>> > "Armando Prato" wrote:
>> >
>> >> You first have to drop the default constraint
>> >>
>> >> alter table myTable
>> >> drop constraint DF__ccAstPublica__rv__27C3E46E
>> >>
>> >> Then drop the cilumn
>> >>
>> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> >> > Hi,
>> >> > I have a table with a column:
>> >> > rv smallint default 1 not null
>> >> >
>> >> > I want to drop it:
>> >> > alter table myTable
>> >> > drop column rv
>> >> >
>> >> > But I get an error:
>> >> > Server: Msg 5074, Level 16, State 1, Line 1
>> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> >> > Server: Msg 4922, Level 16, State 1, Line 1
>> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> >> > column.
>> >> >
>> >> > The 'object' mentioned above is the default constraint.
>> >> > How do I just drop the column (without having to know waht constraints it
>> >> > has)?
>> >> > Thanks
>> >> > Michael
>> >> >
>> >>
>> >>
>> >>
>>