Environment:
Windows Server 2003 computer name: queen
Local Users: Administrator, John, Simon
To simplify, both John and Simon are members of Local Administrators group.
SQL Server 2005 installed
Three databases: AdventureWorks (case sensitive), aData, and bData
A table dbo.DOCS in AdventureWorks is shown:
Create a Merge Publication, Add Filter as shown below:
Create a subscription and make user the Merge Agent security use QUEEN\Administrator.
SUSER_SNAME() will return QUEEN\Administrator, or QUEEN\John, or QUEEN\Simon, etc. If your table has Queen\Administrator, queen\john, etc, subscription will be empty because the AdventureWorks is case-sensitive.You can create another subscription for bData database with Merge Agent security: QUEEN\John. You will find the records with QUEEN\John are replicated.
Interesting observation:
Insert a record into the Subscription:
You can see the record added. However after the Cycle of Merge Agent runs, the new record is not shown in the subscription. It shows in the Publisher.Same thing happens for bData.
Pay attention to the DocumentID number, which has the Identity property. Each subscription uses different range. The Identity is automatically managed by replication process.I didn't create real scenario to use HOST_NAME() (computer name). You should have a table with a computername column.
