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.
| Author |
Topic |
|
jkokado
Starting Member
5 Posts |
 Posted - 2005-05-25 : 19:29:25
|
| I have a fairly large production database that is replicated transactional. I am experimenting in using a backup and restore method to set up the replication due to its size. On the subscriber side is there a way to change the table definition for identity insert columns by using a TSQL script. I was told that using the not for replication option on the publisher side was not recommended for transactional replication.I am open for other suggestions to solve my dilema. Thanks! |
|
|
dursaliye
Starting Member
22 Posts |
Posted - 2005-05-25 : 20:48:17
|
| ALTER TABLE does what you want. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-25 : 21:41:31
|
| >>ALTER TABLE does what you want.actually, you can't use ALTER TABLE to change the identity property of a column.I'm not clear on what your objective is. You want to have the identity column replicated but without the identity property? Will you be writing to the subscriber table or just reading from it?Be One with the OptimizerTG |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-05-25 : 23:17:57
|
to change table definitions in the subscriber, you have to apply the change at the publisher, to change the column definition, you have to drop the article from the publication, but you have to drop the subscriptions as wellor if you want to reflect the change only at the subscriber's side, drop the publication all together and make the change at the subscriberi've used this before because there was a need and have not encountered any problems so farquote:
I was told that using the not for replication option on the publisher side was not recommended for transactional replication.
--------------------keeping it simple... |
|
|
|
jkokado
Starting Member
5 Posts |
Posted - 2005-05-26 : 11:56:38
|
| Here's some more info. In the publisher, there are identity columns in many tables. I will backup the database restore it on the subscriber then setup replication manually. However, after I setup the replication and start the agent I am getting an error "cannot update identity column pri-key". Which makes sense because it is trying to insert a record in a field willnot allow it to. I can go into enterprise manager and change identity = no for the column and replication continues until it finds another identity column again. My question is:Is there a way to disable all the identity columns using a t-sql only on the subscriber? Or any other suggestions? The standard way to set up replication is taking over 7 hours to do the snapshot and bulk inserts. This is why I am trying to set this up manually. |
|
|
|
|
|
|
Identity columns