Alternate Synchronisation Partner

I am using merge replication and trying to configure an alternate
synchronisation partner (ASP). My topology is V-shape and replication is
working OK as long as I do not perform a sync of the subscriber with the ASP.
My subscription contains articles having identity columns managed by merge
replication, and this is casuing me some grief due to error 'Violation of
PRIMARY KEY constraint...Cannot insert duplicate key in object
'MSrepl_identity_range'.'. For ASP, it is not possible to have different
publications to that of the Primary publisher, hence disabling automatic
range management is not an option. Does anyone have an answer to this problem?
Also, does anyone know if it is possible to implement redundancy with merge
replication, similar to a peer-to-peer topology available with transactional
replication in SQL Server 2005?
Paul
MCDBA, MCSE
ASP do not provide redundancy to merge replication topologies. What they do
is allow a merge subscriber to connect with another publisher if the main
publisher goes offline for a brief time. It is unable to parcel out new
identity ranges.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Mu" <PaulMu@.discussions.microsoft.com> wrote in message
news:51DAB05E-232F-4FF0-A89D-2B7EF6859258@.microsoft.com...
> I am using merge replication and trying to configure an alternate
> synchronisation partner (ASP). My topology is V-shape and replication is
> working OK as long as I do not perform a sync of the subscriber with the
ASP.
> My subscription contains articles having identity columns managed by merge
> replication, and this is casuing me some grief due to error 'Violation of
> PRIMARY KEY constraint...Cannot insert duplicate key in object
> 'MSrepl_identity_range'.'. For ASP, it is not possible to have different
> publications to that of the Primary publisher, hence disabling automatic
> range management is not an option. Does anyone have an answer to this
problem?
> Also, does anyone know if it is possible to implement redundancy with
merge
> replication, similar to a peer-to-peer topology available with
transactional
> replication in SQL Server 2005?
> --
> Paul
> MCDBA, MCSE

Alternate snapshot location for merge replication subscriber

Hi,
I'm trying to set up a subscriber for merge replication over https. The
initial snapshot file is about 50 Gigs and I'm wondering if its
possible to download & store this snapshot somewhere other than on my
database drive (I have size contraints). Any ideas or direction?
Thanks,
JC
Hi
Yes this feature is available for merge replication, there is some more
information here about alternate snapshot locations.
http://msdn.microsoft.com/library/de...limpl_3vcj.asp
Nabila Lacey
<jbzcooper@.gmail.com> wrote in message
news:1139947715.833574.258470@.g47g2000cwa.googlegr oups.com...
> Hi,
> I'm trying to set up a subscriber for merge replication over https. The
> initial snapshot file is about 50 Gigs and I'm wondering if its
> possible to download & store this snapshot somewhere other than on my
> database drive (I have size contraints). Any ideas or direction?
> Thanks,
> JC
>
|||As well as Nabila's advice, you might want to consider using winzip 9.0 or
winrar to speed up the data transfer. Compression is available in SQL Server
but is limited to the 2GB limitation of a CAB file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you both. I suppose I should have been more clear. I have no
control over the publication itself and thus cannot specify an
alternate location on the publisher. I was told that the drive I was
replicating to needed at least 100GB free to house both the snapshot
and the database it would be loaded into. Is it possible for me to zip
and download the snapshot to my subscriber (assuming they will let me)
on an alternate drive and point my subscription to that?
I do appreciate the help,
Jeremiah
|||Jeremiah,
the alternative snapshot location I was referring to is basically a
subscriber setting. the method you propose is exactly what I have done in
the past for large publications.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Alternate Snapshot Folder during Setup in 2005

One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?

Hi Rich,

Although you can't set alternate snapshot folder locations in publication setup wizard, you can change it immediately in "Publication Properties" dialog. Please see http://msdn2.microsoft.com/en-us/library/ms151745.aspx.

If you would like to specify default snapshot location, you can set it in distributor property dialog (http://msdn2.microsoft.com/en-us/library/ms151258.aspx)

Peng

|||This is true, but if you are pulling a subsription and you use the ability to setup multiple subscribers all at once then I have to go to the properties of each subscription and change the alternate folder location which is a pain.|||

What is the purpose of using alternate snapshot location, are you no longer in need of using the default snapshot location? You're not required to need both, you can set the default to be the location of the alternate, then you don't have to worry about specifying altnernate snapshot location. See books online topic "Alternate Snapshot Folder Locations". ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/437553b0-19df-4522-8f27-06b5bc747c69.htm.

|||

In this case I do require both. There is a publisher in Toronto that I have limited control over because it is owned y a different company. My distributor is also in Toronto (different facility) where the snapshots are storedin RAW format for my subscribers in Toronto. I then have a process where I compress the snapshots and FTP them to a server I have in Vancouver and ucompress them for my subscribers there. So in this case I need both the default and alternate location. I have about 16 publications like that and about 8 go to each subsciber that I have and I have about 13 subscribers currently.

I'll check out BOL on that matter as you suggest.

Thanks

Rich

Alternate Replication Partner and Identity Ranges

Hi,
I am trying to setup my merge replication to use Alternate Replication
Partners.
What I want to do is to have PubA as the main publisher and PubB as a named
pull subscriber to PubA and a republisher.
Then, there is SubA, which subscribes to PubA (pull/anonymous).
It works fine. I can switch SubA from PubA to PubB, however, the identity
ranges are not working. In other words, when SubA runs off IDs it doesn't
get new IDs from PubB.
Any ideas? Thanks, Jos.
Note that I followed the steps in
http://support.microsoft.com/default...b;en-us;321176 to set this
up.
Alternate Sync Partners do not support the incrementing of identity ranges.
From BOL in the section marked Alternate Synchronization Partners
a.. When using automatic identity range handling, a Subscriber must
synchronize with its primary Publisher to receive a new identity range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OYyo9Qx1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am trying to setup my merge replication to use Alternate Replication
> Partners.
> What I want to do is to have PubA as the main publisher and PubB as a
named
> pull subscriber to PubA and a republisher.
> Then, there is SubA, which subscribes to PubA (pull/anonymous).
> It works fine. I can switch SubA from PubA to PubB, however, the identity
> ranges are not working. In other words, when SubA runs off IDs it doesn't
> get new IDs from PubB.
> Any ideas? Thanks, Jos.
>
> Note that I followed the steps in
> http://support.microsoft.com/default...b;en-us;321176 to set this
> up.
>
|||Thanks.
PS: My BOL doesn't have that clarification. I guess I have an outdated
version of the documentation (ugrhhhh).
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uQLFmA31FHA.2964@.TK2MSFTNGP09.phx.gbl...
> Alternate Sync Partners do not support the incrementing of identity
ranges.[vbcol=seagreen]
> From BOL in the section marked Alternate Synchronization Partners
>
> a.. When using automatic identity range handling, a Subscriber must
> synchronize with its primary Publisher to receive a new identity range.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OYyo9Qx1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> named
identity[vbcol=seagreen]
doesn't[vbcol=seagreen]
this
>

Altering table structure with out deleting replication ?

Dear Members
Is there a way 2 update the table structure that is part of an article
without deleting the replication ?
Best Regards
Shahid Saleem
*** Sent via Developersdex http://www.codecomments.com ***
Shahid,
the best you can do is sp_repladdcolumn and sp_repldropcolumn. Combinations
of these can be used to alter existing column definitions (see
http://www.replicationanswers.com/AddColumn.asp). This all changes in SQL
Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Altering table & trans. replication

Hi,

How can I modify table with publication (change of one column length)
without completely breaking replication.

Thanks in advance"Wagner" <wagner@.email.t-com.hr> wrote in message
news:1x0k6ml5is0vs$.mmdq6nunj5l6$.dlg@.40tude.net.. .
> Hi,
> How can I modify table with publication (change of one column length)
> without completely breaking replication.

Besides the method you found, I've also done the following:

Create a NEW column of the type you want, call it foo_temp.

Copy data into it.

Then sp_repldropcolumn on the existing column.

Then sp_repladdcolumn with the same name, but new definition.

Copy data back.

> Thanks in advance

Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

Altering a Published Table

Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:

Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.

Any ideas, what has gone wrong??

I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.|||Originally posted by joejcheng
Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.

No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||When you add a columns that is not PK, and you dont wish to reinitiliase during operation hours, you can choose this way, try to create a column by using Publisher properties, go to Filter columns in Publisher properties, then add the column using the Add column button, and then dont tick on that column you just added. Now you didnt ask merge agent to replicate that column for you. What you doing is to add a column for your user to make use of that column in the publisher database server. Wait till you have time to replicate the whole snapshot off the operation hours. Good Luck !

Originally posted by TALAT
No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||Thanx for your help eshl. I use to apply the SNAPSHOT only when there's a change in the PK or an ID seed column of a published table. The publication-database has 24 hours production and the subscribers have online websites running on them. I am eagerly lookin for a way to completely bypass the SNAPSHOT.|||http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20800513.html

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.
Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

Alter table with merge replication

Hello,
We are using sql2000.
1.Is there a better way to change field data type with
merge replication than add column, copy data and then drop
column?
2.How can i create index with merge replication?
3.If the only way is stop and restart the replication,
what is the easiest way to do so?
Many Many Thanks For Reply.
>
> We are using sql2000.
> 1.Is there a better way to change field data type with
> merge replication than add column, copy data and then drop
> column?
No, there isn't. You use sp_repladdcolumn to add the new column and then
sp_repldropcolumn to remove the old column. Note that ALTER TABLE and ALTER
COLUMN does this transparently.

> 2.How can i create index with merge replication?
>
You use the @.schema_option argument of the sp_repladdcolumn to generate a
corresponding index. A value of 0x010 will generate a clustered index and
0x40 will generate a nonclustered index.

> 3.If the only way is stop and restart the replication,
> what is the easiest way to do so?
You don't need to break merge replication to perform make schema changes in
SQL Server 2000.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

Alter table with merge replication

Hello,
We are using sql2000.
1.Is there a better way to change field data type with
merge replication than add column, copy data and then drop
column?
2.How can i create index with merge replication?
3.If the only way is stop and restart the replication,
what is the easiest way to do so?
Many Many Thanks For Reply.>
> We are using sql2000.
> 1.Is there a better way to change field data type with
> merge replication than add column, copy data and then drop
> column?
--
No, there isn't. You use sp_repladdcolumn to add the new column and then
sp_repldropcolumn to remove the old column. Note that ALTER TABLE and ALTER
COLUMN does this transparently.
> 2.How can i create index with merge replication?
>
--
You use the @.schema_option argument of the sp_repladdcolumn to generate a
corresponding index. A value of 0x010 will generate a clustered index and
0x40 will generate a nonclustered index.
> 3.If the only way is stop and restart the replication,
> what is the easiest way to do so?
--
You don't need to break merge replication to perform make schema changes in
SQL Server 2000.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

Alter table that is masked for replication

Hi,
I have a snapshot replication between two SQL server 2000 (Main Svr & Backup
Svr). At present the replication is working well but now due enhancement i
need to alter soem of the table. When I perform the alteration to the 'Main
Svr' I get a error message 'Cannot delete and create table and it is used by
replication'.
How can I alter the table?
After alteration will this be reflected back to 'Backup Svr'?
The 'Backup Svr' is hosted as another SQL instance on a Win2k server. How
can I connect to this second instance using ADODB (V2.8) from visual basic.
Thanks
Hari
Hi Paul,
Thanks for that let me try it.
Hope you could also help me on this.
How can i connect to secodn sql instance using ADODB in VB.
Thanks,
Hari
"Paul Ibison" wrote:

> To add a column, use sp_repladdcolumn. To drop one use
> sp_repldropcolumn. To change an existing column, you
> could add a new column with the new datatype
> (sp_repladdcolumn), do an update on the table to populate
> the column, then drop the column (sp_repldropcolumn). Do
> this again to create the column having the same original
> name. Alternatively you could use:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> sp_refreshsubscriptions @.publication ='northwindxxx'
> And do the opposite to add back in once the change has
> been made.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Alter table in transactional Replication

I have a table that is being used in transactional replication. On publisher
side table is altered and a column is added. I would want the new column also
to be replicated.
Here is what i did-
- drop the subscription
- remove article from publication
- add back article
- modify insert update stored procedures at subscriber
- add the subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
You can use sp_repladdcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Alter table impossible due to replication

Hi !
I can't alter a table because of an old problematical replication.
I've already tried to clean the replication informations with
sp_removedbreplication and sp_msunmarkreplinfo. I've already update
sysobjects to set replinfo to 0 for the table that I try to update. I've
also the database from the replication configuration.
When I try to alter the table, I receive this message in french :
table 'Espaces'
- Impossible de modifier la table.
Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de ALTER
TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
In english, I think it would be : Error ALTER TABLE DROP COLUMN because
'xxxx' is at the moment replicated.
I hope someone can help me !
Thanks in advance !!!!
Bernard
bernard.borsu@.odysseos.net
can you try this:
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
(thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi !
> I can't alter a table because of an old problematical replication.
> I've already tried to clean the replication informations with
> sp_removedbreplication and sp_msunmarkreplinfo. I've already update
> sysobjects to set replinfo to 0 for the table that I try to update. I've
> also the database from the replication configuration.
> When I try to alter the table, I receive this message in french :
> table 'Espaces'
> - Impossible de modifier la table.
> Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de
ALTER
> TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
> In english, I think it would be : Error ALTER TABLE DROP COLUMN because
> 'xxxx' is at the moment replicated.
> I hope someone can help me !
> Thanks in advance !!!!
> --
> Bernard
> bernard.borsu@.odysseos.net
>
|||Thanks for your solution, but i've already tried this solution and the error
message is always the same.
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de news:
Odw1pwB%23EHA.1296@.TK2MSFTNGP10.phx.gbl...
> can you try this:
> EXEC sp_configure 'allow',1
> go
> reconfigure with override
> go
> use your_database_name
> go
> update sysobjects set replinfo = 0 where name = 'your_table_name'
> go
> EXEC sp_configure 'allow',0
> go
> reconfigure with override
> go
> (thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
> news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> ALTER
>

alter replication triggers

I have a customer application where I utilize merge replication between
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

ALTER PK

As I'm in the process of planning replication of two
tables to from one database in one location (country) to
another location (another country) I need to change the
tables using an ALTER TABLE statement so that the UNIQUE
index acting as the primary key, changed to a declared
primary key.
Since I will be applying this change to a live production
environment will there be any issues when applying this
change.
The key column is defined currently as:
data type: uniqueidentifier
allow nulls: no
default value: newid()
Thanks,
AnjelinaAnjelina
I don't see any negative issues if you are going to apply this change. If a
table is large then it will take time to arrange keys with ascending order.
BOL says:
"Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique
index on the specified columns in the "table
"anjelina" <ajturner@.canada.com> wrote in message
news:078701c38e21$cd144af0$a401280a@.phx.gbl...
> As I'm in the process of planning replication of two
> tables to from one database in one location (country) to
> another location (another country) I need to change the
> tables using an ALTER TABLE statement so that the UNIQUE
> index acting as the primary key, changed to a declared
> primary key.
> Since I will be applying this change to a live production
> environment will there be any issues when applying this
> change.
> The key column is defined currently as:
> data type: uniqueidentifier
> allow nulls: no
> default value: newid()
> Thanks,
> Anjelina
>|||If the primary key constraint will be a clustered index, all of the
non-clustered indexes will automatically be re-built. This can take some
time on a large table, and users will not be able to access the table
during this time.
Kick everyone off of the system prior to beginning.
drop the unique constraint.
then create the primary key constraint.
When complete, be sure to back up the database with a full or differential
backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"anjelina" <ajturner@.canada.com> wrote in message
news:078701c38e21$cd144af0$a401280a@.phx.gbl...
> As I'm in the process of planning replication of two
> tables to from one database in one location (country) to
> another location (another country) I need to change the
> tables using an ALTER TABLE statement so that the UNIQUE
> index acting as the primary key, changed to a declared
> primary key.
> Since I will be applying this change to a live production
> environment will there be any issues when applying this
> change.
> The key column is defined currently as:
> data type: uniqueidentifier
> allow nulls: no
> default value: newid()
> Thanks,
> Anjelina
>

alter indentity field

Hi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
ShaffiqHi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
Shaffiq

alter table table_name
alter column column_Name int not null


that should solve your problem.|||Hi Enigma,
I'd tried it before but it not work. Even query analyzer return success message "The command(s) completed successfully." but when I open the table it still the same. And the identity field still ON

Regards,
Shaffiq|||do alteration in Enterprise Manager( dont save it) and click on 'save change script'(3 rd button from second row).copy and run that script in query analyser|||or

Alter Table MyTable ADD NewColumn int
GO
UPDATE MyTAble SET NewColumn = OldColumn
GO
ALTER TABLE MyTable DROP COLUMN MyCOlumn
GO
sp_rename 'MyTable.NewColumn','OldColumn',COLUMN