Merge Agent fails on Visual Basic-created publication

Merge Agent fails on Visual Basic-created publication

Post by Pete Saunde » Fri, 12 Apr 2002 14:18:32



Hello-
     I have a problem with a merge replication scenario that I am
trying to implement using Visual Basic.  I have 2 Win2K servers, both
running SQL 2K at Service Pack 2.  I am trying to set up a push merge
replication from one existing DB to another existing DB.  I am using
integrated security on both SQL servers, with a domain admin account
running both the SQL Server Agent and SQL Server service.  If I create
the publication and run the snapshot agent and then synchronize the
merge agent (options to delete all rows in target table, script
nothing, and checkboxes to initialize schema and data) using
Enterprise Manager, all works.  If I try to do the same using SQL-DMO
objects, the snapshot agent runs fine, but the synchronization fails
with 3 messages in the history.  The first is an OS message indicating
that either the path can't be found (snapshot at \\SERVER\c$\share) or
filename/directory name/syntax is incorrect (snapshot at
\\SERVER\share), followed by the merge agent indicating that it
couldn't read the file at the snapshot location, followed by the final
error indicating that the merge agent couldn't propagate the script to
the subscriber.  I have tried setting up shares with completely open
access, and have set the various DMO objects (SQLServer, Distributor,
DistributionPublisher) security options to all available, as well as
trying both ImpersonateClient = True and False.  If I stop my VB
application after it has created the publication but BEFORE it has
added articles, I can manually add articles through Enterprise Manager
and push a subscription successfully.  However, if my app adds the
articles to the publication, any subsequent manual or VB merge
synchronization fails with the messages listed above.  One other
thing-the CompatibilityLevel of my VB-created publication appears to
be set at SQL7.0 regardless of what I do in VB, which leads to a
warning message appearing when I manually (using EM) add the first
article to it stating that all subscribers must be at SQL2000.  I
don't get the same message when I create the publication using EM.
Any ideas would be gratefully received, as I am at my wit(less) end.
Thanks
Pete Saunders
 
 
 

Merge Agent fails on Visual Basic-created publication

Post by David Sheaffer » Fri, 19 Apr 2002 07:06:34


Pete,
  For the DistributionPublisher2 object make sure you set the
TrustedDistributorConnection property to true to use NT authentication.  
Also when you are running the Visual Basic application are logged into NT
with the same account as the SQL Server agent startup account?  If not then
try that account and verify the account you are logged in as has
permissions to the snapshot folder location share.
  For the compatibility message are you using the TransPublication or
TransPublication2 object?  The later is the newer version in SQL Server
2000.  Also consider using the TransArticle2 object vs. the TransArticle.


This posting is provided 'AS IS' with no warranties, and confers no rights.

 
 
 

Merge Agent fails on Visual Basic-created publication

Post by Pete Saunde » Fri, 19 Apr 2002 19:37:43



> Pete,
>   For the DistributionPublisher2 object make sure you set the
> TrustedDistributorConnection property to true to use NT authentication.  
> Also when you are running the Visual Basic application are logged into NT
> with the same account as the SQL Server agent startup account?  If not then
> try that account and verify the account you are logged in as has
> permissions to the snapshot folder location share.
>   For the compatibility message are you using the TransPublication or
> TransPublication2 object?  The later is the newer version in SQL Server
> 2000.  Also consider using the TransArticle2 object vs. the TransArticle.


> This posting is provided 'AS IS' with no warranties, and confers no rights.

David-
     Thanks for the response.  I have been using the ***2 objects in
order to take advantage of the SQL2000 functions, and I do have the
TrustedDistributorConnection set.  I did find a work-around, however.
It seems that as long as you set at least one of the
CreationScriptOptions for the merge article you are creating
(collation, extended properties, etc), the merge agent is able to find
the snapshot.  If, however, you use
SQLDMOCreationScript_DisableScripting (value 0), the problem appears.
I have an incident on file with Microsoft, and the guy I'm working
with left me a message yesterday indicating that this may be a known
issue.  The really interesting thing to me is that if you create the
publication in VB but add the articles to it using Enterprise Manager,
it has no problem setting DisableScripting (no checkboxes checked) and
having the merge agent find the snapshot.  I was under the impression
that EM also uses SQL-DMO.
Thanks
Pete Saunders
 
 
 

Merge Agent fails on Visual Basic-created publication

Post by David Sheaffer » Sat, 20 Apr 2002 00:40:01


Pete,
  If you specify SQLDMOCreationScript_DisableScripting value of 0, then no
snapshot files are scripted.  Hence the distribution agent will fail to
find the file.  Therefore you need to specify an option for
CreationScriptOptions.


This posting is provided 'AS IS' with no warranties, and confers no rights.

 
 
 

1. One of publication listed under Replication Monitor/Agents/Merge Agents was automatically removed

We are running merge replication on SQL7. Totally there are 8
publication-subscription pairs created, all of them are listed under
Replication Monitor/Agents/Merge Agents. However, since last week, One
of publication-subscription pairs listed under Replication
Monitor/Agents/Merge Agents was automatically removed during midnight,
but this publication still listed under Replication
Monitor/Agents/Snapshot Agents, subscriber is still listed in the Pull
Subscription under subscribed database, and publisher is still listed
in the publication under the publishing database. Any ideas why this
might be?  I sure would appreciate your help.

2. Formating text in a Rich Text Box

3. Merge Replication : Merge Agent fails with Garbled sp name errors

4. 29590-AK-Alaska-IBM-AIX-UNIX-RS/6000-ORACLE-Communication Skills-Project Managem

5. Merge Agent Fails - sp_MSupdategenhistory call fails

6. Middleware

7. Moving Merge Agents and publications

8. Advanced Pick for DOS

9. Snapshot & Merge Agent Exists , Publication Does Not

10. Distribution Agent Fails (Transactional Publication)

11. Distribution Agent Error - Publication Failed to Push

12. Cannot create Pull subscription to Merge Publication

13. Creating a Merge Publication with SQL-DMO