Replication Transformation

I am currently trying to setup Transformation on a Transactional Publication.
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:

> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql

Replication Subscription Standard Edition

So I set up a "Replication/Local Publication" on a Production SQL
2005
Database.
It publishes a Database on the production database.
I then set up a "Replication/Local Subscription" on another Database
we call "Report Database"
that will have reports running on it and querying the Subscription
which is feed from the
production database.
They are syncronizing fine and everthing looks great.
My question is how do I access the Subscription? Can I query it like
a
database
using a "Database Engine Query"? Do I have to create a Database
instance on the "Report Database" Server
feeding off the Subscription?
Am I not going to be able to access the subscription because I don't
have snap shots?
I have no idea what to do now? How can I query the subscription? Can
the subscription act like a database?
I am having problems understanding the question. You have transactional
replication set up correctly and it is syncing ok to the Report Database.
This database can now be queried as per usual. if you are concerned about
blocking, you could use read_committed_snapshot isolation level to help with
the concurrency of reports.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Replication makes copies of tables and their data which by default will be
identical to the tables in the publication database. You should be able to
query them in the same way you query them in the publication database.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
> So I set up a "Replication/Local Publication" on a Production SQL
> 2005
> Database.
> It publishes a Database on the production database.
> I then set up a "Replication/Local Subscription" on another Database
> we call "Report Database"
> that will have reports running on it and querying the Subscription
> which is feed from the
> production database.
>
> They are syncronizing fine and everthing looks great.
>
> My question is how do I access the Subscription? Can I query it like
> a
> database
> using a "Database Engine Query"? Do I have to create a Database
> instance on the "Report Database" Server
> feeding off the Subscription?
> Am I not going to be able to access the subscription because I don't
> have snap shots?
>
> I have no idea what to do now? How can I query the subscription? Can
> the subscription act like a database?
>
|||On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Replication makes copies of tables and their data which by default will be
> identical to the tables in the publication database. You should be able to
> query them in the same way you query them in the publication database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173882913.696871.315700@.o5g2000hsb.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I'm just wondering how to query the subscription.
When the subscription is created on the Report Database is a database
created on that server?
|||On Mar 14, 8:15 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server... Hide quoted text -
> - Show quoted text -
I had an existing database on my Report Server that was the same name
of the Database on the Production Server that is getting publicated.
I am going to do a new publication on a different database and see how
it goes. Thanks guys
|||No, its not, you have to pre-create it.
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
> On Mar 14, 8:12 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I'm just wondering how to query the subscription.
> When the subscription is created on the Report Database is a database
> created on that server?
>
|||On Mar 14, 10:58 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No, its not, you have to pre-create it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "jughead" <mike.aar...@.gmail.com> wrote in message
> news:1173885320.499067.58050@.y66g2000hsf.googlegro ups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
You guys are right. I don't know what the heck I was doing when I
created my first Publication and Subscription.
Seems that everything is awesome now!
Thanks everyone
sql

Replication Stops at distributor in some cases

Hi,
I recently had a problem with a merge replication publication. In
some cases when data is changed on a subscriber, the change gets back
to the publisher but does not replicate from the publisher to the rest
of the subscribers. I checked the conflict viewer and there are no
conflicts. I am running SQL Server 2000 and I was wondering if anyone
is familiar with this problem.
Thanks,
Paul
Are you using any sort of filtering? This might account for this behavior.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408091019.79d49cf2@.posting.google.c om...
> Hi,
> I recently had a problem with a merge replication publication. In
> some cases when data is changed on a subscriber, the change gets back
> to the publisher but does not replicate from the publisher to the rest
> of the subscribers. I checked the conflict viewer and there are no
> conflicts. I am running SQL Server 2000 and I was wondering if anyone
> is familiar with this problem.
> Thanks,
> Paul
|||I do have some row filters on but none that comes into play in this
particular situation.
|||Can you give us some more details about this topology? How many subscribers,
etc.
I have tried to repro this on a local server with two subscribing database
and can't get the expected behavior. IE an update on Subscriber1 makes it to
the publisher and Susbcriber2. Same with updates on Susbcriber2.
Also can you query the conflict tables directly on the Publisher and
subcriber just to verify the conflict viewer is correct.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408120524.77f6d0ca@.posting.google.c om...
> I do have some row filters on but none that comes into play in this
> particular situation.
|||This publication has 12 subscribers (push subscriptions) that resync
every 2 hours. They are staggered to run 5 minutes apart. This only
happens with one particular table and I think it is perhaps a bug that
is just not listing the conflict. So far I have determined that this
happens when a personnel record is update on two different
subscribers. The only thing is in this case the fields being updated
on the two different subscribers do not overlap. So there should not
be any conflicts, and none of them are showing up in the conflict
manager. I also queried the conflict table and there were no rows in
there.
For example:
Field a and b are changed on subscriber X
Field d and c are changed on subscriber Y
publisher resyncs with X and gets the changes to field a and b
publisher resyncs with Y and gets changes to field d and c but does
update
Y with the changes from X.
I hope this helps, I am currently trying to reproduce this to have get
a more detailed version of what is going down. Thank you very much
for your help Hilary, it is appreciated.

replication setup

Hi,
I am using SQL 2005 server. If I want to replicate a table from A
server to B server, using transactional publication, do I setup a
publication in A server with article of the table and then setup a
subscription in B server? Am I over-simplifying or there is some thing I
need to set up? Cause now it is not changing the record in B server when I
change the record in A server.
Please help. Thanks.
Michael - have you set this up already? If the table is on Server B then I
assume that the initialization has worked and therefore the snapshot and
distribution agent have worked. Can you see if the distribution agent is on
a schedule and if it is, start the relevant job to send the recent
transactions over. Assuming this is the case, you might want to set the
agent to run continuously to reduce latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication Scripts Stopped Working..

I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.

The actual command i am executing is a simple

sp_addpublication_snapshot

but it fails with

Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!

I am using SQL 2005 SP2 if that makes any difference.

Thanks

Andy

It looks like you have an uncommitted transaction hanging around. Open a query window and type:

Commit Tran

GO

It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.

Hope this helps...

Scott

Replication Scripts Stopped Working..

I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.

The actual command i am executing is a simple

sp_addpublication_snapshot

but it fails with

Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!

I am using SQL 2005 SP2 if that makes any difference.

Thanks

Andy

It looks like you have an uncommitted transaction hanging around. Open a query window and type:

Commit Tran

GO

It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.

Hope this helps...

Scott

Replication scripts for 2000 same as 2005 ?

I am curious to know if i script the replication scripts for creating
publisher, publication,etc on 2000, can i safely run them on sql 2005 or are
there some changes to the metadata ?
Thanks
Yes you can run them no problem.
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
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23hK0gj8yHHA.1208@.TK2MSFTNGP03.phx.gbl...
>I am curious to know if i script the replication scripts for creating
>publisher, publication,etc on 2000, can i safely run them on sql 2005 or
>are there some changes to the metadata ?
> Thanks
>
|||Have a look in BOL at : "How to: Upgrade Replication Scripts".
The original ones should work OK but the recommendation is to make changes
to the security of the jobs.
Cheers,
Paul Ibison
sql

Replication Question

Hi,

I am trying to set-up a transactional replication.

1. Created a Publication with 1 table containing only the first four columns (out of 10 columns).

2. When I create a subscription, how do I map the Publication Columns to Subscription columns? In my subscriber DB, I need different column names. I don't get an option to map the columns ...

How do I do it? TIA.

1. To create article with part of columns, please refer to Books On line, title <To define a column filter for an article published in a snapshot or transactional publication>

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/504fd568-13ae-4dc4-88da-950c1ec1e8c1.htm

2. Subscription can have table name different to published table name, but columns name has to be same.

Thanks

Yunjing

Replication Question

Dear All,
After marked re-initialize an existing publication, a new snapshot has been
generated and applied to the subsciber, after that, there is an error on
Distributor agent saying that can't apply command as duplicate key error. I
have checked those insert commands and those records are already exists on
subsciber as new snapshot has been applied.
What I can do to resolve the problem? Do Distributor agent is smart enought
to ignore those old not yet replicated transactions which is older than my
latest snapshot being applied? What is the correct action sequence for
re-initialization?
Yours sincerely,
Henry
Henry,
see if this applies:
http://support.microsoft.com/default...b;en-us;813494
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Henry,
also, have a look at the post 'Subscription Choking' from yesterday, as
there might need to be an additional step if the Hotfix is applied:
http://groups-beta.google.com/group/...8?dmode=source
Rgds,
Paul Ibison
sql

Replication problems

I have set up a server with a publication of a very large database. A backup
server needs to retrieve the data and do some massive calculations (which
isn't really relevant). On the backup server I've created a subscription to
retrieve the database but I get the following error:

Message: The process could not retrieve file
'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the FTP
site '10.25.10.25'.

Details: The process could not retrieve file
'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the FTP
site '10.25.10.25'.
(Source: AGENT (Agent); Error number: 00000)
-----------------------
-----------

Now this file IS available and the server CAN connect to the FTP site since
it happens and it downloads a lot of other files/scripts before reaching
this one. The file is around 200 MBs which I suspect is the reason why. I
even have a table of 1.29 GBs I need replicated as well.

Does anybody know what goes wrong? Is there some sort of script timeout
setting I can adjust somewhere to get this to run properly?

=8-) Simon"Simon B. Nielsen" <simon@.webplanet.dk> wrote in message
news:EWqXa.45089$Kb2.1927005@.news010.worldonline.d k...
> I have set up a server with a publication of a very large database. A
backup
> server needs to retrieve the data and do some massive calculations (which
> isn't really relevant). On the backup server I've created a subscription
to
> retrieve the database but I get the following error:
> Message: The process could not retrieve file
> 'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the
FTP
> site '10.25.10.25'.
> Details: The process could not retrieve file
> 'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the
FTP
> site '10.25.10.25'.
> (Source: AGENT (Agent); Error number: 00000)
> -----------------------
--
> -----------
> Now this file IS available and the server CAN connect to the FTP site
since
> it happens and it downloads a lot of other files/scripts before reaching
> this one. The file is around 200 MBs which I suspect is the reason why. I
> even have a table of 1.29 GBs I need replicated as well.
> Does anybody know what goes wrong? Is there some sort of script timeout
> setting I can adjust somewhere to get this to run properly?
>
> =8-) Simon

You'll probably get a better response by posting this on
microsoft.public.sqlserver.replication. This newsgroup posting seems to be
about the same issue, but it's quite old, and there's no actual solution:

http://groups.google.com/groups?hl=...4%40tkmsftngp05

Simon|||"Simon B. Nielsen" <simon@.webplanet.dk> wrote in message
news:EWqXa.45089$Kb2.1927005@.news010.worldonline.d k...
> I have set up a server with a publication of a very large database. A
backup
> server needs to retrieve the data and do some massive calculations (which
> isn't really relevant). On the backup server I've created a subscription
to
> retrieve the database but I get the following error:
> Message: The process could not retrieve file
> 'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the
FTP
> site '10.25.10.25'.
> Details: The process could not retrieve file
> 'SQLCLUSTER_maersk_HR_maersk_HR\20030804101206\ATT ACH_224.bcp' from the
FTP
> site '10.25.10.25'.
> (Source: AGENT (Agent); Error number: 00000)
> -----------------------
--
> -----------
> Now this file IS available and the server CAN connect to the FTP site
since
> it happens and it downloads a lot of other files/scripts before reaching
> this one. The file is around 200 MBs which I suspect is the reason why. I
> even have a table of 1.29 GBs I need replicated as well.

Well, I suspect a couple of things.

The obvious, sure that's the right file? (I'm guessing it's a time stamp in
there?)

Is it possible the file is still be created?

When you say the server does other things, I'd tend to rule out permissions
and user context, but always a good place to start.

200 MB should not be a problem to FTP, but their may be a timeout you need
to reset. (can't recall where off the top of my head.)

> Does anybody know what goes wrong? Is there some sort of script timeout
> setting I can adjust somewhere to get this to run properly?
>
> =8-) Simon

Replication problem "can not insert null into colum step_name in sysjobs".

When trying to create a new publication using the wizzard i get the error message

"cannot insert null into colum step_name in sysjobs".

Replication was working fine on the server before. It stopped working when i installed SP3, i removed the old replication and tried setting it up again but get the error above when creating the publication.

Any ideas would be extremely useful. I have been unable to find any details of this on the web.

Many Thanks
Ash.Are you sure the clean up was successful? What I mean is there maybe some replication setups still reside in the system tables. I would check sysjobs first to see if there are unrecognized jobs in it and go from there.|||Originally posted by joejcheng
Are you sure the clean up was successful? What I mean is there maybe some replication setups still reside in the system tables. I would check sysjobs first to see if there are unrecognized jobs in it and go from there.

Thanks for the reply joejcheng.

I've tried quite a few different things. Like you the first thing I thought was to look in sysjobs and sysjobsteps, however there isn't anything in there that looks untoward.

Like you I'm inclined to think its something that hasn't been completely removed. I'd do a reinstall but its a production server so this isn't an option.

replication problem

Ok I'm going to post this again in another way.
Here's what I have.
2 databases p3e_master and winship.
winship has a merge publication that I use with sqlserverce for pocketpc
users. That works fine.
p3e_master has a transactional updateable publication that includes 3
tables. Those 3 tables are also included in the merge publication for
winship.
here's my problem.
If I update a value in any of the three tables either from winship or
p3e_master (with the sqlquery analyzer for instance) the change propogates
in either direction as it should.
However, when I change data on the pocketpc and the data is synced. The
update shows in winship (as it should), but it does not propogate back to
p3e_master.
Does aybody know if this aproach should work, and if so what do I need to do
to get it working correctly?
Thanks in advance
The various forms of replication don't work well together. You would be best
to use merge all the way for such a scenario as you describe.
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
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uk$I%23hYMFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ok I'm going to post this again in another way.
> Here's what I have.
> 2 databases p3e_master and winship.
> winship has a merge publication that I use with sqlserverce for pocketpc
> users. That works fine.
> p3e_master has a transactional updateable publication that includes 3
> tables. Those 3 tables are also included in the merge publication for
> winship.
>
> here's my problem.
> If I update a value in any of the three tables either from winship or
> p3e_master (with the sqlquery analyzer for instance) the change propogates
> in either direction as it should.
> However, when I change data on the pocketpc and the data is synced. The
> update shows in winship (as it should), but it does not propogate back to
> p3e_master.
> Does aybody know if this aproach should work, and if so what do I need to
> do
> to get it working correctly?
> Thanks in advance
>

Replication Problem

I am settng up a Distributor, Publisher and Publication. The subscriber will be enabled later.
A1 is the Distributor
A2 is the Publisher
I configureed A1 as Distributor and enter a password for A2 to use when connecting.
Then, I enabled A2 to be a Publisher.
When I try to create the Publication on A2, I get the following error:
Error 20028: The Distributor has not been installed correctly. Could not enable database for publishing. The replication option 'publish' of database 'dbname' has been set to false.
As a test, I enabled my local SQL Server to be a Publisher using the Distributor and it works fine.
Both A1 and A1 have SQL Services under the same domain admin. From A2, I can see the snapshot folder on the Distributor.
Could someone provide some insight into this?
Thank you in advance.
Robert M.
Thanks to those who read this...I kept at it. Did a select @.@.servername and it was null on the Publisher. Ran sp_addserver 'a2', 'local' and restarted the services.
Now I can create the Publication.
Robert M.
"Robert M." wrote:

> I am settng up a Distributor, Publisher and Publication. The subscriber will be enabled later.
> A1 is the Distributor
> A2 is the Publisher
> I configureed A1 as Distributor and enter a password for A2 to use when connecting.
> Then, I enabled A2 to be a Publisher.
> When I try to create the Publication on A2, I get the following error:
> Error 20028: The Distributor has not been installed correctly. Could not enable database for publishing. The replication option 'publish' of database 'dbname' has been set to false.
> As a test, I enabled my local SQL Server to be a Publisher using the Distributor and it works fine.
> Both A1 and A1 have SQL Services under the same domain admin. From A2, I can see the snapshot folder on the Distributor.
> Could someone provide some insight into this?
> Thank you in advance.
> Robert M.
>
>

Replication Problem

I am settng up a Distributor, Publisher and Publication. The subscriber wil
l be enabled later.
A1 is the Distributor
A2 is the Publisher
I configureed A1 as Distributor and enter a password for A2 to use when conn
ecting.
Then, I enabled A2 to be a Publisher.
When I try to create the Publication on A2, I get the following error:
Error 20028: The Distributor has not been installed correctly. Could not e
nable database for publishing. The replication option 'publish' of database
'dbname' has been set to false.
As a test, I enabled my local SQL Server to be a Publisher using the Distrib
utor and it works fine.
Both A1 and A1 have SQL Services under the same domain admin. From A2, I ca
n see the snapshot folder on the Distributor.
Could someone provide some insight into this?
Thank you in advance.
Robert M.Thanks to those who read this...I kept at it. Did a select @.@.servername and
it was null on the Publisher. Ran sp_addserver 'a2', 'local' and restarte
d the services.
Now I can create the Publication.
Robert M.
"Robert M." wrote:

> I am settng up a Distributor, Publisher and Publication. The subscriber w
ill be enabled later.
> A1 is the Distributor
> A2 is the Publisher
> I configureed A1 as Distributor and enter a password for A2 to use when co
nnecting.
> Then, I enabled A2 to be a Publisher.
> When I try to create the Publication on A2, I get the following error:
> Error 20028: The Distributor has not been installed correctly. Could not
enable database for publishing. The replication option 'publish' of databa
se 'dbname' has been set to false.
> As a test, I enabled my local SQL Server to be a Publisher using the Distr
ibutor and it works fine.
> Both A1 and A1 have SQL Services under the same domain admin. From A2, I
can see the snapshot folder on the Distributor.
> Could someone provide some insight into this?
> Thank you in advance.
> Robert M.
>
>

Replication over Internet

I want to create merge publication between Server A(publisher) and Client
B(subscriber) over Internet. However, because of the security reasons Server
A cannot be directly accessed over Internet. What is the solution for this
case?
If I seperate distrbutor from Server A and enable it to be accessed directly
over Internet and Client B subscribes through distrbutor can this be a
solution?
(VPN is not allowed)
Erkan,
as far as I understand, the merge agent will need connectivity between the
publisher and subscriber to read the MSmerge_contents and MSmerge_tombstone
tables on each box, and the absence of any connectivity will preclude this
from working. However, you could investigate republishing to achieve your
goal.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication on multiple servers

here what I want to do,
1) transactional replication using two servers
2)Configure one server as publisher and distributor
3)Create publication on server 1
4)Create immediate update push subscription on server 2
5)I want bidirectional replication

First three steps I have created successfully
for step 4,
I created a push subcription from server 1 on publication created in step 3.
I am unable to find the option "immediate updating subscriber" in the wizard of creating push subcription.

anyway even without this option I am unable to create the replication.
in the replication monitor it is displaying as follows for the publication,
Snapshot---succeeded---snapshot of 1 artile created
Log reader--idle------no replicated transaction available
Server2:db2--Push--failed--the process could not connect to subscriber 'Server2'

what is missing?

thanks in advance for your advice

mayurThere is one step missing in your Replication setup. You have to define 'Server 2' as a subscriber. And 'Server 2 has to be able to lookup 'Server 1' as a publisher.

As far as setting 'Server 2' as updatable subscriber, you need to not only turn on 'Allow_Sync_Trans' in publisher, 'update_mode' has to be 'Sync Trans' in subscriber.

One last recommandation, write script if you can.|||I succeeded creating replication from Server1 to server2.
The problem is Server1 is in one domain and Server2 is in seperate domain.
the data is being replicated from S1 to S2
the problem is when I add/update any record in S2 database then it gives error that,
"Login failed for user sa"

what should i do next
which user account should I set up at subscriber i.e S2

thanks

Q: One last recommandation, write script if you can.
what do you mean by that, as I am using Enterprise manager|||S2 is now defined as remote server (or link server) on S1.
The authentification of S2 is made via windows logins. You shoul do one of the follwoing.

1) Talk to your domain admin(s) and set a trust relationship between those two domains

2) If you want to use the sa user to authentificate from s1 to s2, make sure that SqlServer on S2 is set up with MixedMode authentificatio, or will not work

Good luck,
ionut|||In BOL, under system stored procedure there is a section with all the system stored procedures you need to create or disable replication. Writing script gives you more control in the replication setup. You can start out using EM, but eventually you will find things that you can't do with EM.

Replication of StoredProcedures

I am trying to replicate stored procedures (after the fact) using the
following script
EXEC sp_addscriptexec @.publication = 'BSC_UserPubl',
@.scriptfile = '[\\MGROVEDB10\data$\ReplData\iDeal\DynSnapShot\Sc ript\MGroveDB10_Ideal_Client_Repl_ScriptVER1.sql]'
,
@.skiperror = 1
and i get this error
Server: Msg 21331, Level 16, State 1, Procedure sp_MScopyscriptfile,
Line 40
Failed to copy user script file to the Distributor.(copy
"[\\serverDB10\data$\ReplData\iDeal\DynSnapShot\Sc ript\MGroveDB10_Ideal_Client_Repl_ScriptVER1.sql]"
"\\serverdb4\data$\iPlan\ReplData\2004051209421711 0")
Could some one help me with pointers as tyo what needs to be done to
get this going. This help will be very much apprciated
Thanks
RG
Ramesh,
can you try sharing the repldata folder and use
\\MGROVEDB10\ReplData\MGroveDB10_Ideal_Client_Repl _ScriptVER1.sql
HTH,
Paul Ibison
|||Thanks Paul, I will give it a shot and let you know of the progress
|||I shared the repldata folder and used a different publication
EXEC sp_addscriptexec @.publication = 'BSC_UserPubl',
@.scriptfile = '\\MGROVEDB10\ReplData$\MGroveDB10_Ideal_Client_Re pl_ScriptVER1.sql' ,
@.skiperror = 1
and I still get the error
Server: Msg 21331, Level 16, State 1, Procedure sp_MScopyscriptfile, Line 40
Failed to copy user script file to the Distributor.(copy "[\\MGROVEDB10\data$\Script.sql]" "\\mgrovedb10\data$\iPlan\ReplData\200405130913011 10")
In fact whene i tried executing the sp_MScopyscriptfile proc with the appropraite parameters i still get the same error.
Any pointers will be greatly appreciated
Thx
RG
-- Paul Ibison wrote: --
Ramesh,
can you try sharing the repldata folder and use
\\MGROVEDB10\ReplData\MGroveDB10_Ideal_Client_Repl _ScriptVER1.sql
HTH,
Paul Ibison
|||Ramesh,
you're still using hidden shares and I think this may be the issue. Can you
change to a normal share, add rights to allow the sql server agent and see
if that works. If not, then there is a known error with this type of
behaviour: http://www.mcse.ms/message641761.html and I would be tempted to
use linked servers or use remote registrations to do this.
Regards,
Paul Ibison

Replication of dependent objects...

Hi,
I have been trying to create a publication of all my views in my database.
The problem that i have, when the subscriber tries to apply the snapshot is
that some views are dependent to others views... so, the subscriber fails
(because the dependency has not been created yet).
This is happening in snapshot and merge replication... I know i can
workaround it, by just creating multiple publications (one per each level of
dependencies that i have), however, this adds more tasks to my already long
maintance list of TO-DOs...
Anyone has one idea? Should i just learn to live with this problem?
Thanks, Jos.
replication depends on sysdepends which is not always accurate. You can try
to fix sysdepends, or replication your stored procedures/functions/views
using a post snapshot command.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been trying to create a publication of all my views in my database.
> The problem that i have, when the subscriber tries to apply the snapshot
is
> that some views are dependent to others views... so, the subscriber fails
> (because the dependency has not been created yet).
> This is happening in snapshot and merge replication... I know i can
> workaround it, by just creating multiple publications (one per each level
of
> dependencies that i have), however, this adds more tasks to my already
long
> maintance list of TO-DOs...
> Anyone has one idea? Should i just learn to live with this problem?
> Thanks, Jos.
>
|||Jos,
I have had the same issue and as Hilary says, the main option is to fix the
dependencies. Correcting the order even applies to a script run after the
snapshot, in the case of views, which (unlike stored procecures) don't use
deferred name resolution.
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
HTH,
Paul Ibison
|||Thanks for your answer...
However, what is a "post snapshot command"?
Do you mean to not include my sp/functions/views in the original snapshot,
and then running the scripts to create the objects?
Thanks again... Jos
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> replication depends on sysdepends which is not always accurate. You can
try[vbcol=seagreen]
> to fix sysdepends, or replication your stored procedures/functions/views
> using a post snapshot command.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
database.[vbcol=seagreen]
> is
fails[vbcol=seagreen]
level
> of
> long
>
|||I'll sure check out the link you've provided...
Thanks a lot... Jos.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||If your post snapshot script is jumbled you will get warning messages while
running the script referring to the sysdepends problem, but your
distribution agent won't fail. So it will work.
How do you fix the dependencies on the publisher so the snapshot script is
generated correctly in the first place? AFAIK - there is no way to fix
sysdepends.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||"AFAIK - there is no way to fix sysdepends."
Yeah... i have been looking for a way to recreate the dependencies, but the
only one that I have found is to recreate all objects (which is crazy).
It seems so logical that there should be a SP that recreate the dependencies
information for a given object (the same SP that MSSQL should use to create
that information in the first place)... but either it doesn't exist, or
nobody knows it exists ...
Jos.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If your post snapshot script is jumbled you will get warning messages
while[vbcol=seagreen]
> running the script referring to the sysdepends problem, but your
> distribution agent won't fail. So it will work.
> How do you fix the dependencies on the publisher so the snapshot script is
> generated correctly in the first place? AFAIK - there is no way to fix
> sysdepends.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> the
the[vbcol=seagreen]
use
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||evidently this problem plagues other RDBMS's.
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:O2PhDYtYEHA.4068@.TK2MSFTNGP10.phx.gbl...
> "AFAIK - there is no way to fix sysdepends."
> Yeah... i have been looking for a way to recreate the dependencies, but
the
> only one that I have found is to recreate all objects (which is crazy).
> It seems so logical that there should be a SP that recreate the
dependencies
> information for a given object (the same SP that MSSQL should use to
create[vbcol=seagreen]
> that information in the first place)... but either it doesn't exist, or
> nobody knows it exists ...
> Jos.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> while
is[vbcol=seagreen]
fix[vbcol=seagreen]
> the
> use
in
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||right click on your publication, select publication properties, in the
snapshot tab there is an option for pre and post snapshot scripts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for your answer...
> However, what is a "post snapshot command"?
> Do you mean to not include my sp/functions/views in the original snapshot,
> and then running the scripts to create the objects?
> Thanks again... Jos
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> try
> database.
snapshot
> fails
> level
>
|||thanks
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyBcx1vYEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> right click on your publication, select publication properties, in the
> snapshot tab there is an option for pre and post snapshot scripts.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...
snapshot,[vbcol=seagreen]
can[vbcol=seagreen]
procedures/functions/views[vbcol=seagreen]
> snapshot
already
>

replication monitor error

When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?

Thank you!

Thanks for using replmonitor.

Would you like provide more detailed repro steps? like how do you remove publicaiton jobs, delete jobs under sqlserveragent, or remove subscriptions? How did you reconfigure everything, did you disable distribution and publication, then recreate them?

Thanks

Yunjing

|||My problem has been resolved by restart sql server agent. Thank you!

replication monitor error

When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?

Thank you!

Thanks for using replmonitor.

Would you like provide more detailed repro steps? like how do you remove publicaiton jobs, delete jobs under sqlserveragent, or remove subscriptions? How did you reconfigure everything, did you disable distribution and publication, then recreate them?

Thanks

Yunjing

|||My problem has been resolved by restart sql server agent. Thank you!