Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Replication Issue with Dynamic Filter using HOST_NAME()

Answered Replication Issue with Dynamic Filter using HOST_NAME()

  • Wednesday, January 03, 2007 2:37 PM
     
     
    Sign In to Vote
    0

     

    Dear ppl,

    I am having a couple of problems with using dynamic filters in Merge Replication (SQL Server 2005 - SQL Mobile)

     

    1)  I am trying to add a dynamic filter to my publication using the wizard as follows:

    SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

    This gives me an error Conversion failed when converting the nvarchar value 'MSSWS02' to data type int. because my computer name cannot be converted to an integer.

    After reading on http://msdn2.microsoft.com/en-us/library/ms152478.aspx I have found that it has been recommend specifying CONVERT(nchar,AuditID) = HOST_NAME() in the wizard and then use sp_changemergearticle to change the clause to AuditID = CONVERT(int,HOST_NAME()) before creating a snapshot for the publication. Therefore I tried to run the following sql inside SQL Server, but it gives me the same error.

    DECLARE @publication AS sysname;
    DECLARE @article AS sysname;
    SET @publication = N'HASSystem';
    SET @article = N'Audit';

    USE [HASSystem]

    EXEC sp_changemergearticle
    @publication     = @publication,
    @article = @article,
    @property     = N'subset_filterclause',
    @value     = N'AuditorID = CONVERT(int,HOST_NAME())',
    @force_invalidate_snapshot = 1,
    @force_reinit_subscription = 1;
    GO

    Conversion failed when converting the nvarchar value 'MSSWS02' to data type int.

    Anyone knows whats wrong with it ? Is there anything i m missing?

     

    2)  The second problem I am having is that I added a dynamic filter to my publication

    SELECT <published_columns> FROM [dbo].[Audit] WHERE CONVERT(nchar,AuditorID) = HOST_NAME()

    which works fine but later on I decided to drop this dynamic filter and reinitialise the subscriptions and creating a new snapshot. But after deleting the dynamic filter and when snapshot agent tries to create a new snapshot, it gives me the following error.

    The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

    Anyone knows whats wrong with it? How can i redefine my publication so that it doesn't use dynamic filters?

     

    Regards
    Nabeel Farid

     

     

All Replies

  • Friday, January 05, 2007 1:37 AM
     
     
    Sign In to Vote
    0

    I wouldnt set the dynamic filters with conversions, there could be other issues down the road.

    To get around the problem of

    The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

    use sp_changemergepublication and set dynamic_filters='false'

    Also make sure your articles' filter clause are nulled out too.

     
  • Friday, January 05, 2007 7:55 PM
     
     
    Sign In to Vote
    0

     

    thanx for the help Mahesh... As for the conversion issue, what would you recommend then ? I am doing replication between SQL Mobile and SQL Server 2005. I want to filter out data based on AuditorID in a table on PDA, which is an integer. The SqlReplication class exposes a HostName property which is a string and is the only parameter that i can use as far as my knowledge is concerned.

    In articles in the publication i recieve that AuditorID in HOST_NAME() which is a string as well. That is why in dynamic filters I convert HOST_NAME() to int or the UserId column to string (viceversa) as follows

    SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

    Is there any way I don't have to use conversion? and also if  i can  pass more parameters from the PDA  ?

    Regards

     
  • Tuesday, January 09, 2007 7:00 AM
     
     Answered
    Sign In to Vote
    0

    I am not totally against the conversion. It works in some cases and it doesnt in others. I just did not want you to take this road and face some issues down the road.

    You could add a new column which is string and use this for filtering. the values in this column would correspond to the ones in the AuditorID. If you cannot, then continue as per the recommendation and we can troubleshoot any problems you would face.

     
  • Tuesday, January 09, 2007 12:57 PM
     
     
    Sign In to Vote
    0

     

    Thanx for the help Mahesh.

    I have another question for you. One of our client has asked us how is the data secured over the air during Replication?

    I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.

    We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and using GPRS connection, they all synchronise with a 2 Publications SQL Server 2005. Both publications are of the same database and resides on the same server.  We haven't got any SSL certificates installed on the server.

    Now how can i make use of TLS in my application to secure my data over the air (using TLS)? 

    Regards,

     
  • Wednesday, January 10, 2007 6:59 PM
     
     
    Sign In to Vote
    0

    Replication does not provide anything out of the box for secure transmission or encryption. It relies on the SQL server protocols to do that.

    You can use the certificates and SSL for encrypting data sent to/from SQL Server.

    Take a look at this article and see if that helps you. http://msdn2.microsoft.com/en-us/library/ms189067.aspx

     

     
  • Monday, January 22, 2007 10:04 PM
     
     
    Sign In to Vote
    0
    I would still like to know how to do the conversion.  Using sp_changemergearticle still generates the same "cannot convert" error message that the New Publication Wizard generates.

    What is the workaround?  I would really like to use a UserID (int) in the filter clause.
     
  • Monday, January 22, 2007 10:39 PM
     
     
    Sign In to Vote
    0

    I had the same problem and i coudn't figure out how to convert the hostname() either though wizard or through the sp_changemergearticle ...  it gives the same error....therefore I had to convert the column to string to make it work instead of Hostname...(which is not a recommended practice)... and u might face problems with that later on...depends
     
  • Monday, January 22, 2007 11:24 PM
     
     
    Sign In to Vote
    0
    I agree that there are a number of messy workarounds.  However, in the article you cited early in this thread (concerning Parameterized Filters) there is a large, bolded note about using sp_changemergearticle.

    If this is out of date and no longer works for security/whatever reasons, it'd be nice to hear from an MS dev/test.  However, if that note in the article still applies but with some restrictions (i.e. some system parameters need to be set initially), I'd like to know that as well.
     
  • Wednesday, September 12, 2007 8:34 PM
     
     
    Sign In to Vote
    0
    Did anyone figure out if this can be done without converting the column to a string.  I really want to keep the column as an int and be able to override the hostname() with an int I pass in.

     

     
  • Wednesday, November 21, 2007 10:27 PM
     
     
    Sign In to Vote
    0

     

    Hi there,

     

    I have recently been faced with an issue relating to deletion of rows from the subscriber when a row ceases to match the Dynamic Filter Criteria.

     

    Here is the 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 used to automatically get deleted from the subscriber. Recently this has stopped happening. Validation of the subscription also fails when records are changed at the publisher. All other Inserts /updates/deletes are happening normally. This issue is there only on tables with dynamic filters.

     

    We are running off SQL Server2005 SP2.

     

    Any help wil be much appreciated

     

    Regards

    Rocky

     

     
  • Friday, June 06, 2008 9:47 PM
     
     
    Sign In to Vote
    0
    I solved it using this variant:

    Instead of just CONVERT(int, HOST_NAME()) I used

    ...WHERE [sucId] = CONVERT(int, CASE WHEN ISNUMERIC(HOST_NAME())=1 THEN HOST_NAME() ELSE [sucId] END)

    I hope this workaround helps... the real solution for the problem is that MS fixes it and quits evaluating HOST_NAME() when creating the filter...
     
  • Wednesday, October 28, 2009 9:10 AM
     
     
    Sign In to Vote
    0
    Hi All,
    I cannot change value of "subset_filterclause" in sp_changemergearticle because partition column is INT. Has microsoft published any fix for this issue?
    Thanks
    Leila
     
  • Friday, September 02, 2011 8:09 PM
     
     Proposed
    Sign In to Vote
    2

    Try to connect to server with a wsid=1 additional connection paramer, after then you can run the original script

    EXEC sp_changemergearticle 
    @publication     = @publication,
    @article = @article, 
    @property     = N'subset_filterclause', 
    @value     = N'AuditorID = CONVERT(int,HOST_NAME())',
    @force_invalidate_snapshot = 1,
    @force_reinit_subscription = 1;
    GO

     


    • Proposed As Answer by Istvan Safar Friday, September 02, 2011 8:09 PM
    • Edited by Istvan Safar Monday, September 05, 2011 7:29 AM
    •  
     
  • Wednesday, May 09, 2012 4:32 AM
     
     
    Sign In to Vote
    0

    Hi,

    It took me a minute to understand the previous post. Specifying a WSID connection parameter allows you to set the computer name that the HOST_NAME() function will return, so then the validation will not fail.

    http://msdn.microsoft.com/en-us/library/ms130822.aspx

    Thanks,
    Lukas

     
  • Wednesday, May 09, 2012 12:20 PM
    Moderator
     
     
    Sign In to Vote
    0
    Why don't you override the subsciber hostname with the HostName parameter in your merge agent, ie if you want to filter on a sucid of 7, make -HostName 7

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941