replication alternatives

Anyone know of third party tools that would do the same
thing? Maybe without alter replicated tables restriction?
TIA, ChrisR
Chris,
Datamirror is sometimes mentioned in this newsgroup. I suppose a solution
could also be based on datacompare.
The main alternative that comes up is of course DTS/log shipping. These
solutions are for different scenarios, but often fit the bill. Using DTS if
you have updating subscribers can be painful however to set up.
BTW, transactional/snapshot replication (standard) doesn't alter the schema.
HTH,
Paul Ibison

Replication - Alter Column Data Type (How)

I've looked everywhere on this one and not found one jot of info

All I wanna do is alter a Char(40) Column to a Char(80)

SQL 2k sp3 - Merge Replication

Can anyone tell me how to do this without having to Halt Production in a Live environment ?

Looks like either a Drop Subscription

or

Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.

Both methods involve taking the system offline even though it's not for long.

This Column has no Constraints or anything

If this type of Schema Change is possible then this Column is the simplest.

Any help appreciated

thx

GW

PS. I hope someone posts on this one - I don't seem to have much response with Replication Q'sIn general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page. In some cases, other schema changes must include to reinitialize the publication.

As its a production database and you're looking for major change in column size then obviously you need to have outage to make sure it affected without any issues. (suggested)

In any case you have to define the outage if not do it in less traffic hour.

The other option of adding temp. column and export data and again re-define the alterations on previous columns is fine though it will not cause outage..|||thx Satya

did'nt quite get what U meant by

In general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page

Is this the same as adding - dropping - recreating the field as in my second option ?

Anyway I'll try
Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.

I'll post back if it fails

thx

GW|||Yes, after a thorough look up I suggest to use sp_repladdcolumn sp_repldropcolumn SPs as its a schema change.|||I used Enterprise Manager (Publication Properties) GUI to achieve same thing (sp_repladdcolumn etc) cos it's quicker & I'm Idle

It would of worked as well if it was'nt for the fact that the statement

Update my_Table SET NewCol = OldColumn

Left me with different Values in some of the records in the NewCol

dunno Why - we are using some kind of Binary Data in each Char Value.

The Extended Charachter set that displays with a select certainly looks different in about 5% of the records

O well - Onwards Forever Onwards

GW|||Glad it worked, but always remember to use SPs and functions provided which will give you a chance to get to know the system functionality rather depending on EM, it helps.

Replication

undefined

Hi,

I have successfully replicated a database (type : Transactional), but when i have executed a Alter scripts to effect the schema of the database it worked. My concern is whether the replication was done successfully or not ?
Can we change the schema of a replicated database by plain Alter scripts ?
Plz let me know....

Thanks
RakeshDepending on what you altered. If you want to alter rules or foreign keys without checking replication, you don't have to do anything to the replication setup. But if you change table schma, you need to either use the replication system procedures to change it or you have to drop all subscriptions and initialize them.|||I am able to alter the table schema by using Replication stored procedures, but my concern is, i am also able to change the table schema by simple alter statements on the primary database, but these changes are not making into secondary database i,e subscriber database. Do we get any error Msg trying to alter a table schema of a replicated database ?|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP

In case of merge replication it is not possible to alter repilcated table but for transaction replication it works. Welcome to Microsoft...