Merge replication with dynamic filter

Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database.

I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc.

I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great.

Thanks in advance.

SUSER_NAME is the account that replication connects to the publisher with. You can override it with the -PublisherLogon property. By chance is this subscriber SQL Server Express? There are some limitations of SQL Server Express with replication, and I suspect that you are bumping into one of them. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 wrote in message news:740eab10-6268-4aff-9dc9-56651ebc9d3d@.discussions.microsoft.com... Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database. I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc. I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great. Thanks in advance.|||Yes, the subscriber is on SQL Express. In the mean time, I am trying to get it to work with HOST_NAME(). That doesn't seem to have the login/user/rights issues that SUSER_SNAME() does?|||You could override the -HostName parameter with what you normally use for the SUSER_NAME. For example if you want to filter on users, you could use a -HostName parameter of Fred. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 wrote in message news:927b07a9-77e7-4a97-b7f5-14f5f9b9a496@.discussions.microsoft.com... Yes, the subscriber is on SQL Express. In the mean time, I am trying to get it to work with HOST_NAME(). That doesn't seem to have the login/user/rights issues that SUSER_SNAME() does?|||

There are three situations related to your scenario:

1. if the merge subscription is push, there is a known Yukon bug, which tells there is no way to input a SQL Authentication user to login in Publisher/Distributor. Please refer to the below description of the bug.

In dynamic filter merge pub, if filter is like SUSER_SNAME(), and SUSER_SNAME is a SQL user, there is no way to input a SQL Authentication user to connect Pub/Dist in Merge Agent Security Page of CreateSub wizard.

For push sub, Publisherlogin and DistributorLogin can only be process account in UI.

2. If merge subscription is pull. It will be fine.

3. If subscriber is based on SQL Express and merge subscription is pull, it won't be supported. Since SQL Express doesn't support Merge agent at all, sync can only be done through Windowns Synchronization.

Hope the above information will help.

-Yunjing

|||

I got rid of everything that I had and started over this time using HOST_NAME(). Everything went smoothly and it worked the first time!

I'm not sure why the MS online books appear to focus on SUSER_SNAME() and mention HOST_NAME(). The use of SUSER_SNAME() looks to be way more complicated in accomplishing the same thing despite the bug mentioned.

Thanks for helping a new guy get going. The merge replication tool with filtering is going to save a bunch of additional application development.

|||

It's glad to see your scenario worked. SUSER_SNAME() is used more often than HOST_NAME(), since it can implement partitional filtering for different user instead of for different machine. The bug I mentioned is only for configuration through UI, if replication is set up through SP, everything still works fine.

Hope these information will help as well.

Thanks

Yunjing