How to change the Credential for the replication

Dear Friends
we are having mearge replication between two domains. Now our policy for the
SA is changed and we do not use the SA account anymore for the replication.
As we have changed the password for the SA now the replication is failed
with the follwoing error.
The process could not connect to Subscriber 'SERVER'.
(Source: SERVER (Agent); Error number: 20084)
------
Login failed for user 'sa'.
(Source: SERVER (Data source); Error number: 18456)
------
Please suggest how i can change the credential so that the new username will
be used to connect the other server for the replication.
YOur earlier reply would be a great help to me.
Best regards
Sharad
It would be nice to know if this is from SQL 2000 or SQL 2005. It would
also be good to know if you are running the merge agent via a SQL Agent job,
an API call, or via the command line. Most likely you are using a SQL 2000
SQL Agent job to sync a pull merge subscriber. In this case, you need to
examine the properties of the SQL Agent job that is failing to execute the
merge agent. In the step called "Run Agent", check out the "command", this
contains the parameters used to run the merge agent in the job. You should
be able to find -SubscriberSecurityMode 0 -SubscriberLogin
sa -SubscriberPassword XXXXXX. This is where you can change the subscriber
credential information.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sharad" <Sharad@.discussions.microsoft.com> wrote in message
news:01EA29E2-D2DA-4748-BE15-218EDEF5625D@.microsoft.com...
> Dear Friends
> we are having mearge replication between two domains. Now our policy for
> the
> SA is changed and we do not use the SA account anymore for the
> replication.
> As we have changed the password for the SA now the replication is failed
> with the follwoing error.
> The process could not connect to Subscriber 'SERVER'.
> (Source: SERVER (Agent); Error number: 20084)
> ------
> Login failed for user 'sa'.
> (Source: SERVER (Data source); Error number: 18456)
> ------
> Please suggest how i can change the credential so that the new username
> will
> be used to connect the other server for the replication.
> YOur earlier reply would be a great help to me.
> Best regards
> Sharad

How to change replication agent's default profile properties....

Hi,
my problem is: we have poor line between servers. To avoid this issue i
created new profile for distribution agent. This profile has been
created with bigger QieryTimeout and LogonTimeout values. After that i
assigned this profile to distribution agent and SQL Server agent was
restarted after this. According to new settings replication works fine.
But: i've found, that may be my boss, may be somebody else rolled back
all my changes and restored "system default" profile for distribution
agent. After this replicaion fault every time. So every morning i
assigned my profile, every night "default" profile has been restored.
My question is: is it possible to change "default" profile settings? I
saw MSdistribution_agent table, found job description, where profile_id
= 4. Where i should looking for details of profile settings?
Thanks a lot in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
We do not recommend updating the default profile, I'm not quite sure if i
understand what you mean by "rolled back" changes.
What you did is the recommended way of changing the profile values.
thanks
gopal
|||Many thanks for all.
"roll back" means, that i assigned my profile to distribution agent, but
next morning i saw "default" profile assigned to the same agent.
This issue now sorted out because i found MSagent_profiles,
MSagent_properties tables in msdb.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

How to Change Publication on Local Network to Publish on Internet

Please Help me with this problem , I am working with merge replication , i
have got publication for local network and i want to chage it to publish the
articles on internet without creating new publication.
Please Reply
Thanks
Abulla Fatiya
drop all subscriptions
run the below script - change the name of pubs5 to your publication name
sp_changemergepublication 'pubs5','allow_pull','true'
go
sp_changemergepublication 'pubs5','allow_anonymous','true'
go
sp_changemergepublication 'pubs5','alt_snapshot_folder','c:\inetpub\ftproot'
go
sp_changemergepublication 'pubs5','compress_snapshot','true'
go
declare @.str varchar(200)
set @.str='sp_changemergepublication
''pubs5'',''ftp_address'',''ftp://'+@.@.ServerName+''''
print @.str
exec (@.str)
go
sp_changemergepublication 'pubs5','ftp_port','21'
go
sp_changemergepublication 'pubs5','ftp_subdirectory','ftp'
go
sp_changemergepublication 'pubs5','ftp_login','anonymous'
go
sp_changemergepublication 'pubs5','ftp_password','password'
go
sp_changemergepublication 'pubs5','enabled_for_internet','true'
go
sp_changemergepublication 'pubs5','snapshot_ready','true'
go
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
"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
news:7464DC4B-3760-4DD2-95E3-280ACB8E898C@.microsoft.com...
> Please Help me with this problem , I am working with merge replication , i
> have got publication for local network and i want to chage it to publish
the
> articles on internet without creating new publication.
> Please Reply
> Thanks
> Abulla Fatiya
>
>
|||Fatiya,
my comments re VPN are OK, but Hilary's change script is
extremely neat and avoids the need for reinitialization,
so please disregard my later comments.
Rgds,
Paul Ibison

>--Original Message--
>drop all subscriptions
>run the below script - change the name of pubs5 to your
publication name
>sp_changemergepublication 'pubs5','allow_pull','true'
>go
>sp_changemergepublication 'pubs5','allow_anonymous','true
'
>go
>sp_changemergepublication 'pubs5','alt_snapshot_folder','
c:\inetpub\ftproot'
>go
>sp_changemergepublication 'pubs5','compress_snapshot','tr
ue'
>go
>declare @.str varchar(200)
>set @.str='sp_changemergepublication
>''pubs5'',''ftp_address'',''ftp://'+@.@.ServerName+''''
>print @.str
>exec (@.str)
>go
>sp_changemergepublication 'pubs5','ftp_port','21'
>go
>sp_changemergepublication 'pubs5','ftp_subdirectory','ftp
'
>go
>sp_changemergepublication 'pubs5','ftp_login','anonymous'
>go
>sp_changemergepublication 'pubs5','ftp_password','passwor
d'
>go
>sp_changemergepublication 'pubs5','enabled_for_internet',
'true'
>go
>sp_changemergepublication 'pubs5','snapshot_ready','true'
>go
>--
>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
>"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in
message
>news:7464DC4B-3760-4DD2-95E3-
280ACB8E898C@.microsoft.com...[vbcol=seagreen]
merge replication , i[vbcol=seagreen]
chage it to publish
>the
>
>.
>

How to change IDENTITY to NOT FOR REPLICATION?

I need to change many IDENTITY columns in many tables so that they show as
NOT FOR REPLICATION. This way when I replicate, it doesn't trash my identity
values. I can do it with Enterprise Manager without any problems but I need
to code it (using t-sql) so I can repeat the process and undo it later.
I have not been able to come up with a suitable syntax.
How can I use ALTER TABLE/ALTER COLUMN to change a col from IDENTITY to
IDENTITY NOT FOR REPLICATION? I don't want to drop the column and recreate
it!
Any help appreciated.
Thanks in regards,
JessYou will have to drop and recreate the table if you want the same column in
the same ordinal position.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"languy" <jn@.online.stofanet.dk> wrote in message
news:OddrwY2DFHA.624@.TK2MSFTNGP09.phx.gbl...
I need to change many IDENTITY columns in many tables so that they show as
NOT FOR REPLICATION. This way when I replicate, it doesn't trash my identity
values. I can do it with Enterprise Manager without any problems but I need
to code it (using t-sql) so I can repeat the process and undo it later.
I have not been able to come up with a suitable syntax.
How can I use ALTER TABLE/ALTER COLUMN to change a col from IDENTITY to
IDENTITY NOT FOR REPLICATION? I don't want to drop the column and recreate
it!
Any help appreciated.
Thanks in regards,
Jess

How to change data type of column from int to bigint in replication

Hi,
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
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
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql

How to change column length on published DB - Merge Replication

Guys,
I have Merge Replication on SQL SERVER 2000.
Nilay,
Directly this can't be done. Indirectly it can, but not nicely! 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 co
lumn having the same original name. Hopefully this functionality will be directly available in SQL Server 2005.
HTH,
Paul Ibison
|||The sp_repladdcolumn proc has a @.schema_change_script parameter where you can package a update statement to simplifiy the operation of this.
ie
update table TableYouAreModifiying set newColumn=oldColumn
Here is a code sample of how to do this using SQL-DMO
Option explicit
dim objServer, objReplication, objReplicationDatabases,_ objReplicationDatabase, objReplicationTables,_ objReplicationTable
set objServer=CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp" , "sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("pu bs")
set objReplicationTables=objReplicationDatabase._
ReplicationTables
for each objReplicationTable in objReplicationTables
if objReplicationTable.Name ="authors" then
objReplicationTable.ReplicationAddColumn _ "temp","char(100)","pubs",_
"c:\temp\updatetemp.sql"
objReplicationTable.ReplicationDropColumn _
"au_lname"
objReplicationTable.ReplicationAddColumn _
"au_lname","char(100)","pubs",_
"c:\temp\updatetemp2.sql"
objReplicationTable.ReplicationDropColumn "temp"
end if
next
set objReplicationTable=Nothing
set objReplicationTables=Nothing
set objReplicationDatabase=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Here are my update scripts
updatetetmp.sql
update authors set temp=au_lname
updatetemp2.sql
update authors set au_lname=temp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Guys,
This helps.