Dynamic Repl....other than Host_Name or SUser_Name ?

Hello...
Are the only parameters allowed for dynamic filtering of data just the
Host_Name and SUser_SName?
Just curious...
thanks
- will
Will,
on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
host_name()) and UDFs using them. Actually the HOSTNAME can be overridden in
the merge job's parameters, so this is not as restrictive as it seems. On
SQL Server 2005 there is the option to override the HOSTNAME setting in the
subscription wizard. Scriptwise this corresponds to
sp_addmergesubscription @.hostname = 'hostname'
and is referred to as the "partition definition".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul..
I am currently overriding the Host_Name value in my application...so that
works fine. I was just hoping/wondering if there could be other parameters
(like a stored proc) that I could pass in... Oh well...guess I'll have to
wait for SQL 2025!
...
"Paul Ibison" wrote:

> Will,
> on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
> host_name()) and UDFs using them. Actually the HOSTNAME can be overridden in
> the merge job's parameters, so this is not as restrictive as it seems. On
> SQL Server 2005 there is the option to override the HOSTNAME setting in the
> subscription wizard. Scriptwise this corresponds to
> sp_addmergesubscription @.hostname = 'hostname'
> and is referred to as the "partition definition".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could use a UDF to transform the HOSTNAME, or use a join to an
intermediate table to produce a 'many' value filter - it depends on what you
would like to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||hey...interesting idea about using a UDF. I'll give that some brain power...
thanks!
"Paul Ibison" wrote:

> You could use a UDF to transform the HOSTNAME, or use a join to an
> intermediate table to produce a 'many' value filter - it depends on what you
> would like to achieve.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||HostName and SUSER_SNAME() can both be over ridden. SUSER_SNAME() can be
overridden by PublisherLogin.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SciuK3JGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Will,
> on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
> host_name()) and UDFs using them. Actually the HOSTNAME can be overridden
> in the merge job's parameters, so this is not as restrictive as it seems.
> On SQL Server 2005 there is the option to override the HOSTNAME setting in
> the subscription wizard. Scriptwise this corresponds to
> sp_addmergesubscription @.hostname = 'hostname'
> and is referred to as the "partition definition".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Dynamic Filtering, a further question

Am I correct to assume that the Host_Name function, when used in a filter
expression at the publisher will return the subsriber host name?
What does SUSER_SNAME return:
The name of the current logged on user at the subscriber?
The name of the SQL server run account ath the subsciber?
Or the SQLServerAgent run account at the subscriber?
Tony Toker
Data Identic Ltd.
That depends. If it is a pull subscription, which means the agent is running
on the subscriber it will return the name of the subscriber. If it is a push
subscription, which means the agent is running on the publisher it will
return the name of the publisher.
Unless of course we are talking merge replication and you override this
behavior by using the HostName parameter in the merge agent commands
section. (right click on your merge agent, select agent propertes, Steps,
run agent, click edit, and in the command section look or add a -HostName
and then enter the name you wish to be replaced by the HostName parameter
here.
SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:celd7r$n8l$1$830fa7b3@.news.demon.co.uk...
> Am I correct to assume that the Host_Name function, when used in a filter
> expression at the publisher will return the subsriber host name?
> What does SUSER_SNAME return:
> The name of the current logged on user at the subscriber?
> The name of the SQL server run account ath the subsciber?
> Or the SQLServerAgent run account at the subscriber?
> Tony Toker
> Data Identic Ltd.
>
|||<mini-quibble>
"SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber."
The Merge Agent typically runs under SQL Server Agent at the Distributor for
push subscriptions (not the publisher) or at the Subscriber for pull
subscriptions.
</mini-quibble>
Regards,
Paul Ibison
|||That is correct. Thanks for the correction.
Keep in mind that for most topologies - the publisher will be on the same
server as the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uM%23AusJeEHA.1000@.TK2MSFTNGP12.phx.gbl...
> <mini-quibble>
> "SUser_SName will resolve to account that the SQL Server agent runs under.
> For a push, its the SQL Server agent account on the publisher, for a pull
> the SQL Server agent account on the Subscriber."
> The Merge Agent typically runs under SQL Server Agent at the Distributor
for
> push subscriptions (not the publisher) or at the Subscriber for pull
> subscriptions.
> </mini-quibble>
> Regards,
> Paul Ibison
>

Dynamic Filter, Inline Table & HOST_NAME() - Does it work or MS BUG?

Hi All,
Can someone give me a definitive answer on the following:
Can I define a UDF that is used by my Dynamic Filter, receives the HOST_NAME() as a
parameter and passes back a table that I can perform a SELECT IN on ? An example is
shown below...
SELECT <published_columns> FROM [dbo].[Stores] WHERE StoreID
IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
This Dynamic Filter gives me an error
Error 170, line 1: Incorrect Syntax near '('.
I've had conflicting views on this, and I'd really like to get it sorted, any
input is welcome.
Cube,
I have only been able to use UDFs before when they are scalar, eg
SELECT <published_columns> FROM [dbo].[Region] WHERE region.regionid = dbo.fn_TaxRate(host_name())
works fine. In your case can you restructure your query to return a scalar value and then use this value combined with another table in a join + where clause perhaps?
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the reply. Not a bad idea. In relation to my question about passing in
HOST_NAME() into my inline-table UDF is it the case that this isn't possible ?
BTW - I'll give you all an update when I've tried the other suggestion...now :o)
Cheers
|||Cube,
I can't say definitively that it's not possible - I have always had the same
error message you've seen, and probably like yourself I've tried every
syntax combination for use of the table-leveled UDF I could think of. I've
never heard of anyone else using a table valued UDF in a dynamic filter, but
hopefully if someone else has they'll post it up.
Regards,
Paul Ibison

Dynamic Filter Not Updating Correctly

I have several SQL Mobile devices (WM5) syncronizing with a SQL 2005 database through merge replication. We are using the HOST_NAME parameter to filter several tables. A few of these filters use a sub-query to filter the data because of the complexity of the filter (need to do more than join to one table). The subquery will use the HOST_NAME parameter. For example, I want to get a list of all users in the current user's company I need to use host_name to get the current user, find their company ID, then filter the user table on that company ID.

Everything works fine until a user is removed from the table. In that case, the delete statement is not replicated to the device until they reinitialize the mobile database.

Thanks for the help!!

Mike

it's unclear what the problem is without knowing what your filter clause looks like. Could you script out your publication, article and any merge/join filter clauses?|||

One of the affected pieces consists of 2 tables. A USER_LOCATION table that contains the locations a user want to see and a LOCATION table that contains all locations (see script below). I filter the USER_LOCATION table with the following filter: WHERE cast(person_id as varchar(36)) = host_name(). I then use a join to extend the filter on the location_id to the LOCATION table. On the device, I do not use the USER_LOCATION table as it is really just used to filter the proper locations for the user. I have also tried a filter on the LOCATION table directly using a subquery (so that the user_location table never makes it to the device). The same problem still shows up: If a user removes a location from their list from our website (used to configure their settings, which are replicated to the device), the next sync does not delete the record from the device. In fact, the only way to get the delete to take is to reinitialize the database on the device.

Tables:

CREATE TABLE [dbo].[USER_LOCATION](

[user_location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_USER_LOCATION_user_location_id] DEFAULT (newsequentialid()),

[user_id] [uniqueidentifier] NOT NULL,

[location_id] [uniqueidentifier] NOT NULL)

GO

CREATE TABLE [dbo].[LOCATION](

[location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__LOCATION__locati__48CFD27E] DEFAULT (newsequentialid()),

[name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[abbreviation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

|||

Mike, it would be much easier if you scripted your publication, articles (maybe just for USER_LOCATION and LOCATION for now) and merge filter joins, can you do that please?

Just to be clear, when a row from LOCATION is deleted at the publisher, the delete isn't replicated down to the subscriber, correct? That would mean there's nonconvergence, and validation would fail. Could you verify that validation for the given subscriber fails?

|||

Actually, we just updated some of our code and upgraded to SP1 and the problem seems to be gone. Hopefully we won't see it in the future. Thanks for the help!

Mike

|||Hello,
I am facing the same problem. I have dynamic filters on most tables and the rows dont get deleted from the subscriber once they cease to satisfy the filter conditions.
HEre is a scenario: A Dynamic filter checks for suser_sname() and based on that(and a few other conditions) sends rows to the subscriber. when the data in the column used to compare the suser_sname() value changes at the publisher , the related row automatically got deleted from the subscriber. Recently this has stopped happening. Validation of the subscription also fails. Mike- What did you upgrade to SP1? We are running off SQL Server2005 SP2? thanks in advance. Rocky