Merge Rpl. Pull from subscriber access denied problem

Hi,
I have set up one laptop as the Distributer/Publisher. Went through the
wizard and set up a Publication also, used Pubs. Then registered another
remote laptop that I can see via the network, and it can see me. I went
through the wizard again and set up a Push to that laptop. Said it ran good,
and I can see the tables on the remote laptop now.
I deleted the Push and keep trying to create a Pull at the other laptop,
(subscriber). The wizard sets it up, but when it Starts Syncronizing, it
immediately gets the big Red X.
The error said The schema script
'\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_arti cles\20050928212317\stores_1.sch' could not be propagated to the subscriber.
I can see this share from both ends. I have read a ton about the accounts
the agent has to run under in the last 12 hours, but can't see what I am
doing wrong.
Is there a trick here?
Thanks.
Steve,
try logging on to the subscriber laptop using the same account that the sql
server agent uses as a service account. The see if you can browse to the
snapshot folder
\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
can, see if you can copy the contents of this directory locally. I'm
guessing that the first part won't be possible due to permission
restrictions, but please post back with your results.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I had tried some other things prior to being able to read your response.
Here is what I have done that is now working.
I set up new SQL users on both Publisher/Dist. and Subscriber, giving them
the proper roles. I then set up a new Windows login on each box, "Bob", of
type admin. I then changed both the MSSQLSERVICE and SQLAGENTSERVICE on each
to run under "Bob". When it asks for logon credintials for the subscriber, I
use the sa, when it asks for the publisher, I use the new SQL user I had set
up. Probably may have been able to use that for the subscriber instead of
the sa, don't know.
I have tried so many things since yesterday afternoon, I am a little unsure
as to what actually solved it. From all I read over night, having the two
service run under "Bob" was needed.
Thank you for the response,
Steve
"Paul Ibison" wrote:

> Steve,
> try logging on to the subscriber laptop using the same account that the sql
> server agent uses as a service account. The see if you can browse to the
> snapshot folder
> \\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
> can, see if you can copy the contents of this directory locally. I'm
> guessing that the first part won't be possible due to permission
> restrictions, but please post back with your results.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This is OK - what you've set up is known as pass-through authentication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is there a better or more preferred method? We have one laptop that acts as
the publisher/distributor, and two other laptops that will be subscribers.
They run over a wireless network. The two subscribers will be able to
initial pull merge replications.
Thanks,
Steve
"Paul Ibison" wrote:

> This is OK - what you've set up is known as pass-through authentication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Steve,
are the laptops all on the same domain? If so, you could use a domain
account, which is given rights to the distributor's working folder. If not,
it's either pass-through, FTP, backup and restore or alternative snapshot
locations.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge Replication: Insert Trigger ONLY when replicating

Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong here.
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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>

Merge Replication in Enterprise 2005 to Express 2005

I implemented a merge replication publication on a SQL Server 2005
Enterprise instance on a test server. On my laptop I have VS.NET 2005
installed with SQL Server 2005 Express.
It's my understanding that I can subscribe to a publication with
Express. I also have SQL Server Workgroup installed on my laptop so I'm
able to use Management Studio to control the Express instance.
The first issue I encountered was that you cannot subscribe to a
publication if you are not on the same network as the publishing
instance (it requires the actual server name). And in lieu of this I
tried to implement Web synchronization, but had problems with SSL
(which is my problem since I'm not entirely familiar with how to
implement SSL certs so they don't show the security warning on the
client without purchasing a certificate).
So then I decided to go ahead and create an account on my laptop and
add it to the domain of the test publishing server. I was then able to
subscribe to the published replication.
So then I look at my Express instance and the synchronization never
runs. In the documentation it says that you're supposed to be able to
right-click on the subscription and "View Synchronization" and force it
to run. This is not available in the Express instance, but it is
available in the Workgroup instance.
So I'm looking at cleaning up my web synchronization issues to make
that work, but I see two issues here.
1) Why do I have to specify the "real name" of the publishing database?
Why can't I access it like any other sql server via an IP address? This
seems like a highly restrictive implementation of replication to me.
2) Why do I have to implement SSL for replication? I understand the
need for security, but that should be left to me to implement. I
shouldn't be forced to use SSL as my security layer.
3) Why doesn't Express have "View Synchronization"?
Any help here is greatly appreciated.
David CornelsonRetry #1
Anyone?

Merge Replication in Enterprise 2005 to Express 2005

I implemented a merge replication publication on a SQL Server 2005
Enterprise instance on a test server. On my laptop I have VS.NET 2005
installed with SQL Server 2005 Express.
It's my understanding that I can subscribe to a publication with
Express. I also have SQL Server Workgroup installed on my laptop so I'm
able to use Management Studio to control the Express instance.
The first issue I encountered was that you cannot subscribe to a
publication if you are not on the same network as the publishing
instance (it requires the actual server name). And in lieu of this I
tried to implement Web synchronization, but had problems with SSL
(which is my problem since I'm not entirely familiar with how to
implement SSL certs so they don't show the security warning on the
client without purchasing a certificate).
So then I decided to go ahead and create an account on my laptop and
add it to the domain of the test publishing server. I was then able to
subscribe to the published replication.
So then I look at my Express instance and the synchronization never
runs. In the documentation it says that you're supposed to be able to
right-click on the subscription and "View Synchronization" and force it
to run. This is not available in the Express instance, but it is
available in the Workgroup instance.
So I'm looking at cleaning up my web synchronization issues to make
that work, but I see two issues here.
1) Why do I have to specify the "real name" of the publishing database?
Why can't I access it like any other sql server via an IP address? This
seems like a highly restrictive implementation of replication to me.
2) Why do I have to implement SSL for replication? I understand the
need for security, but that should be left to me to implement. I
shouldn't be forced to use SSL as my security layer.
3) Why doesn't Express have "View Synchronization"?
Any help here is greatly appreciated.
David Cornelson
Retry #1
Anyone?

Merge Replication in Enterprise 2005 to Express 2005

I implemented a merge replication publication on a SQL Server 2005
Enterprise instance on a test server. On my laptop I have VS.NET 2005
installed with SQL Server 2005 Express.
It's my understanding that I can subscribe to a publication with
Express. I also have SQL Server Workgroup installed on my laptop so I'm
able to use Management Studio to control the Express instance.
The first issue I encountered was that you cannot subscribe to a
publication if you are not on the same network as the publishing
instance (it requires the actual server name). And in lieu of this I
tried to implement Web synchronization, but had problems with SSL
(which is my problem since I'm not entirely familiar with how to
implement SSL certs so they don't show the security warning on the
client without purchasing a certificate).
So then I decided to go ahead and create an account on my laptop and
add it to the domain of the test publishing server. I was then able to
subscribe to the published replication.
So then I look at my Express instance and the synchronization never
runs. In the documentation it says that you're supposed to be able to
right-click on the subscription and "View Synchronization" and force it
to run. This is not available in the Express instance, but it is
available in the Workgroup instance.
So I'm looking at cleaning up my web synchronization issues to make
that work, but I see two issues here.
1) Why do I have to specify the "real name" of the publishing database?
Why can't I access it like any other sql server via an IP address? This
seems like a highly restrictive implementation of replication to me.
2) Why do I have to implement SSL for replication? I understand the
need for security, but that should be left to me to implement. I
shouldn't be forced to use SSL as my security layer.
3) Why doesn't Express have "View Synchronization"?
Any help here is greatly appreciated.
David CornelsonRetry #1
Anyone?

Merge Replication and publisher deleting subscriber inserts.

The row was inserted at 'Subscriber Laptop' but could not be inserted at
'Publisher server'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK1'. The conflict occurred in database 'Tst', table 'Report',
column 'ReportNo'.
http://support.microsoft.com/default...b;en-us;307356
We are using Merge Replication. Sql Server 2000 SP4 on the server and MSDE
SP4 on the laptops.
We've been testing for a few months and just starting to trickle into
production. This is the first time we've seen this error. From what I could
google, the KB I refer to above says we should have NOT FOR REPLICATION on
all FK constraints.
Now, all of my hits on this subject seem to be 2003 and earlier. So before I
go ahead and make this huge change, I want to confirm that yes, infact, I
should be doing this.
Should NFR be put on Primary Keys as well?
Can this NFR be specified through VISIO as that is where we maintain the
data model. If not, is there an alter statement we can apply to constraints
to turn this option on?
Buzz.
NFR can't be put onto PKs but definitely you should have it on the FKs (see
http://www.replicationanswers.com/Me...derArticle.asp)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)