replication question

Hi guys,
I have a question, not sure if it's do-able.
I have a transaction replication setup on Server A (Publisher) and Server B
is my Subscriber) I have over 100 tables in this publication.
I want to change order of columns to just one table in Publisher. is
there a way that I can do it without dropping publication?
let said my table have column A , column B, column C
I want to change order to Column B, Column C , Column A
nope. You would have to drop replication, script out your tables, export
your database, drop the tables, recreate them with the correct column order
and then reimport the data.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:O%23hiljsuFHA.3588@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have a question, not sure if it's do-able.
> I have a transaction replication setup on Server A (Publisher) and Server
B
> is my Subscriber) I have over 100 tables in this publication.
> I want to change order of columns to just one table in Publisher. is
> there a way that I can do it without dropping publication?
>
> let said my table have column A , column B, column C
> I want to change order to Column B, Column C , Column A
>

Replication problems

Hello guys, we have a problem with the merge replication in my company, the
downloads replication is very very slow all times, the upload process is
very fast, but, when the download process starts, then the suscriptor crash
and the locks begins to appear in the database. The processor usage is 100
% and the disk activity is elevate too.
Excuse me for my bad English, please I need help, I don't know wit this
Regards
Its hard to say what is going on. You should review this white paper.
http://www.microsoft.com/technet/pro.../mergproc.mspx
and
http://www.microsoft.com/technet/pro.../mergperf.mspx
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
"Jesus Brito" <jesus.brito@.sigosa.com> wrote in message
news:O04tJibdFHA.720@.TK2MSFTNGP15.phx.gbl...
> Hello guys, we have a problem with the merge replication in my company,
the
> downloads replication is very very slow all times, the upload process is
> very fast, but, when the download process starts, then the suscriptor
crash
> and the locks begins to appear in the database. The processor usage is
100
> % and the disk activity is elevate too.
> Excuse me for my bad English, please I need help, I don't know wit this
> Regards
>

Replication Problem, Please Urgent Help!

Hello Guys,
I have set up a transactional replication with 2pc's.
One server is the publisher & the distributor, and the other one is the
subscriber.
Everything is fine, but the replication stops when my partner shut down
the subscriber pc, now I have to restart the replication, but I don't
know how to do it because I tried to restart the agents and it didn't
work.
Which agent is supossed to be restarted?.
Thanks,
*** Sent via Developersdex http://www.codecomments.com ***
Javier,
you'll need to restart the distribution agent. If it generates an error,
this might be because of the distribution retention period being exceeded.
Please can you post up any errors you see and we'll work it out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication problem

hi guys,
Just a question, I set up transaction replication between 2 servers.
it's working fine with data, table structures are also being replicated to
destination (I do reinitialize subscription). However, indexes and foreign
key are not being replicated. what is going on?
I checked the replication monitor, I dont' see any errors.
any hint?
thanks
kevin
By default the replication of fk's and pk's is not done. You can select to
include DRI in the articles property dialog. Right click on your
publication, select publication properties, click on the article tab, and
click on the browse button to the right of each table. In the snapshot tab
at the bottom you will find this option.
HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data is
flowing from your publisher to your subscriber and ideally your DML will be
occuring on your publisher not your subscriber. So your DRI will be
maintained on your publisher and there is no reason to include it on your
subscriber.
If you are doing DML on your subscriber as well and replication does work
with this, it is advisable to put the NFR attribute on your constraints on
your subscriber tables.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> hi guys,
> Just a question, I set up transaction replication between 2
> servers. it's working fine with data, table structures are also being
> replicated to destination (I do reinitialize subscription). However,
> indexes and foreign key are not being replicated. what is going on?
> I checked the replication monitor, I dont' see any errors.
> any hint?
> thanks
> kevin
>
|||okay.. you're right.
I shouldn't worry about foreign key, but I do worry about indexes.
also, I added an article to subscription using sp_addarticle, but when I
force to run snapshot agent, snapshot agent said
no snapshot is available. why doesn't snapshot agent know I just added an
article?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> By default the replication of fk's and pk's is not done. You can select to
> include DRI in the articles property dialog. Right click on your
> publication, select publication properties, click on the article tab, and
> click on the browse button to the right of each table. In the snapshot tab
> at the bottom you will find this option.
> HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data
> is flowing from your publisher to your subscriber and ideally your DML
> will be occuring on your publisher not your subscriber. So your DRI will
> be maintained on your publisher and there is no reason to include it on
> your subscriber.
> If you are doing DML on your subscriber as well and replication does work
> with this, it is advisable to put the NFR attribute on your constraints on
> your subscriber tables.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>
|||causer you have to issue a sp_refreshsubscriptions. You might have to
re-rerun the snapshot agent as well.
Keep in mind that on the subscriber the PK's are by default replaced by
unique indexes which are the functional equivalent of a pk, only it will
allow a single null - but the unique key is a clustered index.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
> okay.. you're right.
> I shouldn't worry about foreign key, but I do worry about indexes.
> also, I added an article to subscription using sp_addarticle, but when I
> force to run snapshot agent, snapshot agent said
> no snapshot is available. why doesn't snapshot agent know I just added an
> article?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||sorry, Last question.
it looks like after I did sp_refreshsubscriptions, the destination table
(subscriber) was truncated first, now it's buck copying data to destination
table at subscriber.
But originally, I had 5200000 rows in destination table(subscriber), In
same table in Publisher, It has 8000000 rows. I thought it would just copy
over 2800000 rows over instead of doing everything all over again. is
there a system procedure that can do such ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
> causer you have to issue a sp_refreshsubscriptions. You might have to
> re-rerun the snapshot agent as well.
> Keep in mind that on the subscriber the PK's are by default replaced by
> unique indexes which are the functional equivalent of a pk, only it will
> allow a single null - but the unique key is a clustered index.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||No there is no proc to do this. In SQL 2005 snapshots are restartable which
is probably what you are looking for.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:u8IS%23IO1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> sorry, Last question.
> it looks like after I did sp_refreshsubscriptions, the destination table
> (subscriber) was truncated first, now it's buck copying data to
> destination table at subscriber.
> But originally, I had 5200000 rows in destination table(subscriber), In
> same table in Publisher, It has 8000000 rows. I thought it would just
> copy over 2800000 rows over instead of doing everything all over again.
> is there a system procedure that can do such ?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
>

Replication over FTP

Hey guys.
Really stuck in a jam on this one.
SQL subscriber is behind a firewall, so FTP snapshots don't work. From
behind the firewall you can only connect to an external FTP server and
transfer data via FTP Passive mode. I can't find anywhere a setting for SQL
2000 to use passive mode.
Does anyone at all know if SQL 2000 does not support passive mode in any way?
Does anyone know a solution for this problem. We don't want to do VPN
because then we have to give each location direct access to our network. Is
there an option I'm perhaps missing here?
Thanks in advance.
Take care.
You could write a script that connects with an FTP server download the
required snapshot files, and then have SQL Server apply the snapshot from
an alternate location. You will see this option when you pull your
subscription.
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
|||Could I get an explination for what the snapshots are for?
I believe their only for initialization correct? Not the actual replication
merging, that happens on the 1433 port connection?
If so..
Can I, when setting up the database, export all objects to the server. Then
create the subscription, and I've tried to take the initialization step off
in the wizard, saying "subscriber already has schema" etc.
Then when I try and start the pull subscription I get this history in the
job history:
Bulk copying data into table 'sysmergesubsetfilters'
Message: The process could not deliver the snapshot to the Subscriber.
Message: The process could not bulk copy into table
'"dbo"."MSmerge_contents"'.
Message: Unable to open BCP host data-file
The process could not deliver the snapshot to the Subscriber. The step
failed.
Is my idea possible? Not use any snapshots if the server can already be
populated with the data via export?
Thanks for all the help!
"Hilary Cotter" wrote:

> You could write a script that connects with an FTP server download the
> required snapshot files, and then have SQL Server apply the snapshot from
> an alternate location. You will see this option when you pull your
> subscription.
> --
> 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
>
|||the snapshots are to create the schema on the subscriber and push the data
there. It also contains replication metadata which is normally used for
tracking. So, yes, it is for initialization. All replication activity takes
place over port 1433 except for the application of the snapshot which is
pulled or pushed through a unc or ftp port.
Unfortunately with merge replication even a no-sync snapshot requires a mini
snapshot to be deployed and applied on the subscriber. Transactional
replication uses pure sql statements to create the metadata tables and
populate them with the required data.
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...[vbcol=seagreen]
> Could I get an explination for what the snapshots are for?
> I believe their only for initialization correct? Not the actual
> replication
> merging, that happens on the 1433 port connection?
> If so..
> Can I, when setting up the database, export all objects to the server.
> Then
> create the subscription, and I've tried to take the initialization step
> off
> in the wizard, saying "subscriber already has schema" etc.
> Then when I try and start the pull subscription I get this history in the
> job history:
> Bulk copying data into table 'sysmergesubsetfilters'
> Message: The process could not deliver the snapshot to the Subscriber.
> Message: The process could not bulk copy into table
> '"dbo"."MSmerge_contents"'.
> Message: Unable to open BCP host data-file
> The process could not deliver the snapshot to the Subscriber. The step
> failed.
> Is my idea possible? Not use any snapshots if the server can already be
> populated with the data via export?
> Thanks for all the help!
> "Hilary Cotter" wrote:
|||Hey Hilary,
Thanks a ton for the reply. So again I hit another bind.
Let me explain my situation. My publisher, when setting up the replication,
will have no data. The subscriber, a pull merge subscription, has all the
data/schema.
Reason for this is, subscribers can't open listening ports on firewalls, so
I'm making them the "puller".
This is all working now with your guidance, the only problem is, when the
subscriber applies snapshots in a manual location just to get the merge
going, because you said its required, it wipes out all the data on the
subscriber applying the snapshots.
What I really want is for the subscriber to push all the data to the
publisher, and from then on they merge continiously.
Is this possible? Or do I have to export all data to the publisher, make
snapshots, and then the subscriber basically because of the snapshots wipes
out its own data, and re-applies it through the snapshot?
Thanks for all the help.
"Hilary Cotter" wrote:

> the snapshots are to create the schema on the subscriber and push the data
> there. It also contains replication metadata which is normally used for
> tracking. So, yes, it is for initialization. All replication activity takes
> place over port 1433 except for the application of the snapshot which is
> pulled or pushed through a unc or ftp port.
> Unfortunately with merge replication even a no-sync snapshot requires a mini
> snapshot to be deployed and applied on the subscriber. Transactional
> replication uses pure sql statements to create the metadata tables and
> populate them with the required data.
> "Synced" <Synced@.discussions.microsoft.com> wrote in message
> news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...
>
>

replication monitor icon become red color

Hi guys,
I just re-create replication and everything seems like ok. I can replicate
data into subscriber. Only on the replication monitor icon, it has red cross
icon. under it, in the treeview, each item has red color with cross icon. I
check the history, no error, and data has been transfer successfully. Can
some one tell me how to get rid of it? Thanks.
the replication is working fine, only replication monitor become red. The
agent is ok, no error in the history. I don't know why it is red cross on it.
"Iter" wrote:

> Hi guys,
> I just re-create replication and everything seems like ok. I can replicate
> data into subscriber. Only on the replication monitor icon, it has red cross
> icon. under it, in the treeview, each item has red color with cross icon. I
> check the history, no error, and data has been transfer successfully. Can
> some one tell me how to get rid of it? Thanks.
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication Monitor could not connect to Distributor

Hello Guys,

I have the following scenario:

PC1 : located in Dubai representing my laptop

PC2: located in Denver ,

Both pcs are connected to each other via the internet as i am using Aliases ,they can both access and see each other using SQL Auth.

I want to implement Merge Replication to Synchronize TestDB ,

I have NO DOMAIN connnecting them.

I Created a new publication ,under security i asked the agent to use the Sa account to connect

When launching the rep monitor i get the following error:

"Replication Monitor could not connect to Distributor 'laptop'."

"Login failed for user ''. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)"

Both Publisher and Distributer are on my Laptop ,why cant they connect to each other?

Another Question : could i implement Traditional Merge replication to synchronize my DBs or do i need to configure Web Synch in Merge rep to enable connecting via the internet?!

Thanks Guys!

Moodi

First off while you are free to use web synchronization where appropriate you probably can use traditional merge replication.

It is probably that you have configured replication monitor to connect to your distributor with an account which does not have the necessary rights to monitor SQL server. Can you ensure that SQL Server is able to authenticate using mixed authentication (right click on it, select properties, select security and ensure that SQL Server and windows authentication is selected. Add an account in the sysadmin role or verify you know the sa password. Then right click on your distributor in Replication Monitor and select remove. Then right click and add it again, this time use the sa account and its password to add it.|||

Thanks Hillary,

i was able to fix this by configuring the property of the connection used to connect to distributer ,it was windows auth. so i changed it to run under SQL auth. now replication monitor can run and show all subscribers but i have another problem.

I get the following error when i try to initialize the subscription located on my USA server :

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.1.1]
2007-07-05 11:55:11.00 Logon Error: 18456, Severity: 14, State: 11.

Please note that i am using merge replication via the internet with out web synch. ,when i run the SQL Agent under the local system account i get the above mentioned error although i configured Sa account with the same pwd to be used on both machines.

I created a windows account RepUser on both machines as part of Admin group ,then i configured the agent on both machines to run under this account ,i got the following error:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.1]
2007-07-05 11:55:06.15 Logon Error: 17806, Severity: 20, State: 2.

Looks like the SQL agent process account is the one thats being authenticated and NOT the Sa account ,why is this happening?

|||

Hi Ali

I am a little lost here, but it sounds like you need to configure your publication to use a ftp snapshot and then configure ftp for anonymous authentication.

HTH

|||

Hello Hilary ,

Are u saying that if i have two mahcines replicating via the internet then i should have the snapshot folder accessible via FTP?

Ali

Replication Maintanance Issues!

Hi Guys/Gals,

Need some comments/ideas on the following scenario:

Current Situation:
-I have a PROD DB and a REPORTING DB( Separte Physical Servers).
-PROD DB replicates (Transactional) to REPORTING DB. ( Using only Replication Wizard to set up, quite easy)
-DB is for running shopfloor(manufacturing) applications, thus needs to be minimum downtime.
-Retention period for PROD DB data is 3 months ,Reporting DB is 3 years.

Future Problem that I will encounter:
-Purging of data in PROD DB will be replicated into Reporting.
-Schema change in PROD DB need to delete existing Publication before able to change schema.
-After schema change, need to recreate publication and rerun snapshot.

Questions:
-How to prevent the purging to be replicated to Reporting?
Switching off Replication when Purging takes place will not help. The only solution
I am thinking of is to alter the store procedure for replication during purging.
Is there any other "CLEANER" hassle free way?

- How to cater shema changes better?
Currently the data is not alot, but down the road, it might go to Terabytes, by then running a
snapshot will cost us alot. Is there any way not to redo a snapshot for this scenario?

Please give your comments/ideas/ .

Thanks.--> another thing I miss out, doing a snapshot again would clear any data
Reporting DB has stored. e.g. Prod DB( 3 months data) , Reporting DB(1 year data), do snapshot....all gone.|||Anyone...any suggestions?|||Patrick, have you thought about replication without snapshot? That will minimize the impact of snapshoting on publications.

Changing replication stored procs sounds the best way to avoid data changes on the subscribers. But it could go wild if you have a lot of sprocs to modify.|||replication without snapshot..hmmm...I'll check it out...
no idea as of how not to have a snapshot for now....can u give some highlights...

Anybody else? I'm sure other ppl would have the same senario as me....|||there was a thread a few days back that was talking about transactional replication without snapshot. check it out:
http://www.dbforums.com/showthread.php?p=3665793#post3665793

SQL Magazine also has an article on this.

I have used this technique for years. The key is to ensure publisher and subscriber are in sync without snapshoting. make sure no one can do transactions in the servers. break replication, dropping subscriber, articles etc, while the publisher and subscriber dbs are not modified. At the end when you reset up replication, click on the option that says "subscriber already has the data".

Replication Issue With Cisco VPN Client

Morning Guys,
I really need you help, I am just about to seriously pull my hair out. Here
is the situation.
I have 1 server running SQL Server 2000 sp3a with merge replication setup,
and 150 users on laptops running MSDE 2000 with the latest service pack and
dialling up to the server via a 56k modem and using a Cisco VPN Client to
gain access.
If I communicate on the network, all the replication works fine, if I
communicate on a ADSL Line all the communicates is fine, if I dialup,
communication will just fail, it will process the articles and then fail. I
really need some help here. I have adjusted the MTU rate on the server,
Cisco router and the client. but I just can't get this to work correctly and
I have this working in many other companies.
The dial up users are getting a good connection with 50k
If you need anymore information give me a shout.
Thanks Tim.
Tim,
have you tried following the recommendations in this article:
http://support.microsoft.com/default...49&Product=sql
It refers to a transactional setup but if you substitute 'merge agent' for
'distribution agent' it should apply eqally.
HTH,
Paul Ibison

replication is pending mode

hi guys,
Publication is in pending status. I don't know what is the problem.. I am using DOMAIN account(administrator). Publisher and subscriber are on same machine.
Error I got in my event viewer.
SQL Server Scheduled Job 'Replication agents checkup' (0xF2F0DE84FCFB1B46A009419F676E83A9) - Status: Failed - Invoked on: 2005-09-20 10:40:01 - Message: The job failed. Unable to determine if the owner (CORP\user) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'CORP\user'. [SQLSTATE 42000] (Error 8198)).
Kevin,
try changing the job owner to sa and then run the job.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||but it's under pending mode.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:e2KvrjfvFHA.3500@.TK2MSFTNGP09.phx.gbl...
Kevin,
try changing the job owner to sa and then run the job.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kevin - I'm not too sure what's happening here - can't you change the job owner in SQL Server Agent, Jobs and then run it manually?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
|||now it's active.
Could you tell me what is difference between reinitialize subscription and synchronize? is that almost the same thing?
I'm assuming reinitialize subscription will refresh all the data,
and synchronize is just download the latest data.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:eXJhGhgvFHA.2076@.TK2MSFTNGP14.phx.gbl...
Kevin - I'm not too sure what's happening here - can't you change the job owner in SQL Server Agent, Jobs and then run it manually?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
|||Kevin,
reinitialize will require that a new snapshot be generated and sent down to the subscriber as a set of odbcBCP files. On the other hand, synchronize will run the distribution/merge agent and send down the latest changes. For merge the flow of data is in both directions and is just pub -> sub for normal transactional replication. In the case of snapshot replication, synchronization and reinitialization are quite similar, but there's a big difference for the other methods.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Since it's in active mode, and I'm using transaction replication,
I did a test. I change column defintion for one of the column on publisher, and when I go to subscriber, how come I don't see the change? I also wait about 12 hours, it is still not there. Is it because it's depending on schedule? how do I force the synchronization?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uMDS2PovFHA.2008@.TK2MSFTNGP10.phx.gbl...
Kevin,
reinitialize will require that a new snapshot be generated and sent down to the subscriber as a set of odbcBCP files. On the other hand, synchronize will run the distribution/merge agent and send down the latest changes. For merge the flow of data is in both directions and is just pub -> sub for normal transactional replication. In the case of snapshot replication, synchronization and reinitialization are quite similar, but there's a big difference for the other methods.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kevin wrote on Wed, 21 Sep 2005 10:26:34 -0400:

> Since it's in active mode, and I'm using transaction replication,
> I did a test. I change column defintion for one of the column on
> publisher, and when I go to subscriber, how come I don't see the change?
> I also wait about 12 hours, it is still not there. Is it because it's
> depending on schedule? how do I force the synchronization?
Did you make the change using ALTER TABLE, or via the the
sp_repladdcolumn/sp_repldropcolumn procs or replication properties dialogs
in EM?. Check BOL for "Schema Changes on Publication Databases", according
to that ALTER TABLE changes to the schema are not replicated (I think I read
that SQL Server 2005 will be able to do this though).
Dan
|||YES. I changed it using alter table using following script. but I drop the article and then alter table, AND THEN I re-add article back in publication. SO even that method won't work?
altering the subscriptions
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
-- Change column definition
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message news:uYj2fPsvFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Kevin wrote on Wed, 21 Sep 2005 10:26:34 -0400:
>
> Did you make the change using ALTER TABLE, or via the the
> sp_repladdcolumn/sp_repldropcolumn procs or replication properties dialogs
> in EM?. Check BOL for "Schema Changes on Publication Databases", according
> to that ALTER TABLE changes to the schema are not replicated (I think I read
> that SQL Server 2005 will be able to do this though).
> Dan
>
|||This is OK - if you're using transactional replication, you'll also need to run the snapshot then the distribution agents for it to go over. Also, Daniel's quite right that this type of tinkering is supported directly using the 'Replicate Schema Changes' option on the subscription options pane of SQL Server 2005 (by default is enabled).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication is in pending mode

hi guys,
I don't understand something, when I use domain account to do replication,
it's in pending mode for snapshot agent, but once I use sa account to do
replication, then it's in active mode. why can't I use domain account to
create replication?
The replication subsystem can't figure out if the domain account has rights
to start the service. A patch, not sure which one, broke this sometime ago.
I logon and register my servers in EM using the sa account and everything is
peachy.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23S%23iz20yFHA.596@.TK2MSFTNGP12.phx.gbl...
> hi guys,
> I don't understand something, when I use domain account to do
> replication, it's in pending mode for snapshot agent, but once I use sa
> account to do replication, then it's in active mode. why can't I use
> domain account to create replication?
>