Re: Can't view merge agent properties (trying again)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: MSDN-Managed (MSDN-Managed_at_nospam.nospam)
Date: 12/21/04 

Date: Tue, 21 Dec 2004 17:04:00 -0500

Distributor and Publisher are the same machine. I've taken a default
install of SQL Server - created one database - and opened up some tables to
replication. That's it. My feeling is that this is some type of Microsoft
issue that isn't clearly documented anywhere. Who knows? The issue is that
it's not documented anywhere.

SQL CE is the subscriber.

I have this same issue on the development, test, and production servers at
the client (so it's not just my laptop). I have VMWare and was trying to
create a step-by-step manual for the client - I believe that this is how SQL
Server was set-up for Replication (on those servers they are named
instances):

   *SQL Server CE Virtual Directory Creation Wizard
   Virtual Directory Alias and Content Folder
     Enter the alias you want to use to access this virtual directory. Use
the same naming convention as a folder: sqlce

   * SQL Server CE Virtual Directory Creation Wizard
     If you plan to use SQL Server merge replication with SQL Server CE
applications, you must set the NTFS permissions on the snapshot folder of
the computer running SQL Server.

8. Create PIPES database in SQL Server
blah, blah, blah

9. Create share
   a. Navigate (using explorer) to: C:\Program Files\Microsoft SQL
Server\MSSQL$HHPR\REPLDATA

   b. Right-click on REPLDATA folder and select Properties.

   c. Click the 'Sharing' tab.

   d. Click the 'Share this folder' radio button (leave default 'Share name'
of 'REPLDATA')

   e. Click the Permissions button

   f. On the 'Share Permissions' tab click the 'Add...' button.

   g. From the 'Select Users or Groups' dialog, find the
machine_name\IUSR_machine_name (DOB-TEST\IUSR_DOB-TEST) user, select it, and
click the 'Add' button.

   h. Click the OK button. Leave the permissions as is, and click OK again.

   i. Click the 'Security' tab.

   j. Click the 'Add...' button.

   k. From the 'Select Users or Groups' dialog, find the
machine_name\IUSR_machine_name (DOB-TEST\IUSR_DOB-TEST) user, select it, and
click the 'Add' button.

   l. Click the OK button. For the permissions of this new user, ensure
that only the Allow/Read checkbox is checked (uncheck 'Read & Execute' and
'List Folder Contents')

   m. Click the OK button to close out the 'REPLDATA Properties' dialog.

10. Configure Distribution
   a. Expand tree on left:
        Microsoft SQL Servers
        - SQL Server Group
          - DOB-TEST\HHPR (select - highlighted)

   b. Tools -> Replication -> Configure Publishing, Subscribers, and
Distribution...

   c. Configure Publishing and Distribution Wizard for 'DOB-TEST\HHPR'
      * Welcome to the Configure Publishing and Distribution Wizard
      Next

      * Select Distributor
      Next (default of "Make 'DOB-TEST\HHPR' its own Distributor; SQL Server
will create a distribution database and log)

      * Configure SQL Server Agent
      Next (default of "Yes, configure the SQL Server Agent service to start
automatically)

      * Specify Snapshot Folder
      Snapshot folder: \\machine_name\REPLDATA (\\DOB-TEST\REPLDATA)
      (change from default UNC naming convention to the share we created -
no need to browse)

      * Customize the Configuration
      Next (default of "No, use the following default settings:")

      * Completing the Configure Publishing and Distribution Wizard
      Finish (allow SQL Server Enterprise Manager dialog to finish each
step)

      - Should receive message describing success (click the OK button).

      - After clicking the OK button, you will now receive an informational
dialog stating the addition of the 'Replication Monitor' branch to the
console tree (click the Close button).

11. Create publication
    a. Expand tree on left:
         Microsoft SQL Servers
         - SQL Server Group
           - DOB-TEST\HHPR
             - Databases
               - PIPES (select - highlighted)

    b. Right-click and select 'New -> Publication...'

    c. Create Publication Wizard
       * Welcome to the Create Publication Wizard
       Next

       * Choose Publication Database
       Next (ensure that PIPES is the 'Database' selected)

       * Select Publication Type
       Click the 'Merge publicaton' radio button (followed by Next)

       * Specify Subscriber Types
       Ensure that only the 'Devices running SQL Server CE' checkbox is
checked (followed by Next)

       * Specify Articles
       - In the List Ctrl on the left, for 'Object Type' 'Tables' click the
'Publish All' checkbox (this will check all the checkboxes for the table
names in the right List Ctrl).
       - In the List Ctrl on the right, UNCHECK any tables that begin with:
"mstage_" (should be 9 tables)
       - Click Next

       * Select Publication Name and Description
       Ensure that the 'Publication name' is PIPES (followed by Next)

       * Customize the Properties of the Publication
       Click the "Yes, I will define data filters, enable anonymous
subscriptions, or customize other properties" radio button (followed by
Next)

       * Filter Data
       Check the "Horizontally, by filtering the rows of published data"
checkbox (followed by Next)

       * Enable Dynamic Filters
       Click the "Yes, enable dynamic filters" radio button (followed by
Next)

       * Generate Filters Automatically
       - In the "Table to filter" combobox select [dbo].[Work_Order]. This
fills the following text into the Edit control below it: "SELECT
<published_columns> FROM [dbo].[Work_Order] WHERE"
       - Add to the end of the 'WHERE' clause: "Inspector_ID = HOST_NAME()"
       - Click Next (allow dialog that appears to finish it's steps -
"Generating filters for publication 'PIPES'")

       * Filter Table Rows
       Next (leave all defaults)

       * Validate Subsciber Information
       Next (leave default of "Yes, validate Subscriber information")

       * Optimize Synchronization
       Click the "Yes, minimize the amount of data" radio button (followed
by Next)

       * Allow Anonymous Subscriptions
       Next

       * Set Snapshot Agent Schedule
       - Check the "Create the first snapshot immediately" checkbox
       - Click the "Change..." button
       - Edit Recurring Job Schedule - DOB-TEST\HHPR
         - Daily frequency - Occurs once at 12:00:00 AM
         - Duration - Click the "No end date" radio button
         - Click OK
       - Next

       * Completing the Create Publication Wizard
       Finish (allow the "Creating Publication 'PIPES' from Database 'PIPES'
dialog to finish the steps it needs to perform - and click Close on the
dialog noting successful completion)

William Campbell

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in message
news:n4UL2mz5EHA.3152@cpmsftngxa10.phx.gbl...
> Hi William,
>
> Thanks for your prompt updates!
>
> Please understand that replication with SQL CE is not supported in
> newsgroup and its issue might tend to be very complex and hard to
> troubleshoot in newsgroups. If you need further assistance, I recommend
> that you open a Support incident with Microsoft Product Support Services
> (PSS) so that a dedicated Support Professional can work with you in a more
> timely and efficient manner. If you need any help in this regard, please
> let me know.
>
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
>
> However, If you'd still like to continue working via the newsgroup, I want
> to set your expectations that the issue might take a long time to narrow
> down. During the course of troubleshooting, we may redirect you to PSS if
> required.
>
> For now, it seems that merge agent is running on the Distributor and it
> failed to connect the publisher. By default a subscriber will try to use
NT
> authentication when connecting to the publisher. So, I would like to check
> your computer environment and scenario. Furthermore, would you please let
> me know how do you configure the merge replication? You use your SQL CE as
> Publisher or Subscriber? Is it possible for you to show me the detailed
> steps you configure this Replication?
>
> Please show me whether there is anything useful in Event Log (use Event
> Viewer from Start -> Run -> Eventvwr.msc) and SQL Error Log (defaultly in
> C:\Program Files\Microsoft SQL Server\MSSQL\LOG)when it reports "SQL
Server
> does not exist or access denied". You are encouraged to send these files
to
> me via email when you have concerns on the log privacy. My email address
is
> v-mingqc@online.microsoft.com (Please NOTE that "online" is only for SPAM,
> do not include it in)
>
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
>
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---------------------------------------------------------------
> Get Secure! - http://www.microsoft.com/security
>
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Quantcast