replication failed after restart

Hi..

I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.

At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.

I hope some body out there can explain something to me and how to overcome the problem...

If the PC restarts during the initialization then it will fail and you will have to start initialization again.

It would be helpful to know the error messages.

Martin

|||

the error message is

the process could not connect to subscriber

|||

Can you confirm that the subscribers are up and running? After a reboot are the SQL Services all started and the databases available?

|||yes...|||

Instead of restarting the pc, did you try connecting to the subscriber via isqlw, osql.exe or some other client tool with the same credentials that the merge agent uses? If that also fails then it's not replication.

replication failed after restart

Hi..

I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.

At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.

I hope some body out there can explain something to me and how to overcome the problem...

If the PC restarts during the initialization then it will fail and you will have to start initialization again.

It would be helpful to know the error messages.

Martin

|||

the error message is

the process could not connect to subscriber

|||

Can you confirm that the subscribers are up and running? After a reboot are the SQL Services all started and the databases available?

|||yes...|||

Instead of restarting the pc, did you try connecting to the subscriber via isqlw, osql.exe or some other client tool with the same credentials that the merge agent uses? If that also fails then it's not replication.

sql

replication failed after restart

Hi..

I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.

At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.

I hope some body out there can explain something to me and how to overcome the problem...

If the PC restarts during the initialization then it will fail and you will have to start initialization again.

It would be helpful to know the error messages.

Martin

|||

the error message is

the process could not connect to subscriber

|||

Can you confirm that the subscribers are up and running? After a reboot are the SQL Services all started and the databases available?

|||yes...|||

Instead of restarting the pc, did you try connecting to the subscriber via isqlw, osql.exe or some other client tool with the same credentials that the merge agent uses? If that also fails then it's not replication.

replication failed after restart

Hi..

I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.

At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.

I hope some body out there can explain something to me and how to overcome the problem...

If the PC restarts during the initialization then it will fail and you will have to start initialization again.

It would be helpful to know the error messages.

Martin

|||

the error message is

the process could not connect to subscriber

|||

Can you confirm that the subscribers are up and running? After a reboot are the SQL Services all started and the databases available?

|||

yes...|||

Instead of restarting the pc, did you try connecting to the subscriber via isqlw, osql.exe or some other client tool with the same credentials that the merge agent uses? If that also fails then it's not replication.

Replication Error Message

I get the following error message when I run replication that I have set up
on one of our servers. I have no ideal why, any help would be appreciated.
This error is from the application log on the replication server to our web
server. I think it is because it is trying to login as user "null" but i
have no ideal where to set this.
Thank you.
Greg
Event Type: Information
Event Source: SQLAgent$INST2DB
Event Category: Job Engine
Event ID: 203
Date: 11/15/2005
Time: 5:03:45 PM
User: N/A
Computer: VS2DB
Description:
SubSystem Message - Job 'VS2DB\INST2DB-Charity-WEBSERVER-13'
(0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection. The
process could not connect to Subscriber 'WEBSERVER'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
It looks like you are trying to logon to a SQL Server using SQL
authentication and this server is not configured for mixed mode
authentication.
"greg" <anomious@.microsoft.com> wrote in message
news:O9UGJqj6FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I get the following error message when I run replication that I have set up
>on one of our servers. I have no ideal why, any help would be appreciated.
>This error is from the application log on the replication server to our web
>server. I think it is because it is trying to login as user "null" but i
>have no ideal where to set this.
> Thank you.
> Greg
> Event Type: Information
> Event Source: SQLAgent$INST2DB
> Event Category: Job Engine
> Event ID: 203
> Date: 11/15/2005
> Time: 5:03:45 PM
> User: N/A
> Computer: VS2DB
> Description:
> SubSystem Message - Job 'VS2DB\INST2DB-Charity-WEBSERVER-13'
> (0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection. The
> process could not connect to Subscriber 'WEBSERVER'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
|||Hillary, thanks for the response, I check both servers and they are set up
to user both SQL and Windows authentication. Do you have any other ideas?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%237M1YNk6FHA.1140@.tk2msftngp13.phx.gbl...
> It looks like you are trying to logon to a SQL Server using SQL
> authentication and this server is not configured for mixed mode
> authentication.
> "greg" <anomious@.microsoft.com> wrote in message
> news:O9UGJqj6FHA.2040@.TK2MSFTNGP14.phx.gbl...
>

Replication Distribution Agent Does Not Start

Hi,
For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.
My configuration is: Transactional Replication with a
‘Pull’ Subscription. The Subscriber is Updateable with
Immediate Updates Only. All created via the SQL Server Enterprise
Manager. Both the Publisher and Subscriber are on the same server.
I’m running SQL Server 2000 (8.00.760 SP3), on Windows 2000
Server (5.0 (2195)).
I manually run the snapshop agent. It runs fine with the status
‘Succeeded’ and the Last Action is ‘A Snapshot of X
article(s) was generated’.
When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at ‘Never Started’. All other columns are
either blank or 0. When I right click over the subscription, both
Start Synchronising and Stop Synchronising are NOT greyed out. If I
click on either Start Synchronising or Stop Synchronising (absolutely)
nothing happens (except the mouse turns ‘busy’ for a
fraction of a second). There are no error messages.
I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.
It worked fine up to a couple of days ago. I’m not sure exactly
what caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I’ve tried to go back to the set up before I made
the changes, even disabling replication and starting again from
scratch. Still the problem persists.
The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under ‘Publisher and
Distributor Properties’ I’ve set the ‘Replication
Agent Connection to Publisher’ and ‘Agent Connection to
the Subscriber’ both to Use SQL Server Authentication, both with
the same account that has dbo permissions in both publishing and
subscribing database.
Has anybody come across anything like this before? Can anybody make
any suggestions? Thanks in advance for your help.
Regards
Damian Helme
IS Manager
INTEC Engineering (UK) Ltd.
are you using an ActiveX control or Windows Synchronization Manager to
manage this subscription? If so you will not be able to manage your
distribution agent in EM.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407220153.32af854e@.posting.google.c om...
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
> My configuration is: Transactional Replication with a
> ‘Pull’ Subscription. The Subscriber is Updateable with
> Immediate Updates Only. All created via the SQL Server Enterprise
> Manager. Both the Publisher and Subscriber are on the same server.
> I’m running SQL Server 2000 (8.00.760 SP3), on Windows 2000
> Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> ‘Succeeded’ and the Last Action is ‘A Snapshot of X
> article(s) was generated’.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at ‘Never Started’. All other columns are
> either blank or 0. When I right click over the subscription, both
> Start Synchronising and Stop Synchronising are NOT greyed out. If I
> click on either Start Synchronising or Stop Synchronising (absolutely)
> nothing happens (except the mouse turns ‘busy’ for a
> fraction of a second). There are no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I’m not sure exactly
> what caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I’ve tried to go back to the set up before I made
> the changes, even disabling replication and starting again from
> scratch. Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under ‘Publisher and
> Distributor Properties’ I’ve set the ‘Replication
> Agent Connection to Publisher’ and ‘Agent Connection to
> the Subscriber’ both to Use SQL Server Authentication, both with
> the same account that has dbo permissions in both publishing and
> subscribing database.
> Has anybody come across anything like this before? Can anybody make
> any suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.
|||Hillary
Thanks for your reply. No I am not using an ActiveX control or Windows
Synchronization Manager. It is all done through EM.
Here's the post again, but this time free of the garbled control
characters:
================================================== =============================
Hi,
For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.
My configuration is: Transactional Replication with a 'Pull'
Subscription. The Subscriber is Updateable with Immediate Updates
Only. All created via the SQL Server Enterprise Manager. Both the
Publisher and Subscriber are on the same server. I'm running SQL
Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
I manually run the snapshop agent. It runs fine with the status
'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
generated'.
When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at 'Never Started'. All other columns are either blank or
0. When I right click over the subscription, both Start Synchronising
and Stop Synchronising are NOT greyed out. If I click on either Start
Synchronising or Stop Synchronising (absolutely) nothing happens
(except the mouse turns 'busy' for a fraction of a second). There are
no error messages.
I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.
It worked fine up to a couple of days ago. I'm not sure exactly what
caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I've tried to go back to the set up before I made the
changes, even disabling replication and starting again from scratch.
Still the problem persists.
The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under 'Publisher and Distributor
Properties' I've set the 'Replication Agent Connection to Publisher'
and 'Agent Connection to the Subscriber' both to Use SQL Server
Authentication, both with the same account that has dbo permissions in
both publishing and subscribing database.
Has anybody come across this problem before? Can anybody make any
suggestions? Thanks in advance for your help.
Regards
Damian Helme
IS Manager
INTEC Engineering (UK) Ltd.
|||I've encountered this error before and its driving me crazy trying to recall
exactly what is causing it. IIRC its something pretty simple.
Can you right click on your distribution agent, select agent properties
select steps, click on Run Agent, and click edit.
Then copy everything in the commands text box.
Open a command window and go to c:\program files\microsoft sql server\80\com
and then type
distrib and paste your commands here. To do this, go to the c:\ thingy in
the upper left hand corner of your command window and select edit - paste.
Make sure there is a space between distrib and the commands.
Then hit enter. You should get an error message this time.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407222334.bc69d37@.posting.google.co m...
> Hillary
> Thanks for your reply. No I am not using an ActiveX control or Windows
> Synchronization Manager. It is all done through EM.
> Here's the post again, but this time free of the garbled control
> characters:
>
>
================================================== ==========================
===
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
>
> My configuration is: Transactional Replication with a 'Pull'
> Subscription. The Subscriber is Updateable with Immediate Updates
> Only. All created via the SQL Server Enterprise Manager. Both the
> Publisher and Subscriber are on the same server. I'm running SQL
> Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> 'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
> generated'.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at 'Never Started'. All other columns are either blank or
> 0. When I right click over the subscription, both Start Synchronising
> and Stop Synchronising are NOT greyed out. If I click on either Start
> Synchronising or Stop Synchronising (absolutely) nothing happens
> (except the mouse turns 'busy' for a fraction of a second). There are
> no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I'm not sure exactly what
> caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I've tried to go back to the set up before I made the
> changes, even disabling replication and starting again from scratch.
> Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under 'Publisher and Distributor
> Properties' I've set the 'Replication Agent Connection to Publisher'
> and 'Agent Connection to the Subscriber' both to Use SQL Server
> Authentication, both with the same account that has dbo permissions in
> both publishing and subscribing database.
> Has anybody come across this problem before? Can anybody make any
> suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.
|||Thanks for the suggestion, I didn't get chance to try your suggestion
as I had already reinstalled SQL Server, which solved the problem.
I'll keep a note of your suggestion for future use.
Thanks again.
Damian.

Replication Distribution Agent Does Not Start

Hi,

For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.

My configuration is: Transactional Replication with a 'Pull'
Subscription. The Subscriber is Updateable with Immediate Updates
Only. All created via the SQL Server Enterprise Manager. Both the
Publisher and Subscriber are on the same server. I'm running SQL
Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).

I manually run the snapshop agent. It runs fine with the status
'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
generated'.

When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at 'Never Started'. All other columns are either blank or
0. When I right click over the subscription, both Start Synchronising
and Stop Synchronising are NOT greyed out. If I click on either Start
Synchronising or Stop Synchronising (absolutely) nothing happens
(except the mouse turns 'busy' for a fraction of a second). There are
no error messages.

I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.

It worked fine up to a couple of days ago. I'm not sure exactly what
caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I've tried to go back to the set up before I made the
changes, even disabling replication and starting again from scratch.
Still the problem persists.

The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under 'Publisher and Distributor
Properties' I've set the 'Replication Agent Connection to Publisher'
and 'Agent Connection to the Subscriber' both to Use SQL Server
Authentication, both with the same account that has dbo permissions in
both publishing and subscribing database.

Has anybody come across this problem before? Can anybody make any
suggestions? Thanks in advance for your help.

Regards

Damian Helme
IS Manager
INTEC Engineering (UK) Ltd."Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407222337.542a5fd1@.posting.google.c om...
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
>
> My configuration is: Transactional Replication with a 'Pull'
> Subscription. The Subscriber is Updateable with Immediate Updates
> Only. All created via the SQL Server Enterprise Manager. Both the
> Publisher and Subscriber are on the same server. I'm running SQL
> Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> 'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
> generated'.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at 'Never Started'. All other columns are either blank or
> 0. When I right click over the subscription, both Start Synchronising
> and Stop Synchronising are NOT greyed out. If I click on either Start
> Synchronising or Stop Synchronising (absolutely) nothing happens
> (except the mouse turns 'busy' for a fraction of a second). There are
> no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I'm not sure exactly what
> caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I've tried to go back to the set up before I made the
> changes, even disabling replication and starting again from scratch.
> Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under 'Publisher and Distributor
> Properties' I've set the 'Replication Agent Connection to Publisher'
> and 'Agent Connection to the Subscriber' both to Use SQL Server
> Authentication, both with the same account that has dbo permissions in
> both publishing and subscribing database.
> Has anybody come across this problem before? Can anybody make any
> suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.

You might want to post this in microsoft.public.sqlserver.replication, as
you may get a better answer over there.

Simon

Replication complete event on publisher

I would like to setup transactional replication so that when the publisher
finishes sending data out, a .NET 2.0 app is run. Is there an event I can
grab onto, or would I have to set a trigger up on a system table?
Add it to the final job step of your merge or distribution agent.
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
"Rono" <Rono@.discussions.microsoft.com> wrote in message
news:E31B4A0E-4F1A-420C-AADC-AC39EB76C738@.microsoft.com...
>I would like to setup transactional replication so that when the publisher
> finishes sending data out, a .NET 2.0 app is run. Is there an event I can
> grab onto, or would I have to set a trigger up on a system table?
|||In your distribution agent job there are 3 steps. The second one - "Run
agent" does the real work. In the Advanced tab there is workflow defined so
that when this step completes, the job quits, while if it fails, the job
goes to the next step. So, you'll need another step to be added to call your
app, and ensure that the workflow is such that the step 4 is arrived at on
success of step 2.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication between 2 publisher with same database

Hi,

Before this, I've 2 difference site that run SQL server 2000 as database server. One at north and the other at west.

At north, I've a group of terminal that run SQL server 2000. I've 1 main server that run as publisher and distributor to all the terminal. And also the main database is at the main server. The terminal will replicate together using merge replication. So that the data would be same at all terminal. At west it also have the same scenario.

Last week my boss ask me to replicate both main server ( north and west ). So that he want the data would be same at all place.

So, can anybody help me.....

hi

at first: a subscriber can also be a publisher!

but you have a problem: you have to reinitalize the replication from one point

for example: you publish the db from the west to the north, and there you have create the new subscription to the west-terminals

it is not possible to publish the db from one publisher to another publisher

summary

step 0: you have one publication with serveal subscriper (west)

step 1: create subscription to a server (north)

step 2: create there are publication

step 3: create subscription to other server

bye

thorsten ueberschaer

|||

Huh? Leave your replication implementation as it currently exists alone. Don't touch it, it's working just fine.

On your main server, create a new database which will combine everything together. Once created, push a subscription from North to this database. After that is done, push a second subscription from West to this database. (You have to configure the snapshot to leave the existing table intact otherwise it will error out when it tries to run the default option which is drop/create.

Before you combine these together, you have to make sure that you can actually combine the data sets in the same place. If you have the same primary key values on both the North and West servers, then you can't combine the data together. In this case, you can still bring it all into the same database, just not into the same set of tables.

BUT, there is no need to blow up your existing implementation just to meet this new requirement.

Replication as a job

For a pull snapshot replication: I want to run it as a job ie fully automated. Rite now I have start the 'Snapshot' under the publisher->Replication Monitor>Publishers>ServerName>Name.. and then manually start the Pull replication agent.

Is there a way to do this automatically ie in a job?You should be able to simply go to Agent Properties and set up a schedule for it.

Replication and SQL Server Authentication

I'm trying to use replciation between two computers that run SQL Server that
has been installed using mixed mode authentication, but the replciation
fails. Here are the details:
The Publisher/Distributer - I have installed SQL Server using mixed mode
authentication and used a blank password for user 'sa'. The Publisher has
registered the Subscriber succesfully using SQL Server authentication.
The Subscriber - The same as the Publisher.
I prepared a database for a merge publication and the snapshot agent
completed its job successfully.
When I push a new subscription to the publication, the merge agent fails
declaring that he couldn't connect to the subscriber as login for us 'sa'
fails because it is not associated with a trusted connection.
I knew form Microsoft SQL Server administration book that a trusted
connection is used for windows authentication.
I have reinstalled SQL Server on the Subscriber using Windows
authentication, the publisher remains the same, but te problem remains the
same.
Can a merge publication be used when both the publisher and the subscriber
use SQL Server authentication?!
Thanks in advance,
Shehab.
Go to Tools, Replication, Configure Publishers, Distributors and
Subscribers, and click on the Subscribers tab. Click the browse button to
the right of your subscriber, make sure you are using SQL Authentication
here and make sure the password is blank.
Actually you should not be using a blank password at all. Its a secuirty
risk.
Hilary
"Shehab Kamal" <ShehabKamal@.discussions.microsoft.com> wrote in message
news:527D5795-FA53-44D6-9C73-FD604698A5AA@.microsoft.com...
> I'm trying to use replciation between two computers that run SQL Server
> that
> has been installed using mixed mode authentication, but the replciation
> fails. Here are the details:
> The Publisher/Distributer - I have installed SQL Server using mixed mode
> authentication and used a blank password for user 'sa'. The Publisher has
> registered the Subscriber succesfully using SQL Server authentication.
> The Subscriber - The same as the Publisher.
> I prepared a database for a merge publication and the snapshot agent
> completed its job successfully.
> When I push a new subscription to the publication, the merge agent fails
> declaring that he couldn't connect to the subscriber as login for us 'sa'
> fails because it is not associated with a trusted connection.
> I knew form Microsoft SQL Server administration book that a trusted
> connection is used for windows authentication.
> I have reinstalled SQL Server on the Subscriber using Windows
> authentication, the publisher remains the same, but te problem remains the
> same.
> Can a merge publication be used when both the publisher and the subscriber
> use SQL Server authentication?!
> Thanks in advance,
> Shehab.

Replication and DBCCs

I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
replication need stopped in order to facilitate the dbccs'? Should a new
snapshot be taken following the dbccs?
No need to reinitialize, pause etc - you can run these in place.
If yo are running DBCC DBREINDEX you might notice some delay before the
synchronization completes though because of the locking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul.
"Paul Ibison" wrote:

> No need to reinitialize, pause etc - you can run these in place.
> If yo are running DBCC DBREINDEX you might notice some delay before the
> synchronization completes though because of the locking issues.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There have been some reports that some DBCC commands make the log reader
stop reading transactions. This was in SQL 2000.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
> replication need stopped in order to facilitate the dbccs'? Should a new
> snapshot be taken following the dbccs?
|||We experience similar things during our weekly DBCC REINDEXing on the
replicated database. It stops the distributor from applying replicated
transactions and causes the distribution agent to timeout and fail eventually
after the retry count is exceeded.
Is there a way to automate the checking of distribution agent if it is
running at the end of REINDEXing and to restart distribution agent if not.
Thanks
PVG
"Hilary Cotter" wrote:

> There have been some reports that some DBCC commands make the log reader
> stop reading transactions. This was in SQL 2000.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
> news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>
>
|||You could modify the reindex job to have an additional step before :
exec msdb..sp_stop_job 'distribution agent job name'
and an additional step at the end:
exec msdb..sp_start_job 'distribution agent job name'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||How about scheduling the distribution agent not to run in the interval you
are doing your re-indexing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"PVG" <PVG@.discussions.microsoft.com> wrote in message
news:199ED0FD-FEE5-47AA-BE0D-CD08BB4C5B92@.microsoft.com...[vbcol=seagreen]
> We experience similar things during our weekly DBCC REINDEXing on the
> replicated database. It stops the distributor from applying replicated
> transactions and causes the distribution agent to timeout and fail
> eventually
> after the retry count is exceeded.
> Is there a way to automate the checking of distribution agent if it is
> running at the end of REINDEXing and to restart distribution agent if not.
> Thanks
> PVG
> "Hilary Cotter" wrote:

Replication & Report execution

I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.

Replication - distribution agents?

When distribution is scheduled to run at specific times for specific user, but that user is not connected to the server. Distribution tries to find that subscription, the error comes back:

The step was retried the requested number of times(10) without succeeding.

Where/how can I change the requested number to try two or three times.

My problem is that it takes 10-15 minutes for those re-trys and which sometimes causes overlap with the next subscription coming in.Check sp_update_jobstep in BOL, look for @.retry_attempts and @.retry_interval
The best way would be to use a PULL subscription at the subscriber instead of PUSH at the publisher.

Originally posted by cruzin
When distribution is scheduled to run at specific times for specific user, but that user is not connected to the server. Distribution tries to find that subscription, the error comes back:

The step was retried the requested number of times(10) without succeeding.

Where/how can I change the requested number to try two or three times.

My problem is that it takes 10-15 minutes for those re-trys and which sometimes causes overlap with the next subscription coming in.|||You can also identify the job in Enterprise Manager (Management->SQL Server Agent-> Jobs) then find the step and change the retry attempts there.

Originally posted by dbadelphes
Check sp_update_jobstep in BOL, look for @.retry_attempts and @.retry_interval
The best way would be to use a PULL subscription at the subscriber instead of PUSH at the publisher.

Replication

Hi
I have setup transaction replication, I defined publisher. Now I have new
server
Where I run create script for database and table from publisher server.
First time on my subscriber server's table has rows, I can’t restore from
backup because of time (it took about 8 hrs. to restore).
After setting up replication, my new subscriber got no rows, how tried
tranactional,snapshot replication data is populated on subscriber.
Advice me how to proceed with restoring db on my subscriber m/c.
Thanks
Kalyan
Advice me how to proceed without restoring db on my subscriber m/c.
"Kalyan" wrote:

> Hi
> I have setup transaction replication, I defined publisher. Now I have new
> server
> Where I run create script for database and table from publisher server.
> First time on my subscriber server's table has rows, I can’t restore from
> backup because of time (it took about 8 hrs. to restore).
> After setting up replication, my new subscriber got no rows, how tried
> tranactional,snapshot replication data is populated on subscriber.
>
> Advice me how to proceed with restoring db on my subscriber m/c.
> Thanks
> Kalyan
>

Replication

does anyone know if it's possible to run a replication task with a script, and how (is there ans sp_ for this ?)

thanks,

What task are you asking about?|||yeah , sorry for unprecisions. I was talking about merge agent. I'd like to control it "on-demand", instead of a plannification. i'm running sql 2000.|||

dont know the script..but u can use replmerg.exe to run it from command prompt... its in the com folder of sqlserver..u shud know the parameters for the agent which u can get from sysjobsteps(in msdb)

|||

it's an idea. i'm going to try this.

thanks.

Replicating Stored Procedure

Hi:
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
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
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>

Replicating Stored Proc Execution

Hi Guys:
When we run the proc from Query Analyzer, we can see the replication of
store proc execution on subscriber/distributor. However when we run this
proc from Java application, we don't see the proc being executed on
subscriber even though we see it on distributor. Any reason why execution of
proc is not replicated properly when executing it from Java. Please let me
know.
Thanks
When you say you see it in the distributor do you mean it shows up in the
output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
that it is running there with the correct parameters. Then run it manually
on the subscriber to see if it works there. It is possible that the proc is
referencing non-existent tables or data which is not present on the
subscribers.
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
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
> Hi Guys:
> When we run the proc from Query Analyzer, we can see the replication of
> store proc execution on subscriber/distributor. However when we run this
> proc from Java application, we don't see the proc being executed on
> subscriber even though we see it on distributor. Any reason why execution
> of
> proc is not replicated properly when executing it from Java. Please let
> me
> know.
> Thanks
|||that is what we did we used the profiler on the subscriber but we don't find
that proc when running from Java. You are saying that if the proc is
referencing non-existance table/data, it will not show up in profiler. We
are pretty sure that table is there. Any other thoughts.
Thanks
"Hilary Cotter" wrote:

> When you say you see it in the distributor do you mean it shows up in the
> output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
> that it is running there with the correct parameters. Then run it manually
> on the subscriber to see if it works there. It is possible that the proc is
> referencing non-existent tables or data which is not present on the
> subscribers.
> --
> 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
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
>
>
|||This is bewildering. There are some options for stored procedure execution -
like only running within a serializable transaction. Are you using any of
these?
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
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...[vbcol=seagreen]
> that is what we did we used the profiler on the subscriber but we don't
> find
> that proc when running from Java. You are saying that if the proc is
> referencing non-existance table/data, it will not show up in profiler. We
> are pretty sure that table is there. Any other thoughts.
> Thanks
> "Hilary Cotter" wrote:
|||Thanks for your setting. That is what we thought because we were using that
option (serializable). Now we changed it to execute procedure every time but
still having this problem. Not sure whether Java is using some setting
which is causing this.
"Hilary Cotter" wrote:

> This is bewildering. There are some options for stored procedure execution -
> like only running within a serializable transaction. Are you using any of
> these?
> --
> 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
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...
>
>