| Author |
Topic |
|
yippy
Starting Member
7 Posts |
 Posted - 2005-04-05 : 02:03:39
|
Hi All I have 1 2k SQL server i want to setup Transactional replication to a second backup sql server. I want to keep the identity column the same, this is important because otherwise the custom application just don't work. I was told berifly by one of my friends that by modifying sp_scriptmappedupdproc, it is possible to do it with out writing script for every proc. Any help will be greatly appreicated |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-05 : 06:22:37
|
| identity columns are also replicated unless you specified otherwise, check the column property to verify--------------------keeping it simple... |
 |
|
|
yippy
Starting Member
7 Posts |
Posted - 2005-04-06 : 19:52:48
|
| When I setup Transactional Replication, Microsoft said not to replicat the identity columns. That why i flag the all the identity column not for replication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-06 : 20:20:55
|
| The column is replicated but it is no longer an identity column. Why does your application care if the subscription database has the identity property on these tables? The data will still be the same. Your application should only be reading from this database and not inserting rows.Tara |
|
|
|
yippy
Starting Member
7 Posts |
Posted - 2005-04-06 : 23:14:26
|
| Yes exactly it, it is no longer an identity column. I need to keep it an identity column propertie. |
|
|
|
yippy
Starting Member
7 Posts |
Posted - 2005-04-07 : 00:21:39
|
| I have managed to keep the identity propertie by using generated scripts but i am still having issues when update or insert it tell me can't do it to the identity column :..( |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-07 : 02:39:02
|
| i agree with Tara, you're not allowed to make any updates or insert on replicated tables, you'll get unsynchronized data causing the agent to failwhat do you need the property for?--------------------keeping it simple... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-07 : 12:30:02
|
quote:
Originally posted by yippy
Yes exactly it, it is no longer an identity column. I need to keep it an identity column propertie.
You'll need to look into merge replication instead of transactional replication.Tara |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-08 : 01:49:20
|
quote:
have 1 2k SQL server i want to setup Transactional replication to a second backup sql server
this means it's only a push right? all updates coming from the "1 2k SQL server" going to the "backup" server?merge is not the way, coz if you change the data on the other side, this gets reflected to your "real" server unless you can always assure that no updates will happen at the backup sidehelp us out in helping you, what do you need the identity property for?--------------------keeping it simple... |
|
|
|
yippy
Starting Member
7 Posts |
Posted - 2005-04-15 : 01:02:17
|
| Ok Done it, inserting and updating now working as well since the update proc on the subscriber want to update the identity column that was why it failed. I manually removed that line of code and it worked the fine. But manually editing every update proc for every table. It is very painful anyone got a faster way? |
|
|
|
yippy
Starting Member
7 Posts |
Posted - 2005-04-15 : 01:05:15
|
| The Reason I need the identity property because it is requested by the developer of our custom software. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-15 : 12:37:13
|
quote:
Originally posted by yippy
The Reason I need the identity property because it is requested by the developer of our custom software.
You need to talk to the developer then and tell them that you shouldn't be using replication then. You should be using the identity property on the publishing database and not on the subscribing database.Tara |
|
|
|
|
Transactional Replication Maintaining Identity Col