Changing column length of a replicated table

Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

Are you using SQL Server 2005? If so, you can turn on @.replicate_ddl option (set it to 1) for a publication then use regular "ALTER TABLE ... ALTER COLUMN ..." DDL syntax to increase the length of the column, the DDL change will be automatically replicated to subscriber by replication. (@.replication_ddl options is automatically turned on if you use management studio to create the publication)

Hope that helps,

Zhiqiang Feng

|||

Thank you Zhiqiang for your reply, but sorry we're using 2000.

Opal

|||

Sorry there is no easy workaround in SQL Server 2000. The only alternative is that you can use sp_repldropcolumn then sp_repladdcolumn to drop and re-create the column, but that way you will lose all the data for that column.

Thanks,

Zhiqiang Feng

Changed Table structure

Hi all,
How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)
Pls let me know
TIAFrom your release log...|||Can take help Lumigent's schema alert which is free as of now.
SQL doesn't hold this information.|||hey, how cool is that...installs on the server no?

How does it not have an impact on performance I wonder...

change timestamp from null to not null

Hi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
Many thanks,
Osk

>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>
Osk wrote:[vbcol=seagreen]
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
David Gugick
Imceda Software
www.imceda.com
|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
Many thanks,
Osk

>--Original Message--
>Osk wrote:
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
David Gugick
Imceda Software
www.imceda.com
|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

change timestamp from null to not null

Hi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
Many thanks,
Osk

>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>Osk wrote:[vbcol=seagreen]
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
David Gugick
Imceda Software
www.imceda.com|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
Many thanks,
Osk

>--Original Message--
>Osk wrote:
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
David Gugick
Imceda Software
www.imceda.com|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

Change ownership for User Defined Data Types

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to dbo, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank youThere are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
Neither do objects "inherit" changes made to User-defined datatype.
Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.|||Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
Anyway, I have already found the solution. This is the script:

sp_configure 'Allow Update',1
Reconfigure WITH OVERRIDE

Update sysTypes set uid = user_id('dbo') Where name = …

sp_configure 'Allow Update',0
Reconfigure WITH OVERRIDE